CODEWITHSUNDEEP
vbaexcel
ytk
ytk

Reputation: 2827

automate changing of page layout orientation of excel and printing

I am generating a bunch of excel workbooks, each with one sheet, in R using the openxlsx package. Now to print these out, I have to open up each of these files, go to Page Layout > Orientation > Landscape, then go to File > Print > Scaling > Fit All Columns on One Page and then print it. It gets really tedious, especially when I'm generating over 200 excel files. Is there any way to automate this? None of the R packages I looked at seem to have a way to do this, and I'm open to any language/tool.

Upvotes: 0

Views: 471

Answers (1)

user2906801
user2906801

Reputation:

I would say the best way would be to record the majority of what you want as a Macro (the orientation and printing) and then using VBA to open each of the files call that macro you recorded and then close the file once it has been printed.

Sub Macro()
Dim fpath As String

fpath = Worksheets("Sheet1").Range("H4").Value 

'I use a file that has a file path cell which helps with dealing with lots of sheets

.DisplayAlerts = False
.ScreenUpdating = False

Workbooks.Open Filename:= _
    fpath & "\" & "Test File.xlsx"
    Sheets("Sheet1").Select
   'Call the macro at this point 
    Windows("Test File.xlsx").Activate
    ActiveWorkbook.Close

.DisplayAlerts = True
.ScreenUpdating = True

End Sub

I tested it very quickly and it seemed to work well for me.

But there may be an easier way of going about it, but that's how I would approach it

Upvotes: 1

Related Questions