VBA connection SQL Server database operations Excel



VBA connects SQL Server database operations to Excel.

Sub button 1_click ()

Dim I As Integer, J As Integer, sht as Worksheet ' i,j As Integer variable, sht as an Excel sheet object variable, point to a worksheet

' Dim cn as New ADODB. Connection ' Define the data link object, save the Connection database information; First, add the ADO reference
' Dim rs as New ADODB. Recordset ' Define Recordset object, save data table

The ' Tools---' Reference---〉microsoft ActiveX data Objects ....

' The following two sentences do not need to add a reference to ADO
Set cn = CreateObject ("Adodb.connection")
Set rs = CreateObject ("Adodb.recordset")


Dim STRCN As String, strSQL as String ' string variable
Dim Strcond as String
STRCN = "PROVIDER=SQLOLEDB; Server=r9hdet7;database=dbname; Uid=username; Pwd=password "' Define database link string

' The following statement reads the data table data and saves it to an Excel worksheet: The worksheet is a two-dimensional table, and the recordset is a two-dimensional table

strSQL = "Select Customer_name from Vsc_bi_customer" ' defines the SQL query command string

cn. Open strcn ' Connect with database, if successful, return connection object cn
Rs. Open strSQL, CN ' executes SQL commands contained in strSQL and results are saved in RS Recordset object
i = 2

Set sht = Thisworkbook.worksheets ("test") ' Sht points to the Test sheet of the current workbook
Do and not Rs. EOF ' When the data pointer is not moved to the end of the recordset, loop the following actions
Sht. Cells (i, 1) = RS ("Customer_name") saves the value of field 1 of the current record to column 1th of the Sheet1 worksheet
' Saves the value of the current field 2 to the 2nd column of row I of the Sheet1 worksheet
Rs. MoveNext ' Move the pointer down one record
i = i + 1 ' I plus 1, ready to save the value of the next record related field to the next row in the worksheet
Loop ' loops
Rs. Close ' closes the recordset so that the program saves Fields 1 and 2 of a data table in columns 1th and 2 of the Excel worksheet Sheet1, with rows equal to the number of records in the data table

cn. Close ' closing database links, freeing resources
End Sub

VBA connects SQL Server database operations Excel