VBA Excel从已关闭的文件中获取工作簿对象或通过工作表索引引用Excel SQL表

Dim conn As Object
Dim rs As Object
Dim filePath As String

filePath = "C:\path\to\your\file.xlsx"

Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filePath & ";Extended Properties='Excel 12.0;HDR=YES;'"

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM [Sheet1$]", conn

' 处理数据...

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim conn As Object
Dim rs As Object

Set xlApp = New Excel.Application
Set xlWorkbook = xlApp.Workbooks.Open("C:\path\to\your\file.xlsx")
Set xlWorksheet = xlWorkbook.Worksheets(1) ' 通过索引引用工作表

' 获取数据范围
Set xlRange = xlWorksheet.Range("A1:D100")

' 创建ADODB连接
Set conn = CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & xlWorkbook.FullName & ";Extended Properties='Excel 12.0;HDR=YES;'"

' 执行SQL查询
Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM [" & xlWorksheet.Name & "$A1:D100]", conn

' 处理数据...

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

xlWorkbook.Close SaveChanges:=False
Set xlWorkbook = Nothing
Set xlWorksheet = Nothing
Set xlApp = Nothing