Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim res As ADODB.Recordset
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim STRSQL As String
Dim I As Integer
Dim ROW As Integer
STRSQL = "select * from report1 where 日期=#" & DTPicker1.Value & "#"
'資料庫表名稱
If Dir("c:\khreport1.xlsx") = "" Then '判斷檔案是否存在，不存在則退出
'報表模板位置
MsgBox "報表模版檔案不存在"
Exit Sub
End If
Set cn = New ADODB.Connection
cn.ConnectionString = " DSN=KHreport1;UID=;PWD=;" '資料源名稱
cn.Open
Set res = New ADODB.Recordset
res.Open STRSQL, cn, adOpenKeyset, adLockOptimistic
If res.RecordCount <= 0 Then
MsgBox "你要查詢的資料不存在，可能已被洗掉", vbInformation + vbOKOnly, "系統提示"
res.Close
Set res = Nothing
cn.Close
Set cn = Nothing
Exit Sub
Else
res.MoveFirst
Set xlApp = New Excel.Application
xlApp.DisplayAlerts = ture
xlApp.Visible = ture
Set xlBook = xlApp.Workbooks.Open(StrDir & "c:\khreport1.xlsx")
'報表模板位置
Set xlSheet = xlBook.Worksheets(1)
xlBook.Application.Visible = True
xlSheet.Cells(2, "n") = CDate(res.Fields(0))
I = 1
While I < res.RecordCount
ROW = I + 4
xlSheet.Cells(ROW, "a") = res.Fields(0)
xlSheet.Cells(ROW, "b") = res.Fields(1)
xlSheet.Cells(ROW, "c") = res.Fields(2)
xlSheet.Cells(ROW, "d") = res.Fields(3)
xlSheet.Cells(ROW, "e") = res.Fields(4)
xlSheet.Cells(ROW, "f") = res.Fields(5)
xlSheet.Cells(ROW, "g") = res.Fields(6)
xlSheet.Cells(ROW, "h") = res.Fields(7)
xlSheet.Cells(ROW, "i") = res.Fields(8)
xlSheet.Cells(ROW, "j") = res.Fields(9)
xlSheet.Cells(ROW, "k") = res.Fields(10)
xlSheet.Cells(ROW, "l") = res.Fields(11)
xlSheet.Cells(ROW, "m") = res.Fields(12)
xlSheet.Cells(ROW, "n") = res.Fields(13)
xlSheet.Cells(ROW, "o") = res.Fields(14)
xlSheet.Cells(ROW, "p") = res.Fields(15)
I = I + 1
res.MoveNext
Wend
xlApp.Visible = True
xlApp.DisplayAlerts = False
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End If
ERR:
End Sub
uj5u.com熱心網友回復：
大概看了一下，我感覺是這句的問題： STRSQL = "select * from …………1. 首先，把你的那兩個 # 換成單引號試試；
2. 其次，看看DTPicker1.Value中的年月日是什么分隔符，如果是/就把它替換成-試試。
我幾乎沒使用過DTPicker控制元件，不清楚它的特點，現在也不方便試驗。
如果不能通過屬性設定分隔符，可以用Format函式或Replace函式來處理。
uj5u.com熱心網友回復：
STRSQL = "select * from report1 where 日期=#" & Format(DTPicker1.Value, "yyyy-mm-dd" & "#"從 DTPicker 中回傳的資料格式取決于當前主機作業系統中的地區語言設定，必須用 Format 函式來確定格式。
uj5u.com熱心網友回復：
STRSQL = "select * from report1 where 日期=#" & Format(DTPicker1.Value, "yyyy-mm-dd"） & "#"轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/43645.html