excel vba怎么批量处理一个目录下的所有文档

Question

我想在一个目录下新建一个excel，在这个excel里面用vba汇总其他工作簿数据，要使用相对路径打开，再用一个已经编写好的程序段对所有工作簿进行统一处理，最后求和到新建工作簿。
问题是怎么依次打开和处理每个excel文档，这些文档命名一般是前面字母后面数字序号的形式。但是不一定从1开始
文件夹里面的excel表格数目不确定。比如
jn2
jn3
jn4
jn5
jn6

Answer 1

下面的语句运行后，可以选择你要打开的工作簿，然后要如何处理可在最下面i2的循环嵌套里处理。
Sub data()

FileToOpen = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Please select the files...", , True)

If IsArray(FileToOpen) = 0 Then
'MsgBox "没有选择文件"
MsgBox "No files are selected!"
WS1.Unprotect
GoTo ErrorHandler
End If

For i2 = 1 To UBound(FileToOpen)
userfilename = FileToOpen(i2)
.....

......

.....

next i2
ErrorHandler:
end sub

Answer 2

你好，百度搜: EXCEL工作室或者VBA定制 可以为您订制该程序，回答：
Sub gg() '添加个模块1
Dim Paths As String
Pahts = Dir(ThisWorkbook.Path & "\")
Do While Paths <> ""
'所有表格的名字
MsgBox Paths
Paths = Dir
Loop
End Sub

Answer 3

'excelPath = "C:\Documents and Settings\All Users\桌面"
excelPath = "D:\桌面\"

Set s = Application.FileSearch
s.LookIn = excelPath
s.Filename = "waybilldatalist*.xls" '
s.Execute '执行搜索
On Error Resume Next
'
For I = 1 To s.FoundFiles.Count
myName = s.FoundFiles(I)
myName = Split(myName, "\")(UBound(Split(myName, "\")))
If InStr(myName, "xls") > 0 Then
Dim tm As Integer
Dim td As Integer
tm = CInt(Left(Right(myName, 8), 2))
td = CInt(Left(Right(myName, 6), 2))
If MsgBox("是否导入" + CStr(tm) + "月" + CStr(td) + "日数据？", vbYesNo, "提示") = vbYes Then '6返回是，7返回否

Set xlApp = CreateObject("Excel.Application") '创建EXCEL对象
Set xlBook = xlApp.Workbooks.Open(excelPath + myName) '打开已经存在的EXCEL工件簿文件
xlApp.Visible = True '设置EXCEL对象可见（或不可见）
Set xlsheet = xlApp.Worksheets(Replace(myName, ".xls", "")) '设置活动工作表

If Sheets("运输方式").Cells(1, 5) = "" Then Sheets("运输方式").Cells(1, 5) = "1"
.........................................................................................

xlBook.Save
xlBook.Close

End If
End If
Next I

Answer 4

你好，百度搜: EXCEL工作室或者VBA定制 可以为您订制该程序，回答：
Sub gg() '添加个模块1
Dim Paths As String
Pahts = Dir(ThisWorkbook.Path & "\")
Do While Paths <> ""
'所有表格的名字
MsgBox Paths
Paths = Dir
Loop
End Sub

Answer 5

'excelPath = "C:\Documents and Settings\All Users\桌面"
excelPath = "D:\桌面\"

Set s = Application.FileSearch
s.LookIn = excelPath
s.Filename = "waybilldatalist*.xls" '
s.Execute '执行搜索
On Error Resume Next
'
For I = 1 To s.FoundFiles.Count
myName = s.FoundFiles(I)
myName = Split(myName, "\")(UBound(Split(myName, "\")))
If InStr(myName, "xls") > 0 Then
Dim tm As Integer
Dim td As Integer
tm = CInt(Left(Right(myName, 8), 2))
td = CInt(Left(Right(myName, 6), 2))
If MsgBox("是否导入" + CStr(tm) + "月" + CStr(td) + "日数据？", vbYesNo, "提示") = vbYes Then '6返回是，7返回否

Set xlApp = CreateObject("Excel.Application") '创建EXCEL对象
Set xlBook = xlApp.Workbooks.Open(excelPath + myName) '打开已经存在的EXCEL工件簿文件
xlApp.Visible = True '设置EXCEL对象可见（或不可见）
Set xlsheet = xlApp.Worksheets(Replace(myName, ".xls", "")) '设置活动工作表

If Sheets("运输方式").Cells(1, 5) = "" Then Sheets("运输方式").Cells(1, 5) = "1"
.........................................................................................

xlBook.Save
xlBook.Close

End If
End If
Next I