|
Sub test() '这样?与正确与否无关,纯属猜想。
Dim Cn As Object, Sq$, sh As Worksheet, tb$, ta$, c%
Set Cn = CreateObject("ADODB.Connection")
Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
Sheets("查询结果").Activate
ActiveSheet.UsedRange.Offset(, 1).ClearContents
ta = "[查询结果$a1:a" & Cells(Rows.Count, 1).End(xlUp).Row & "]a"
c = 2
For Each sh In Worksheets
With sh
If .Name <> ActiveSheet.Name Then
tb = "[" & .Name & "$A1:E" & .Cells(.Rows.Count, 1).End(xlUp).Row & "]b"
Sq = "SELECT FIRST(b.进仓单价) FROM " & ta & " LEFT JOIN " & tb & " ON a.物料长代码=b.物料长代码 GROUP BY a.物料长代码"
Cells(1, c) = Left(.Name, 2) & "进仓单价"
Cells(2, c).CopyFromRecordset Cn.Execute(Sq)
c = c + 1
End If
End With
Next
Cn.Close
Set Cn = Nothing
End Sub |
|