Loop through files in a folder

Question

I want to format each Excel file containing different worksheets in my current folder.

This code will set the page setup for each worksheet in each file to a scale of 1 page wide by 1 page tall, set page orientation to landscape and then save it.

I recorded the code for the first worksheet of the first excel file:

Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftMargin = Application.InchesToPoints(0.08)
        .RightMargin = Application.InchesToPoints(0.08)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Application.PrintCommunication = True
    ActiveWorkbook.Save
End Sub

How do I edit this code so that it works on all the worksheets of each Excel workbook in my folder?

You should search for "how to loop through closed workbooks and change formatting" or something similar (I'm just guessing at that search query). Then try that and if you have issues, ask us to help with the issues. This isn't a coding service so much as a teaching and troubleshooting one. :-) — Rodger, Jul 20 '16 at 19:33
@Rodger's on it - simply, look how to loop through all sheets in a workbook (it's pretty much three lines), and also [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) (specifically, look for the answer showing how to set a Worksheet variable). Thankfully, you don't have to tweak your code too much, AFAICT. — BruceWayne, Jul 20 '16 at 19:39
Please don't use the [macros] tag for Excel/VBA questions. The tag info for it says, `*Not for MS-OFFICE / VBA / macro languages. Use the respective tags instead.*` — Alex Knauth, Jul 22 '16 at 00:01

score 0 · Answer 1 · 2016-07-21T20:08:54.910

Sub ProcessFiles()
    Dim FolderPath As String
    FolderPath = "Your Folder Path Goes Here"
    ToggleEvents False

    Dim FileName As String, xlFilename As String
    Dim xlWB As Excel.Workbook, xlWS As Excel.Worksheet

    If Not Right(FolderPath, 1) Then FolderPath = FolderPath & "\"

    FileName = Dir(FolderPath, vbDirectory)

    Do While FileName <> ""
        xlFilename = FolderPath & FileName
        Set xlWB = Workbooks.Open(xlFilename)
        For Each xlWS In xlWB.Worksheets
            ModifyPageSetup xlWS
        Next

        xlWB.Close True

    Loop

    Application.PrintCommunication = True
    ToggleEvents True
End Sub

Sub ToggleEvents(EnableEvents As Boolean)

    With Application
        .EnableEvents = EnableEvents
        .Calculation = IIf(EnableEvents, xlCalculationAutomatic, xlCalculationManual)
        .ScreenUpdating = EnableEvents
    End With

End Sub

Sub ModifyPageSetup(xlWS As Excel.Worksheet)

    With xlWS.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
        Application.PrintCommunication = True
        .PageSetup.PrintArea = ""
        Application.PrintCommunication = False
    End With


    With xlWS.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftMargin = Application.InchesToPoints(0.08)
        .RightMargin = Application.InchesToPoints(0.08)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With

End Sub

where should I enter the path of my folder in the above code? — Karan, Jul 21 '16 at 13:09
I updated my answer to make it clear as to where to put your folder path. — , Jul 21 '16 at 20:09

Loop through files in a folder

1 Answers1