VBA 里用SQL语句查询1个工作簿里两张表里的满足一个条件的信息

qiao1019

Sub connec()
Dim con As New ADODB.Connection
con.Open "provider=microsoft.ace.oledb.12.0;extended properties=excel 12.0; data source= " & Sheet1.TextBox1.Value
Dim sql
sql = "select [Lot ID],Location from [diebank$A3:AI],[shopfloor$A3:AC] where [Lot ID]=" & cells(6,1)
Dim fil As New ADODB.Recordset
Set fil = con.Execute(sql)
For i = 0 To fil.Fields.Count - 1
Sheets(1).Cells(5, i + 5) = fil.Fields(i).Name
Next
Sheets(1).[e6].CopyFromRecordset fil
con.Close
Set con = Nothing
End Sub

帮忙看看问题出在哪里。。。。

qiao1019

sql = "select [Lot ID],Location from [diebank$A3:AI],[shopfloor$A3:AC] where [Lot ID]=" & cells(6,1) ，我怀疑是这句有问题，哪位老师帮忙指正下哈

qiao1019

sql = "select [Lot ID],Location from [diebank$A3:AI],[shopfloor$A3:AC] where [Lot ID]=" & cells(6,1) ，我怀疑是这句有问题，哪位老师帮忙指正下哈

WANT-T

qiao1019 发表于 2023-3-7 21:09
sql = "select [Lot ID],Location from [diebank$A3:AI],[shopfloor$A3:AC] where [Lot ID]=" & cells(6,1) ...

你得描述口径，

mmwwdd

是有问题
sql = "select [Lot ID],Location from [diebank$A3:AI]a,[shopfloor$A3:AC]b where a.[Lot ID]=" & cells(6,1)
两张表，要区分出来lot id是那个表中的

qiao1019

多谢，最后还是以表和表之间用union连起来，字段用‘连起来