VBA connection SQL Server database operations Excel

Source: Internet
Author: User



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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.