2,503
社区成员




Set xlAppFilter = CreateObject("Excel.Application") '创建EXCEL应用类
Set xlBookFilter = xlAppFilter.Workbooks.Open(strFileNameForFilter)
xlBookFilter.Activate
Dim i, Count As Integer
Count = xlBookFilter.Sheets.Count
For i = 1 To Count
‘以下这句是错的,应该怎样写才是对的呢?
xlSheetsFilter = xlBookFilter.Sheets.Select(i)
msgbox xlSheetsFilter.Name
r = r + 1
Next
xlBookFilter.Close (True)
Set xlSheetFilter = Nothing
Set xlBookFilter = Nothing
xlAppFilter.Quit '关闭EXCEL应用类
Set xlAppFilter = Nothing '释放EXCEL应用类
Set xlAppFilter = CreateObject("Excel.Application") '创建EXCEL应用类
Set xlBookFilter = xlAppFilter.Workbooks.Open(strFileNameForFilter)
xlBookFilter.Activate
For Each xlSheetFilter In xlBookFilter.Sheets
xlSheetAddup.Cells(r, 1) = xlSheetFilter.Name
r = r + 1
Next
Dim xlApp As Excel.Application
Dim xlBookAddup As Excel.Workbook
Dim xlSheetAddup As Excel.Worksheet
Set xlApp = CreateObject("Excel.Application") '创建EXCEL应用类
Set xlBookAddup = xlApp.Workbooks.Add '创建统计EXCEL工作簿
Set xlSheetAddup = xlBookAddup.Sheets("Sheet3")
xlSheetAddup.Delete '删除几张默认页
Set xlSheetAddup = xlBookAddup.Sheets("Sheet2")
xlSheetAddup.Delete '删除几张默认页
Set xlSheetAddup = xlBookAddup.Sheets("Sheet1")
xlSheetAddup.Activate
xlSheetAddup.Name = "测试表"
Dim strFileNameForFilter As String
Dim strDir, strSearchPath As String
Dim r As Integer
r = 1
strSearchPath = "c:\temp\多张表"
strDir = Dir(strSearchPath & "*.xls", vbNormal + vbHidden + vbReadOnly + vbSystem)
Do While strDir <> ""
strFileNameForFilter = "c:\temp\" & strDir
Dim xlAppFilter As Excel.Application
Dim xlBookFilter As Excel.Workbook
Dim xlSheetFilter As Excel.Worksheet
Set xlAppFilter = CreateObject("Excel.Application") '创建EXCEL应用类
Set xlBookFilter = xlAppFilter.Workbooks.Open(strFileNameForFilter)
xlBookFilter.Activate
Dim i, Count As Integer
Count = xlBookFilter.Sheets.Count
For i = 1 To Count
xlSheetsFilter = xlBookFilter.Sheets(i)
xlSheetAddup.Cells(r, 1) = xlSheetsFilter.Name
r = r + 1
Next
xlBookFilter.Close (True)
Set xlSheetFilter = Nothing
Set xlBookFilter = Nothing
xlAppFilter.Quit '关闭EXCEL应用类
Set xlAppFilter = Nothing '释放EXCEL应用类
strDir = Dir
Loop
Set xlSheetAddup = Nothing
xlBookAddup.SaveAs ("c:\temp\测试汇总表.xls") '保存文件
xlBookAddup.Close (True) '关闭EXCEL工作簿
xlApp.Quit '关闭EXCEL应用类
Set xlApp = Nothing '释放EXCEL应用类