送你个一步到位的。- Sub CommandButton1_Click()
- Dim MySQL As String, i As Long, k As Integer, shts As Integer, works As Integer
- Dim MyCnn, rs, arr()
- Dim Sht As Worksheet
- Set MyCnn = CreateObject("adodb.connection")
- Application.ScreenUpdating = False
- Columns("a:f").Clear
- '选取文件窗口
- Filename = Application.GetOpenFilename("Microsoft Office Excel Files (*.xls), *.xls", , "请选取文件", , MultiSelect:=True)
- If Not IsArray(Filename) Then Exit Sub
- works = UBound(Filename)
- '工作簿循环
- For k = 1 To works
- With Workbooks.Open(Filename(k))
- '通过循环得到工作簿的sql语句
- For Each Sht In .Worksheets
- shts = shts + 1
- ReDim Preserve arr(1 To shts)
- arr(shts) = "select * from [Excel 8.0;Database=" & Filename(k) & "].[" & Sht.Name & "$]"
- Next Sht
- '执行查询
- .Close
- End With
- Next k
- MySQL = "Select 姓名,SUM(工资) as 工资 From (" & Join(arr, " Union All ") & ") Group By 姓名"
- MyCnn.Open ("provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & Filename(1))
- Sheet1.Range("A2").CopyFromRecordset MyCnn.Execute(MySQL)
- MyCnn.Close
- Set MyCnn = Nothing
- Range("a1:b1").Value = Array("姓名", "工资")
- Application.ScreenUpdating = True
- End Sub
复制代码 |