|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
这是我现在用的VB,我想要在CH列添加上数据来源表格的名称,怎么弄啊,谢谢啦
Sub 统计设计数据()
Dim cnn As Object, SQL As String, MyPath$, MyFile$, n As Long
' Cells.Clear
Set cnn = CreateObject("ADODB.Connection")
MyPath = ThisWorkbook.Path & "/"
MyFile = Dir(MyPath & "*.xlsx")
Do While MyFile <> ""
If MyFile <> ThisWorkbook.Name Then
n = n + 1
If n = 1 Then
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;hdr=no';Data Source=" & MyPath & MyFile
SQL = "SELECT * FROM [设计评审表$A36:CG36] WHERE F1 IS NOT NULL"
Else
SQL = SQL & " UNION ALL SELECT * FROM [Excel 12.0;HDR=NO;Database=" & MyPath & MyFile & "].[设计评审表$A36:CG36] "
End If
End If
MyFile = Dir()
Loop
Range("设计数据!A4").CopyFromRecordset cnn.Execute(SQL)
cnn.Close
Set cnn = Nothing
End Sub
|
|