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.
-
1Yes. There are many ways. What have you tried so far?– HackSlashJun 5, 2020 at 23:00
-
Welcome to SuperUser. In order to get better answers please read superuser.com/help/how-to-ask . This will avoid much frustration.– HackSlashJun 5, 2020 at 23:01
-
this is what you're looking for docs.microsoft.com/en-us/office/vba/api/excel.pagesetup– Alex MJun 5, 2020 at 23:09
-
@HackSlash I was looking at this article: superuser.com/questions/480225/… I want to do something like the code in that article but I don't write VBA regularly so I am unable to modify the code for excel. I only can create a macro that I can run one file at a time at this time.– a_rain_aJun 6, 2020 at 3:16
Add a comment
|
1 Answer
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
-
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! Jun 8, 2020 at 20:44