|
@是SQL保留字,如果坚持使用,字段需要用方括号括起来,temp = "[" & temp & "]":
- Sub SQL合并数据()
- Dim cnn As Object, rs As Object, rst As Object, d As Object, ds As Object, k
- Dim SQL$, Mypath$, MyFile$, s$, m&, n&, i%, j&, l&, arrf(), arr(), temp$, strField$
- With Application.FileDialog(msoFileDialogFolderPicker)
- .InitialFileName = ThisWorkbook.Path & ""
- If .Show = False Then Exit Sub
- Mypath = .SelectedItems(1) & ""
- End With
- Application.ScreenUpdating = False
- Set d = CreateObject("scripting.dictionary")
- Set ds = CreateObject("scripting.dictionary")
- Cells.ClearContents
- MyFile = Dir(Mypath & "*.xls?")
- Do While MyFile <> ""
- If MyFile <> ThisWorkbook.Name Then
- Set cnn = CreateObject("ADODB.Connection")
- cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath & MyFile
- Set rst = cnn.OpenSchema(20) 'adSchemaTables
- Do Until rst.EOF
- If rst.Fields("TABLE_TYPE") = "TABLE" Then
- SQL = Replace(rst("TABLE_NAME").Value, "'", "")
- If Right(SQL, 1) = "$" Then
- Set rs = cnn.Execute("[" & SQL & "]")
- If Left(rs.Fields(0).Name, 1) <> "F" And Not IsNumeric(Mid(rs.Fields(0).Name, 2)) Then
- n = n + 1
- ReDim Preserve arrf(1 To 2, 1 To n)
- arrf(1, n) = "[Excel 12.0;Database=" & Mypath & MyFile & "].[" & SQL & "]"
- arrf(2, n) = "'" & Split(MyFile, ".")(0) & "' as 工作簿名,'" & Replace(SQL, "$", "") & "' as 工作表名"
- strField = ""
- For i = 0 To rs.Fields.Count - 1 '历遍每个工作表的每个字段(判断列数不等的依据)
- temp = rs.Fields(i).Name
- If Left(temp, 1) <> "F" And Not IsNumeric(Mid(temp, 2)) Then '排除其他可能的空字段
- If Len(temp) Then
- temp = "[" & temp & "]"
- If Not d.Exists(temp) Then d(temp) = "" '字段名写入字典
- End If
- strField = strField & temp & "," '字段名用逗号连接
- ds(arrf(1, n)) = strField & "," '工作簿名与工作表名连接添加到字典ds键值,字段名连接字符串添加到字典条目
- End If
- Next
- End If
- End If
- End If
- rst.MoveNext
- Loop
- End If
- MyFile = Dir()
- Loop
- k = d.Keys
- [a1:b1] = Array("工作簿名", "工作表名")
- [c1].Resize(, d.Count) = k
- For i = 1 To n
- SQL = ""
- For j = 0 To UBound(k) '逐个不重复字段
- If InStr("," & ds(arrf(1, i)), "," & k(j) & ",") Then '该工作表存在该字段
- SQL = SQL & "," & k(j)
- Else
- SQL = SQL & ",'' as " & k(j) '该工作表不存在该字段要添加 '' as 字段
- End If
- Next
- SQL = "select " & arrf(2, i) & SQL & " from " & arrf(1, i) & ""
- Range("a" & Range("A1").CurrentRegion.Rows.Count + 1).CopyFromRecordset cnn.Execute(SQL)
- Next
- With ActiveSheet.UsedRange
- .Value = .Value
- End With
- rs.Close
- rst.Close
- cnn.Close
- Set rs = Nothing
- Set rst = Nothing
- Set cnn = Nothing
- Application.ScreenUpdating = True
- MsgBox "查询完成"
- End Sub
- '@
复制代码 |
评分
-
3
查看全部评分
-
|