Excel reads database tables and excel reads Databases

Excel reads database tables and excel reads Databases

The following code is used to read the table fields of a database using an Excel macro.

Public Sub getdata () Dim cnn As New ADODB. connection, sh As Worksheet Dim rs As New ADODB. recordset Dim cnnStr As String, SQL As String cnnStr = "Provider = SQLOLEDB; Initial Catalog = BI" & "; User ID = sa" & "; Password = Aa123456 "&"; data Source = 172.16.0.73 "cnn. connectionString = cnnStr cnn. open SQL = "SELECT * from dbo. tt "MsgBox": "& SQL Set rs = cnn. execute (SQL) For col = 0 To rs. fields. coun T-1 Sheets ("Sheet2 "). range ("A1 "). offset (0, col ). value = rs. fields (col ). name Next Sheets ("Sheet2 "). range ("A1 "). offset (1, 0 ). copyFromRecordset rs. close MsgBox "the query is finished! "Set rs = Nothing cnn. Close Set cnn = NothingEnd Sub

Code Description:
Data Source: Database IP Address
Catalog: Database Name
User ID: User Name
Password: Database Password
SQL: An SQL statement that reads a table directly.
The execution process has two pop-up windows. Click OK.
Finally, the data is written to the sheet 2 of the current Excel file.

To execute the above Code, you also need to set it for Excel:
It's probably a Library, but I cannot remember it. I will update this article later.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.