带有链接到单元格值的日期参数的 VBA Excel SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16494169/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel SQL query with date parameters linked to cell values
提问by Gary
I'm trying to pull data into Excel from a SQL database with date parameters. The following VB query works. Instead of manually changing the date values in the TS of the VB query, I want to have the query use cell values from the spreadsheet. Cell A1 has the date for the >= TS, and cell A2 has the date for the < TS
我正在尝试将数据从带有日期参数的 SQL 数据库中提取到 Excel 中。以下 VB 查询有效。我希望查询使用电子表格中的单元格值，而不是手动更改 VB 查询的 TS 中的日期值。单元格 A1 具有 >= TS 的日期，单元格 A2 具有 < TS 的日期
Sub vba_query_01()
Dim oCon As ADODB.Connection
Dim oRS As ADODB.Recordset
Set oCon = New ADODB.Connection
oCon.ConnectionString = "DRIVER=SQL Server;SERVER=GSEYBERTHNB7
\SQLEXPRESS;UID=gseyberth;Trusted_Connection=Yes;APP=2007 Microsoft Office
system;WSID=GSEYBERTHNB7;DATABASE=DATA_LOGGER"
oCon.Open
Set oRS = New ADODB.Recordset
oRS.ActiveConnection = oCon
oRS.Source = "Select * FROM DATA_LOGGER.dbo.LYLE LYLE WHERE (( [Date] >= {TS '2013-04-24
07:00:00'} )) AND (( [Date] < {TS '2013-04-24 15:00:00'} ))"
oRS.Open
Range("A10").CopyFromRecordset oRS
oRS.Close
oCon.Close
If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCon Is Nothing Then Set oCon = Nothing
End Sub
回答by Gary
Thanks for the help. Got it to work with the following:
谢谢您的帮助。让它与以下一起工作：
In Excel, used the formula
在 Excel 中，使用公式
=TEXT(B1,"YYYY-MM-DD hh:mm:ss")
to convert excel date to text. Start date is in cell B3 and finish date is in cell B4
将excel日期转换为文本。开始日期在单元格 B3 中，完成日期在单元格 B4 中
Changed VB to the following:
将 VB 更改为以下内容：
oRS.Source = "Select * FROM DATA_LOGGER.dbo.LYLE_CH2 LYLE_CH2 WHERE (( [Date] >= {TS '" & Range("B3") & "'} )) AND (( [Date] < {TS '" & Range("B4") & "'} )) ORDER BY [Date]"
oRS.Source = "Select * FROM DATA_LOGGER.dbo.LYLE_CH2 LYLE_CH2 WHERE (( [Date] >= {TS '" & Range("B3") & "'} )) AND (( [Date] < {TS '" & Range("B4") & "'} )) ORDER BY [Date]"
Had to add ORDER BYto keep data from query in proper chronological order for spreadsheet calculations.
必须添加ORDER BY以保持查询中的数据按正确的时间顺序进行电子表格计算。
回答by chris neilsen
Try this (assuming data in the sheet is are Date Serial numbers, and in the active sheet)
试试这个（假设工作表中的数据是日期序列号，并且在活动工作表中）
oR.SSource = "Select * FROM DATA_LOGGER.dbo.LYLE LYLE WHERE (( [Date] >= {TS '" & _
Format(Range("A1"), "yyyy-mm-dd hh:nn:ss") & _
"'} )) AND (( [Date] < {TS '" & _
Format(Range("A2"), "yyyy-mm-dd hh:nn:ss") & "'} ))"
