可以帮我看下一个文件吗，不知道怎么用宏作者: lfx28 时间: 2016-11-4 11:57
那位大侠能帮忙解释一下这个代码吗？万分感谢！
Sub Macro1()
Dim cnn As Object, SQL$, p$, f$, m&, n&, t$
Application.ScreenUpdating = False
ActiveSheet.UsedRange.Offset(1).ClearContents
Set cnn = CreateObject("ADODB.Connection")
p = ThisWorkbook.Path & "\"
f = Dir(p & "*.xls")
Do While f <> ""
If f <> ThisWorkbook.Name Then
n = n + 1
If n = 1 Then
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;hdr=no';Data Source=" & p & f
Else
t = "[Excel 8.0;hdr=no;Database=" & p & f & "]."
End If
m = m + 1
If m > 49 Then
Range("a65536").End(xlUp).Offset(1).CopyFromRecordset cnn.Execute(SQL)
m = 1
SQL = ""
End If
If Len(SQL) Then SQL = SQL & " union all "
SQL = SQL & "select '" & Replace(f, ".xls", "") & "',* from " & t & "[材料汇总$G2:G2]"
End If
f = Dir()
Loop
If Len(SQL) Then Range("a65536").End(xlUp).Offset(1).CopyFromRecordset cnn.Execute(SQL)
cnn.Close
Set cnn = Nothing
Application.ScreenUpdating = True
End Sub 作者: tt2 时间: 2016-11-24 15:39
谢谢！！！