vba 使用 OLEDB 从打开的 Excel 文件中读取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15620080/
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
Reading Data using OLEDB from opened Excel File
提问by NetDeveloper
I have an excel file(Lets' say File X) with 2 sheets. In first sheet I display charts. Second I have data for the chart. In order to get data from chart, I need to process that data as we do in SQL like Group by, order by. Is there any way I can use oledb to read data from second sheet using VBA code in same excel file(file X)?
我有一个有 2 张纸的 excel 文件（比如说文件 X）。在第一张表中，我显示图表。其次，我有图表的数据。为了从图表中获取数据，我需要像在 SQL 中那样处理这些数据，例如 Group by、order by。有什么办法可以使用oledb在同一个excel文件（文件X）中使用VBA代码从第二张工作表读取数据？
Thanks!!
谢谢！！
回答by Tim Williams
Here's an example of using SQL to join data from two ranges: it will work fine if the file is open (as long as it has been saved, because you need a file path).
下面是一个使用 SQL 连接来自两个范围的数据的示例：如果文件处于打开状态（只要它已保存，因为您需要一个文件路径），它将正常工作。
Sub SqlJoin()
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim sPath
Dim sSQL As String
sSQL = "select a.blah from <t1> a, <t2> b where a.blah = b.blah"
sSQL = Replace(sSQL, "<t1>", Rangename(Sheet1.Range("A1:A5")))
sSQL = Replace(sSQL, "<t2>", Rangename(Sheet1.Range("C1:C3")))
If ActiveWorkbook.Path <> "" Then
sPath = ActiveWorkbook.FullName
Else
MsgBox "Workbook being queried must be saved first..."
Exit Sub
End If
oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & sPath & "';" & _
"Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
oRS.Open sSQL, oConn
If Not oRS.EOF Then
Sheet1.Range("E1").CopyFromRecordset oRS
Else
MsgBox "No records found"
End If
oRS.Close
oConn.Close
End Sub
Function Rangename(r As Range) As String
Rangename = "[" & r.Parent.Name & "$" & _
r.Address(False, False) & "]"
End Function
