Popular blog tags

EXCEL VBA访问SQL server外部数据源查、改、增、删


Table of Content

Excel data SQL Query  Using VBA(with ADODB Driver)

Using Excel VBA SQL to query Excel data(with ADODB Driver)

Data type mismatch on SQL Query in VBA

Using Class QueryTable (Excel VBA)  to query Excel data


There are two ways to export Excel data to SQL Server using VBA:

1.To use ADO,OLE, ODBC
2.To use QueryTable.

ODBC:Microsoft ODBC Desktop Database Drivers

The ADODB Recordset is not part of the standard VBA library.

First we need to connect via the ADODB Driver to our Excel Worksheet.

This is the same Driver which runs SQL Queries on MS Access Databases.

This can be found in the VBEditor; tab 'Tools', option 'References' as Microsoft ActiveX Data Objects n.n Library.

1.Add a reference to: Microsoft ActiveX Data Objects  Library
2.conection string
4. excute sql


Microsoft ActiveX Data Objects x.x Library. 
Office 14 version 2.0, 
office 365 version 6.1

VBA+ADO+SQL query + Sql Server

step 1:Add a reference to: Microsoft ActiveX Data Objects  Library

ADO (ActiveX Data Objects,ActiveX数据对象)是微软提出的应用程序接口,用以实现访问关系或非关系数据库中的数据

在EXCEL VBA中,一般只有通过ADO,才可以使用强大的SQL查询语言访问外部数据源,进而查、改、增、删相关数据源中的数据。


Dim Conn As New ADODB.Connection
Dim recset As New ADODB.Recordset
Dim sqlQry As String, sConnect As String

sqlQry = "select top 1000 si.InvoiceID, si.InvoiceDate, sc.CustomerName from Sales.Invoices si" & _
             " left join sales.Customers sc on sc.CustomerID = si.CustomerID"

sConnect = "Driver={SQL Server};Server=[Your Server Name Here]; Database=[Your Database Here];Trusted_Connection=yes;"

Conn.Open sConnect

Set recset = New ADODB.Recordset

    recset.Open sqlQry, Conn
    Sheet2.Cells(2, 1).CopyFromRecordset recset


Set recset = Nothing




Excel data SQL Query Using VBA(with ADODB Driver)


Distinct, grouping rows of Excel data, running multiple selects etc。

I have two excel workbooks. One has a list of target customers and the other has a a table of sales data. I would like to use vba and write a sql query to get the sales history for specific customers and move that sales history to a new ListObject in the Target Customers workbook.

Below is a simple connection and query to another workbook:

Sub simple_Query()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    dbpath = "your path here"
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    strSQL = "SELECT * FROM [Sheet1$] "
    Set vNewWB = Workbooks.Add 'or .CopyFromRecordset rs to open workbook
    connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" & dbpath & ";
Extended Properties=""Excel 12.0; HDR=YES; IMEX=1""; Mode=Read;"
    cn.Open connstr
    Set rs = cn.Execute(CommandText:=strSQL)
    vNewWB.Sheets(1).Range("A2").CopyFromRecordset rs
    For intcolIndex = 0 To rs.Fields.Count - 1
        Range("A1").Offset(O, intcolIndex).Value = rs.Fields(intcolIndex).Name
    Set cn = Nothing
    Set rs = Nothing
End Sub


Sub 查询方法二()
Set CONN = CreateObject("ADODB.Connection")
CONN.Open "dsn=excel files;dbq=" & ThisWorkbook.FullName
sql = "select 区域,存货类, sum(代销仓入库数量),sum(代销仓出库数量),sum(日报数量)from [sheet4$a:i] where 区域='" & [b3] & "' and month(日期)='" & Month(Range("F3")) & "' group by 区域,存货类"
Sheets("sheet2").[A5].CopyFromRecordset CONN.Execute(sql)
CONN.Close: Set CONN = Nothing
End Sub


Sub 根据入库表和回款表的区域名和月份分别求存货类发货数量和本月回款数量查询()
Set conn = CreateObject("adodb.connection")
conn.Open "provider=microsoft.jet.oledb.4.0;" & _
              "extended properties=excel 8.0;data source=" & ThisWorkbook.FullName
Sql = " select a.存货类,a.fh ,b.hk from (select 存货类,sum(本月发货数量) " _
       & " as fh from [入库$] where 存货类 is not null and 区域='" & [b2] _
       & "' and month(日期)=" & [d2] & " group by 存货类) as a" _
       & " left join (select 存货类,sum(数量) as hk from [回款$] where 存货类" _
       & " is not null and 区域='" & [b2] & "' and month(开票日期)=" & [d2] & "" _
       & " group by 存货类) as b on a.存货类=b.存货类"
