筆者在工作中,，經(jīng)常使用 Excel VBA查詢和匯總數(shù)據(jù)，非常方便,，但最近遇到個(gè)麻煩,，怎么也解決不了，今天整理出來還請(qǐng)高手幫忙指導(dǎo),。

一,、原始數(shù)據(jù)

數(shù)據(jù)庫名稱：corn

表：成品出庫

查詢代碼：

SELECT date , sht_id , customer, product , 小袋規(guī)格 , 大袋規(guī)格 FROM `成品出庫`

where customer = '何現(xiàn)鋒' and product = '達(dá)育5158'

查詢結(jié)果如下圖（經(jīng)核對(duì)與原始數(shù)據(jù)一致）：

查詢結(jié)果

二,、Excel VBA 連接MySQL數(shù)據(jù)庫

連接代碼：

Option Explicit

Sub GetDataFromMysql()

Dim con As New ADODB.Connection

Dim rs As New ADODB.Recordset

Dim sql As String, customer_name$

Dim sh As Worksheet

Dim i As Integer

Set sh = Sheets(2)

sh.Range("A1:L500").Clear

'連接數(shù)據(jù)庫

con.ConnectionString = "driver={MySQL ODBC 8.0 unicode Driver};server=localhost;uid=wyj;pwd=wyj;database=corn;port=3306;"

con.Open

customer_name = Sheets(2).Range("M2").Value

sql = "select date,customer,address,product,小袋規(guī)格,大袋規(guī)格 from 成品出庫 where customer like '%" & customer_name & "%' "

rs.Open sql, con, adOpenStatic, adLockOptimistic

'設(shè)置表頭

'sh.Range("A1:E1").Value = Array("ID", "date", "name", "salary", "other")

For i = 0 To rs.Fields.Count - 1

sh.Cells(1, i + 1) = rs.Fields(i).name

Next i

'輸出結(jié)果

sh.Range("A2").CopyFromRecordset rs

'關(guān)閉連接,，釋放內(nèi)存

rs.Close

con.Close

Set rs = Nothing

Set con = Nothing

End Sub

運(yùn)行代碼后查詢結(jié)果：

“巨坑”來了，大家注意看product列中品種名稱達(dá)育5158,，只顯示：達(dá)育51,。丟失了部分?jǐn)?shù)據(jù)。

連接數(shù)據(jù)庫查詢結(jié)果

三,、VBA連接EXCEL檢查查詢結(jié)果

代碼：

Option Explicit

Sub QueryFromExcel()

Dim Conn As Object, Rst As Object

Dim strConn As String, strSQL As String

Dim i As Integer, Path As String

Dim customer_name As String

Set Conn = CreateObject("ADODB.Connection")

Set Rst = CreateObject("ADODB.Recordset")

Path = "E:/public/歷年報(bào)表/玉米報(bào)表2022.7.13.xls"

Select Case Application.Version * 1

Case Is <= 11

strConn = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & Path

Case Is >= 12

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Path & ";Extended Properties=""Excel 12.0 ; HDR =YES"";"

End Select

'設(shè)置SQL查詢語句

customer_name = Sheets(2).Range("M2").Value

strSQL = "SELECT date,customer,address,product,小袋規(guī)格,大袋規(guī)格 FROM [成品出庫$] where customer like '%" & customer_name & "%'" _

& "or address like '%" & customer_name & "%'or product like '%" & customer_name & "%'or description like '%" & customer_name & "%' "

Conn.Open strConn '打開數(shù)據(jù)庫鏈接

Set Rst = Conn.Execute(strSQL) '執(zhí)行查詢,，并將結(jié)果輸出到記錄集對(duì)象

With Sheet2

.Range("A1:L1000").Clear

For i = 0 To Rst.Fields.Count - 1 '填寫標(biāo)題

.Cells(1, i + 1) = Rst.Fields(i).name

Next i

.Range("A2").CopyFromRecordset Rst

End With

Rst.Close '關(guān)閉數(shù)據(jù)庫連接

Conn.Close

Set Conn = Nothing

Set Rst = Nothing

End Sub

如圖所示結(jié)果顯示正常：

VBA 連接 Excel查詢結(jié)果

不知道為什么，非常無奈,，請(qǐng)哪位知道的高手朋友幫忙看看問題出在哪里,？