VBA Code to run the same Macro to all Excel file in the same folder and save and close

KunaiForce

New Member
Joined
May 13, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
I am trying to run a macro to

1. Pick a folder where all the excels are saved
2. Starting with the first open Excel, create a pivot table on that Excel.
3. Open the next file and create the same pivot table. Save and Close
4. Loop until all excel files have that pivot table in the file directory.

Any help would be appreciated!!!!!!


VBA Code:
    Sub RunOnAllFilesInFolder()
    Dim folderName As String, eApp As Excel.Application, fileName As String
    Dim wb As Workbook, ws As Worksheet, currWs As Worksheet, currWb As Workbook
    Dim fDialog As Object: Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
    Set currWb = ActiveWorkbook: Set currWs = ActiveSheet
 
    'Select folder in which all files are stored
    fDialog.Title = "Select a folder"
    fDialog.InitialFileName = currWb.Path
    If fDialog.Show = -1 Then
      folderName = fDialog.SelectedItems(1)
    End If
    
    
    'Create a separate Excel process that is invisibile
    Set eApp = New Excel.Application:  eApp.Visible = False
    
    'Search for all files in folder [replace *.* with your pattern e.g. *.xlsx]
    fileName = Dir(folderName & "\*.xlsx")
    Do While fileName <> ""
        'Update status bar to indicate progress
        Application.StatusBar = "Processing " & folderName & "\" & fileName
 
 
        Set wb = eApp.Workbooks.Open(folderName & "\" & fileName)
        
        ActiveSheet.Name = "Detail"

Dim PSheet As Worksheet
Dim DSheet As Worksheet
Dim PCache As PivotCache
Dim PTable As PivotTable
Dim PRange As Range
Dim lastRow As Long
Dim LastCol As Long


On Error Resume Next
Application.DisplayAlerts = False
Worksheets("PivotTable").Delete
Sheets.Add Before:=ActiveSheet
ActiveSheet.Name = "Pivot Table Sum"
Application.DisplayAlerts = True
Set PSheet = Worksheets("Pivot Table Sum")
Set DSheet = Worksheets("Detail")


lastRow = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastCol = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PRange = DSheet.Cells(1, 1).Resize(lastRow, LastCol)


Set PCache = ActiveWorkbook.PivotCaches.Create _
(SourceType:=xlDatabase, SourceData:=PRange). _
CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), _
TableName:="OldPivotTable")


Set PTable = PCache.CreatePivotTable _
(TableDestination:=PSheet.Cells(1, 1), TableName:="OldPivotTable")


With ActiveSheet.PivotTables("OldPivotTable").PivotFields("EMPLID")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("OldPivotTable").PivotFields("EMPL_RCD")
.Orientation = xlRowField
.Position = 2
End With

With ActiveSheet.PivotTables("OldPivotTable").PivotFields("FISCAL_YEAR")
.Orientation = xlRowField
.Position = 3
End With


With ActiveSheet.PivotTables("OldPivotTable").PivotFields("MONETARY_AMOUNT")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "###0.00"
End With

ActiveSheet.PivotTables("SalesPivotTable").ShowTableStyleRowStripes = True
ActiveSheet.PivotTables("SalesPivotTable").TableStyle2 = "PivotStyleMedium9"

Call TurnOffPTSubs
ActiveSheet.PivotTables("OldPivotTable").RepeatAllLabels xlRepeatLabels
ActiveSheet.PivotTables("OldPivotTable").RowAxisLayout xlTabularRow

              
        wb.Close SaveChanges:=True 'Close opened worbook w/o saving, change as needed
        Debug.Print "Processed " & folderName & "\" & fileName
        fileName = Dir()
    Loop
    eApp.Quit
    Set eApp = Nothing
    'Clear statusbar and notify of macro completion
    Application.StatusBar = ""
    MsgBox "Completed executing macro on all workbooks"
End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top