VBA:Excel 中利用 ODBC数据源,以及 Sheet.QueryTables对象 查询数据
第一步:配置ODBC数据源-"控制面板"-"管理工具"-"ODBC数据源"-"用户DSN"。如 "Reports.DSN" (可以查资料)
第二步:(1)在Excel(03)中-"数据"-"导入数据"-"新建数据库查询"-然后选择之前建立的ODBC源-..(这里几个对话框可以直接关闭)
-"Micorsoft Query"界面-"SQL"按钮(因为我是用sql语句查询)-输入sql语句-关闭(或者选择"将数据返回Excel"按钮)
(2)在Excel(07)中-"数据"-"自其它数据源"-"来自Microsoft Query"-...其他与03差不多
至此数据源,连接到excel中。其中一些步骤,写的有点模糊,大致OK
第三步:VBA代码--主要是利用 QueryTable 对象
'--利用 QueryTable Sub QureyForMonth() Dim qTable As QueryTable, sql As String Dim curMonth As Date, firstDay As Date, lastDay As Date Dim i As Integer, num As Integer, j As Integer On Error GoTo hErr With ActiveSheet '--sheet1 表 If IsDate(.Range("B1").Value) Then curMonth = CDate(.Range("B1").Value) '--当前月份 firstDay = CDate(Year(curMonth) & "-" & Month(curMonth) & "-1") '--第一天 lastDay = DateAdd("m", 1, firstDay) '--下月第一天 lastDay = CDate(Year(curMonth) & "-" & Month(curMonth) & "-" & DateDiff("d", firstDay, lastDay)) '--最后一天 Set qTable = .QueryTables.Item("QUERY") qTable.sql = "SELECT A.InvoiceNo, A.InvDate, A.InvSeqNo, A.VNumber, A.PickDate, A.CustPO AS [Cust P.O.], A.ItemID, A.Enduser, A.EMS," & _ " A.OEM, A.CustItemID,A.Category, A.Qty AS [Inv.Qty], A.Currency, A.Price, B.Quantity, B.Warehouse,B.Location, " & _ " B.CustID, B.CustPO, B.PurchPO, B.InvoiceNO AS [B.Inv No.],B.VMINo " & _ " FROM VMISalesInvX A LEFT OUTER JOIN VMIStockIOX B ON A.VID = B.VID " & _ " WHERE (A.WareHouse IN ('H02', 'H03')) AND (A.InvDate BETWEEN '" & firstDay & "' AND '" & lastDay & "') AND (A.InvoiceNo <> '') " & _ " ORDER BY A.InvDate, A.InvoiceNo, A.InvSeqNo, B.ID" qTable.Refresh False '--设置格式 .Columns(2).NumberFormat = "yyyy-MM-dd" .Columns(5).NumberFormat = "yyyy-MM-dd" .Range("B1").NumberFormat = "YYYY-MM" .Cells.Select .Cells.EntireColumn.AutoFit .Range("B1").Select MsgBox "WMI Sales Stock IO Details Updated!", vbInformation, "UPDATE" Else .Range("B1").Select MsgBox "Input Date Error", vbCritical, "ERROR" End If End With Exit Sub '--查询结束 hErr: MsgBox "Error Number : " & Err.Number & vbCrLf & _ "Error Source : " & Err.Source & vbCrLf & _ "Error Description : " & Err.Description, vbCritical, "ERROR" End Sub