记录集查询- Excel
记录集查询- Excel
提问于 2016-10-19 19:21:23
strQuery = _
"SELECT * FROM [Sheet1$] " & _
"IN '" & ThisWorkbook.Path & "\Source1.xlsx' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
"UNION " & _
"SELECT * FROM [Sheet1$] " & _
"IN '" & ThisWorkbook.Path & "\Source2.xlsx' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
"UNION " & _
"SELECT * FROM [Sheet1$] " & _
"IN '" & ThisWorkbook.Path & "\Source3.xlsx' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
"ORDER BY A;"嗨,
我在VBA中的一个模块中有上面的内容(代码本身是sourced from here )。我的问题是，我在每个文件中的列从第15行开始，数据从第16行开始。如何使每个文件从第15行开始显示为UNION？
提前感谢！
回答 1
Stack Overflow用户
回答已采纳
发布于 2016-10-19 20:21:24
使用通过ADO或DAO进行的Excel工作簿SQL查询，您可以通过以[Sheet$A1:Z2]的方式设置范围来指定工作表的区域。首先查找最后一个命名列(对于UNION，它们必须是相同的长度和类型)，并添加足以进行有效查询的行。下面使用Z999
strQuery = _
"SELECT * FROM [Sheet1$A15:Z999] " & _
"IN '" & ThisWorkbook.Path & "\Source1.xlsx' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
"UNION " & _
"SELECT * FROM [Sheet1$A15:Z999] " & _
"IN '" & ThisWorkbook.Path & "\Source2.xlsx' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
"UNION " & _
"SELECT * FROM [Sheet1$A15:Z999] " & _
"IN '" & ThisWorkbook.Path & "\Source3.xlsx' " & _
"[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties='HDR=YES;'] " & _
"ORDER BY A;"页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40139865
复制相关文章
相似问题
腾讯云开发者
