recipeswhe.blogg.se - Excel 2016 vba and macros tutorial

Read data from record set Private Sub ReadRS()ĭebug.Print "ShipperID: " & mRS.Fields("ShipperID").Value & " CompanyName: " & mRS.Fields("CompanyName").Value & " Phone: " & mRS.Fields("Phone").ValueĬlose connection Private Sub CloseConnection()Ĭall OpenConnection("ServerName", "NORTHWND")Ĭall ExecuteCmd("INSERT INTO. with SQL Server Authentication Private Sub OpenConnection2(pServer As String, pCatalog As String, pUser As String, pPsw As String)Ĭall mDataBase.Open("Provider=SQLOLEDB Initial Catalog=" & pCatalog & " Data Source=" & pServer & " Integrated Security=SSPI User ID=" & pUser & " Password=" & pPsw)Įxecute sql command Private Sub ExecuteCmd(sql As String) with Windows Authentication Private Sub OpenConnection(pServer As String, pCatalog As String)Ĭall mDataBase.Open("Provider=SQLOLEDB Initial Catalog=" & pCatalog & " Data Source=" & pServer & " Integrated Security=SSPI")ī. Microsoft ActiveX Data Objects Recordset 2.8 Libraryĭeclare variables Private mDataBase As New ADODB.ConnectionĬreate connection a. Microsoft ActiveX Data Objects 2.8 Library

Use Worksheet object and not Sheet object.

Methods for Finding the Last Used Row or Column in a Worksheet.

Loop through all Sheets in Active Workbook.

Excel assigns a default name to macros: Macro1, Macro2, Macro3 and so on. This dialog allows you to: Assign a name to the macro. Creating a drop-down menu in the Active Worksheet with a Combo Box Click on Record Macro on the Developer tab or on the Record Macro button that appears on the left side of the Status Bar.