答:把这些数据源集中在一个文件夹里。然后在汇总表里执行Start程序:
Private Sub Start()
Dim DesRange As Range
Dim FilePath As String
Dim strFileName As String
Dim Wkb As Workbook
FilePath = GetDirectory
If FilePath = "" Then Exit Sub
Set DesRange = Sheets("汇总表").Range("A2")
Application.DisplayAlerts = False
Application.ScreenUpdating = False
strFileName = Dir(FilePath & "\*.xls*")
Do Until strFileName = ""
Set Wkb = GetObject(FilePath & "\" & strFileName)
With Wkb.Sheets("sheet1")
DesRange = .Range("B3")
DesRange.Offset(0, 1) = .Range("D3")
DesRange.Offset(0, 2) = .Range("F3")
DesRange.Offset(0, 3) = .Range("D4")
DesRange.Offset(0, 4) = .Range("F4")
DesRange.Offset(0, 5) = .Range("B5")
DesRange.Offset(0, 6) = .Range("B6")
DesRange.Offset(0, 7) = .Range("B10")
DesRange.Offset(0, 9) = .Range("B9")
DesRange.Offset(0, 10) = .Range("E9")
DesRange.Offset(0, 11) = .Range("B13")
DesRange.Offset(0, 12) = .Range("B16")
DesRange.Offset(0, 13) = .Range("B17")
Set DesRange = DesRange.Offset(1, 0)
Windows(.Parent.Name).Visible = True
.Parent.Close SaveChanges:=False
Set Wkb = Nothing
End With
strFileName = Dir
Loop
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "读取完成!"
End Sub
Private Function GetDirectory()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
With fd
If .Show = -1 Then
GetDirectory = .SelectedItems(1)
Else
GetDirectory = ""
End If
End With
End Function
追问你好,很感谢你的回答,请问为什么我运行后提示错误呢?
![](https://video.ask-data.xyz/img.php?b=https://iknow-pic.cdn.bcebos.com/f2deb48f8c5494ee77ff388426f5e0fe98257e49?x-bce-process=image%2Fresize%2Cm_lfit%2Cw_600%2Ch_800%2Climit_1%2Fquality%2Cq_85%2Fformat%2Cf_auto)
追答截图的工作表名要设为"汇总表",运行代码前也要保证该工作表是活动工作表。再试试,我测试过了。
追问嗯嗯,数据调出来了,还有几个小的地方要麻烦一下你,汇总表中,H列的能调到I列不,另外N列的数据应该是模板中E17呢,能帮忙调整下不,麻烦你了,谢谢
追答对不起,是我手误
DesRange.Offset(0, 7) = .Range("B10")改为:DesRange.Offset(0, 8) = .Range("B10")
DesRange.Offset(0, 13) = .Range("B17")改为:DesRange.Offset(0, 13) = .Range("E17")
追问很厉害,很强,我刚已经把这两个改了,完美得到结果,很感谢,方便给我留个联系方式吗?
追答可以在其他软件加我的号