循环浏览同一文件夹中的工作簿，并对All-VBA执行相同的Excel任务 [英] Loop Through Workbooks in the Same Folder and Do the Same Excel Task for All-VBA

问题描述

我需要50多个文件来创建数据透视表，并且每个文件都有相同的确切内容，但内容不同.到目前为止，我已经完成了创建数据透视表的代码，并且在单独运行时效果很好，但是，当我尝试为同一文件夹中的所有工作簿运行代码时，它失败了.我不知道发生了什么，为什么它继续显示即使路径名没有问题也找不到文件.

I have more than 50 files needed to create the pivot table and each file has the same exact formort with different contents. So far, I have finished creating the code for the pivot and it works very well when running alone, however, it failed when I tried to run the code for all workbooks in the same folder. I don't know what happened and why it kept showing that no files could be found despite nothing wrong about the pathname.

Sub DoAllFiles()
Dim Filename, Pathname As String
Dim WB As Workbook

Pathname = "D:\Reports"   
Filename = Dir(Pathname & "\*.xls*")
Do While Filename <> ""

Application.DisplayAlerts = False
Application.ScreenUpdating = False
    Set WB = Workbooks.Open(Pathname & Filename)  'open all files
    PivotX WB
    WB.Close SaveChanges:=True

Application.DisplayAlerts = True
Application.ScreenUpdating = True
    Filename = Dir()
Loop
End Sub

这是数据透视的代码，单独运行时效果很好:

Here is the code for pivot and it works very well when running it alone:

Sub PivotX(WB As Workbook)
Dim Lrow, Lcol As Long
Dim wsData As Worksheet
Dim rngRaw As Range
Dim PvtTabCache As PivotCache
Dim PvtTab As PivotTable
Dim wsPvtTab As Worksheet
Dim PvtFld As PivotField

Set wsData = ActiveSheet
Lrow = wsData.Cells(Rows.Count, "B").End(xlUp).Row
Lcol = wsData.Cells(1, Columns.Count).End(xlToLeft).Column
Set rngRaw = wsData.Range(Cells(1, 1), Cells(Lrow, Lcol))
Set wsPvtTab = Worksheets.Add
wsData.Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rngRaw, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=wsPvtTab.Range("A3"), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12

Set PvtTab = wsPvtTab.PivotTables("PivotTable1")

PvtTab.ManualUpdate = True
Set PvtFld = PvtTab.PivotFields("Month")
PvtFld.Orientation = xlPageField
PvtTab.PivotFields("Month").ClearAllFilters

Set PvtFld = PvtTab.PivotFields("Year")
PvtFld.Orientation = xlPageField
PvtTab.PivotFields("Year").ClearAllFilters

Set PvtFld = PvtTab.PivotFields("Fund_Code")
PvtFld.Orientation = xlRowField
PvtFld.Position = 1

Set PvtFld = PvtTab.PivotFields("Curr")
PvtFld.Orientation = xlColumnField
PvtFld.Position = 1
wsPvtTab.PivotTables("PivotTable1").PivotFields("Curr").PivotItems("USD").Position = 1

With PvtTab.PivotFields("Trx_Amount")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0;[red](#,##0)"
End With

wsPvtTab.PivotTables("Pivottable1").RowAxisLayout xlTabularRow

'Remove grand total
wsPvtTab.PivotTables("Pivottable1").RowGrand = False

For Each PvtTbCache In ActiveWorkbook.PivotCaches
    On Error Resume Next
    PvtTbCache.Refresh
Next PvtTbCache

'Determine filter value
Set PvtFld = PvtTab.PivotFields("Year")
PvtFld.ClearAllFilters
PvtFld.EnableMultiplePageItems = True
  With PvtFld
    .AutoSort xlmnual, .SourceName
    For Each Pi In PvtFld.PivotItems
            Select Case Pi.Name
                Case "2014"
                Case Else
                    Pi.Visible = False
            End Select
    Next Pi
    .AutoSort xlAscending, .SourceName
  End With

'determine filter value
Set PvtFld = PvtTab.PivotFields("Month")
PvtFld.ClearAllFilters
PvtFld.EnableMultiplePageItems = True
  With PvtFld
    .AutoSort xlmnual, .SourceName
    For Each Pi In PvtFld.PivotItems
            Select Case Pi.Name
                Case "11"
                Case Else
                    Pi.Visible = False
            End Select
    Next Pi
    .AutoSort xlAscending, .SourceName
  End With
PvtTab.ManualUpdate = False
End Sub

任何帮助将不胜感激.提前非常感谢您.

Any help would be very much appreciated. Thank you very much in advance.

推荐答案

这应该可以解决您的问题:

This should solve your problem:

Set WB = Workbooks.Open(Pathname & "\" & Filename)

由于某些原因，当我尝试使用您的代码时，它没有保留您在"Filename"变量开头添加的反斜杠.那可以解释为什么VBA找不到文件.重新添加它应该在路径名和文件名之间使它能够正常工作

When I tried using your code, for some reason, it did not retain the backslash you put at the beginning of the "Filename" variable. That would explain why VBA couldn't find the files. Adding it back should between the path name and file name should make it work correctly

这篇关于循环浏览同一文件夹中的工作簿，并对All-VBA执行相同的Excel任务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！