When we use excel programming, we often need to use a database.
So how to connect to the database and then read data from the database?
VBA connects to SQL SERVER database instance:
Dim strConn As String, strSQL As String Dim conn As ADODB.Connection Dim ds As ADODB.Recordset Dim col As Integer 'String to connect to the database strConn = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=[user ];Password=[password];Initial Catalog=[database];Data Source=[database IP address or database server name];Connect Timeout=720; " 'Query statement, if the sql statement is very long, you can use strSQL=strSQL+ to connect the statements divided into multiple paragraphs. If the statement is very short, you can only write it on one line. strSQL = "select * from Hy_KPI_Shop_Dept_WeekRpt " strSQL = strSQL+"where sdate='2014-01-01' order by sdate,shopid " Set conn = New ADODB.Connection Set ds = New ADODB.Recordset 'Open database connection conn.Open strConn 'This sentence and Connect at the database connection string Timeout=720 means that if the statement runs for a long time, these two sentences can extend the waiting time of VBA. Without these two sentences, VBA will often report a query timeout. conn.CommandTimeout = 720 With ds 'Obtain data according to the query statement. Open strSQL, conn 'Automatically control the addition of all column headers For col = 0 To ds.Fields.Count - 1 'Please note that the parameters in Offset(0, col) must be To be correct, this sentence means that the title will be written in the first row, starting from cell A1. If you do not want to write the title row, you can comment out the following sentence. Worksheets("KPI weekly report for each class in the store").Range("A1").Offset(0, col).Value = ds.Fields(col).Name Next 'Add all rows of data, this sentence means that the query results will be written In the first row, it starts from cell A1, but since the title row is written in the first row, this row is actually written from a row below the title. Worksheets("sheet1").Range("A1").Offset(1, 0).CopyFromRecordset ds End With 'Close the database connection and clear the resources Set ds = Nothing conn.Close Set conn = Nothing