0

I have 200 Excel files in one folder, with file extensions .xls, .xlsx, and .xlsm. They were all typed in letter page size and I need to convert all of them to legal page size (landscape orientation). Is there a way to convert the page size for all files at once using VBA? So I don't have to open each file and change the page size one file at a time? Thank you.

4

1 Answer 1

0

I found a Youtube video that showed "How to edit multiple excel files in a folder automatically" --> Credit: https://www.youtube.com/watch?v=gIIj5-267uI

My file name is zzz.xlsm

And was able to edit the code to change page size:

Sub LoopThroughDirectorytoEditOriginal()
Dim MyFile As String
Dim Filepath As String
Dim q As Long

Filepath = "C:\TSS\"

MyFile = Dir(Filepath)

Do While Len(MyFile) > 0
If MyFile = "zzz.xlsm" Then
Exit Sub
End If

Workbooks.Open (Filepath & MyFile)
For q = 1 To Application.Worksheets.Count
Worksheets(q).Activate
ActiveSheet.PageSetup.PaperSize = xlPaperLegal
ActiveSheet.PageSetup.Orientation = xlLandscape
Next q
ActiveWorkbook.Save
ActiveWorkbook.Close
MyFile = Dir
Loop
End Sub
1
  • If this answered your question then click the green check mark on the left to show that this question is closed. This will give you points!
    – HackSlash
    Jun 8, 2020 at 20:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .