vba excel宏来执行多个查询和获取记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15610367/
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
excel macro to execute multiple query & fetch records
提问by user1582596
As of now i have only one standard database connection (via menu or toolbar) which is working fine. however i would like to fetch records for three different period ( each sheet can have different query). before positing this i did various attempts but i couldn't able to fetch any record via macro. i am looking for suggestion or direction to implement my requirement.
到目前为止，我只有一个工作正常的标准数据库连接（通过菜单或工具栏）。但是我想获取三个不同时期的记录（每个工作表可以有不同的查询）。在提出这个之前，我做了各种尝试，但我无法通过宏获取任何记录。我正在寻找建议或方向来实现我的要求。
cell A1 = "name".
单元格 A1 = "名称"。
for,sheet1: select "name" from testDB
for,sheet1: 从 testDB 中选择“名称”
for,sheet2: select "name" from testDB where data >= abc & date <=xyz
for,sheet2：从 testDB 中选择“名称”，其中数据 >= abc & 日期 <=xyz
for,sheet3: select "name" from testDB wehre data >= xyx
for,sheet3: 从 testDB 数据中选择“名称” >= xyx
回答by K_B
Use the Record macro button in the Developer tab to record all the actions you take when you create such a connection with the parameters you want.
使用“开发人员”选项卡中的“记录宏”按钮记录您在使用所需参数创建此类连接时执行的所有操作。
Then stop the recording and go to your VBA screen, take a look at how the code looks and either change it where you want to your liking or record all three versions this way.
然后停止录制并转到您的 VBA 屏幕，查看代码的外观并根据自己的喜好更改它或以这种方式录制所有三个版本。
Now integrate these VBA codes into your VBA script.
现在将这些 VBA 代码集成到您的 VBA 脚本中。
回答by Our Man in Bananas
try doing it all in code using ADODB.
尝试使用 ADODB 在代码中完成所有操作。
First, on each sheet, create a new named range in cell A1 (maybe) called:
首先，在每个工作表上，在单元格 A1（可能）中创建一个新的命名范围，称为：
"Query" & xsheet.name
“查询”& xsheet.name
in that cell put the query specific to that sheet
在该单元格中放置特定于该工作表的查询
then use this code in a VBA Code module:
然后在 VBA 代码模块中使用此代码：
sub getData()
dim cn as new adodb.connection
dim rs as new adodb.recordset
dim connStr as string ' connection string
dim sUDLFile as string ' path and name of Microsoft Data Link File (UDL FILE)
dim xSheet as worksheet
connStr="File Name=" & sUDLFile
cn.open connstr
'loop through all the worksheets
for each xSheet in thisworkbook.worksheets
with rs
' open the connection to the db...
.activeconnection=cn
'get the query from the range on the worksheet!
sQry=xsheet.range("Query" & xsheet.name).text
' open the query from the DB
.open sQry
' dump the dataset onto the worksheet with one line of code in B5 cell!
xsheet.range(B5).copyfromrecordset rs
.close
end with
next
' clean up and release memory
cn.close
set cn=nothing
set rs=nothing
'
end sub
to create your Connection string, (UDL FILE) in MS Windows Explorer:
在 MS Windows 资源管理器中创建您的连接字符串（UDL 文件）：
- navigate to the directory where your workbook is
- right-click and choose New...>Microsoft Data Link.
- change the name to a good one (name.udl maybe)
- double click the new file and set the settings to create and test a connection to the db
- 导航到您的工作簿所在的目录
- 右键单击并选择新建...> Microsoft 数据链接。
- 将名称更改为一个好的名称（也许是 name.udl）
- 双击新文件并设置设置以创建和测试与数据库的连接
any problems, just ask!
有任何问题，尽管问！
Philip
菲利普