Range("a5").CopyFromRecordset conn.Execute(Sql)
End Sub



  '--- Declare Variables to store the connection, the result and the SQL query
    Dim connection As Object, result As Object, sql As String, recordCount As Integer
    '--- Connect to the current datasource of the Excel file
    Set connection = CreateObject("ADODB.Connection")
    With connection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 12.0 Xml;IMEX=1;HDR=YES"";"
    End With
    '--- Write the SQL Query. In this case, we are going to select manually the data range
    '--- To print the whole information of the table
    sql = "SELECT * FROM [冲压外箱标签$A1:J6] where [旧物料代码] = 'IC0652H' and [生产日期] = '2021/10/24'"
    '--- Run the SQL query
    Set result = connection.Execute(sql)
    '--- Fetch information
        ' Print the information of every column of the result
        Debug.Print result(0); ";" & result(1) & ";" & result(2) & ";" & result(3) & ";" & result(4)
        recordCount = recordCount + 1
    Loop Until result.EOF
    '--- Print the amount of results
    Debug.Print vbNewLine & recordCount & " results found."

1.Microsoft ACE OLEDB 12.0 connection strings

Xlsm files

Connect to Excel 2007 (and later) files with the Xlsm file extension. That is the Office Open XML format with macros enabled.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsm;Extended Properties="Excel 12.0 Macro;HDR=YES";

The connection properties are described as follows:

Provider: we will use the Microsoft Access Database Engine 2010 (Microsoft.ACE.OLEDB.12.0)
ConnectionString: we will use the current excel file as the database.
HDR=Yes;: indicates that the first row contains the column names, not data. HDR=No; Default is HDR=Yes.
IMEX=1: setting treats all data as text.
Excel 8.0. Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and 
use the Excel 8.0 source database type for Microsoft Excel 8.0 (97), 9.0 (2000) and 10.0 (2002) workbooks.
office97 : Excel 8.0、office2000 : 9.0、officeXP(2002) : 10.0、office2003 : 11.0、
office2007: Excel12.0、office2010 : 14.0、office2013: 15.0


"HDR=Yes;" indicates that the first row contains columnnames, not data.

SELECT * FROM [Sheet1$A1:E6] WHERE [city] = 'Boston'

"HDR=No;" If you don't use HDR, the nomenclature of the columns will follow the F1, F2, F3, ..., FN pattern:

SELECT * FROM [Sheet1$A2:E6] WHERE [F5] = 'Boston'


detail:The Connection Strings Reference https://www.connectionstrings.com/


       0 is Export mode
  1 is Import mode : tells the driver to always read "intermixed" data columns as text.
              If you want to read the column headers into the result set (using HDR=NO even though there is a header) and the column data is numeric, use IMEX=1 to avoid crash.             
            To always use IMEX=1 is a safer way to retrieve data for mixed data columns. 

  2 is Linked mode (full update capabilities)
  我这里特别要说明的就是 IMEX 参数了,因为不同的模式代表著不同的读写行为:
  当 IMEX=0 时为“汇出模式”,这个模式开启的 Excel 档案只能用来做“写入”用途。
            使用IMEX=0时,Office Excel 不能打开此文件,提示正在被别人使用。

  当 IMEX=1 时为“汇入模式”,这个模式开启的 Excel 档案只能用来做“读取”用途。
            使用IMEX=1时,Office Excel 能打开此文件,并且进行编辑。
        tells the driver to always read “intermixed” (numbers, dates, strings etc) data columns as text.     
        Note that this option might affect excel sheet write access negative.

  当 IMEX=2 时为“连結模式”,这个模式开启的 Excel 档案可同时支援“读取”与“写入”用途。
0 ---输出模式;

2.What are the possible MODE for Provider=Microsoft.ACE.OLEDB.12.0

Read — Read only.

ReadWrite — Read and write.

Share Deny None — Neither read nor write access can be denied to others.

Share Deny Read — Prevents others from opening in read mode.

Share Deny Write — Prevents others from opening in write mode.

Share Exclusive — Prevents others from opening in read/write mode.

Write — Write only.


ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;G:\CC\eStmt_2014-1-11.txt", Destination:=Range("$A$1"))
        .Name = "eStmt_2014-1-11"

Data type mismatch on SQL Query in VBA







Excel data VBA Class QueryTable 

The class QueryTable represents a worksheet table built from data returned from an external data source, such as a SQL server or a Microsoft Access database.

To use a QueryTable class variable it first needs to be instantiated, for example:

Dim qrytbl as QueryTable
Set qrytbl = ActiveCell.QueryTable


Dim SQL As String
Dim con As connection

Set con = New connection
con.ConnectionString = "ODBC;DSN=DB01;UID=;PWD=;Database=MyDatabase"

Worksheets("Received").QueryTables.Add(Connection:=con, Destination:=Worksheets("Received").Range("A5"), SQL:=SQL).Refresh

set con = nothing