Using a macro to open another workbook and change page orientation.
Posted by MDL on May 23, 2001 3:19 PM
I have used the "record Macro" option to save a macro that opens another workbook and changes it's file orientation before printing. (The code is below.) The problem is that it changes the orientation on the workbook the macro is saved in, not in the workbook I just opened. Any idea what I'm doing wrong? TIA!
Workbooks.Open FileName:="\\FIL-MOR1\SYS\revenue\monthend\monthresult.csv"
With ActiveSheet.PageSetup
.PrintTitleRows = "$5:$7"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.CenterFooter = "Page &P of &N"
.Orientation = xlLandscape
End With
Range("C:D,K:K").Select
Range("K1").Activate
Selection.NumberFormat = "0.0%"
Range("E:F,H:I").Select
Range("H1").Activate
Selection.NumberFormat = "#,##0"
Range("G:G,J:J").Select
Range("J1").Activate
Selection.NumberFormat = "0.00"
Columns("C:K").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Rows("6:6").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("A4:K107").Select
Selection.Columns.AutoFit
Range("B1").Select
ActiveWorkbook.SaveAs FileName:="F:\revenue\monthend\monthresult.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Posted by Duane Kennerson on May 23, 2001 3:52 PM
I don't know if I'm right but I belive you have to make the workbook
your opening the active workbook in order for the macro to make the changes to it.
Other wise I think it opens the other workbook but the workbook with the macro remains the active workbook
until you close it or tell it other wise through your code.
Posted by Gypsy on May 23, 2001 4:36 PM
OK, I was trying to do that for a while - but how? nm
Posted by Duane Kennerson on May 23, 2001 5:05 PM
Re: OK, I was trying to do that for a while - but how? nm
Try putting
workbook("name of workbook").activate
put this at the beginning of the code. I think it
will work. (I hope)
Posted by Tom Urtis on May 23, 2001 7:42 PM
Re: OK, I was trying to do that for a while - but how? nm
Gypsy:
If your question is how to activate a particular workbook when more than one is open, the following line of code should do the trick, assuming you have the said file open, which would be the case if I am interpreting this post string correctly.
Insert this as your second line of code:
Windows("monthresult.csv").Activate
Hope this helps.
Tom Urtis
