Formatting Page Setup with Multiple Worksheets and Multiple Workbooks 1

[lube8]

I have several workbooks with several worksheets and each workbook contains a different number of worksheets. I am having a tough time getting the code to format for each sheet within the workbook. I am trying to change margins, make orientation to landscape, repeat top row, and either fit to one page or "best fit".

I believe I can only do formatting on an active sheet so I think I need the code to cycle through each sheet and apply the formatting. Do you have any ideas how to make this work?

The script I have tried makes the procedure either run away (only formatting the first sheet) or gives me errors with object definition ... (but I think I now have that corrected).

 

[TonyJollans]

Hi lube8,

Let's see the code - then maybe we can see what's wrong. I don't think you're trying to do anything difficult; at a simple level ..

For each wb in application.workbooks
  For each sht in wb.Sheets
    sht.pagesetup.orientation = xllandscape
  Next
Next[code][/color]

 .. should work.  You'll probably want to make it ignore hidden workbooks (and maybe hidden sheets), and I don't know what else.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
[COLOR=darkgreen]We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.[/color]

 

[lube8]

Tony,

I am having a tough time with the objects and assigning them to make your code work or the code I have already tried. Here is my existing code:

Dim frmtpath As String
frmtpath = "D:\Documents and Settings\stl1cjl\My Documents\dbExtracts\300001.XLS"


Dim objexcelapp As New Excel.Application
Dim objexcel As Object
'create excel
Set objexcelapp = New Excel.Application
'open file
Set objexcel = objexcelapp.Workbooks.Open(frmtpath)
objexcelapp.Visible = True
Dim sht As Worksheet


With objexcel

For Each sht In .Worksheets

With sht.Columns("A:O&quot
.AutoFit
' FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$A1=""Account Total"""
With .FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.FormatConditions(1).Interior.ColorIndex = 36

End With

Next
End With

 

[TonyJollans]

Hi lube8,

Just ran your code and it worked fine. All I can think is it might be something to do with your workbook but I don't know what. Sorry!

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[lube8]

Tony,

I know this code works ... I don't know how to implement the page setup formatting from within this code. Do you know how to include any formatting for the page setup of each worksheet within the module I have included?

 

[TonyJollans]

Hi lube8,

Just put something like my code inside yours ..

With objexcel
    
For Each sht In .Worksheets
            
    With sht.Columns("A:O")
         :
         :
    End With

    With sht.pagesetup
        .orientation = xllandscape
        :
        :
    End With

Next
End With

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[xlbo]

lube8
A: Please stop posting questions on very similar subjects - it's all related.
B: I have given you more than enough info to be able to do this - this site is NOT a helpdesk. We are NOT here to hold your hand and do your job for you. This is a professional site for IT professionals and to that end, you are expected to be able to do some thinking for yourself. Look in your excel help files. Do some searching / research into loops and how they work. Use the record macro functionality to get the code for pagesetup and apply it to what I have already given you

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[lube8]

Sorry Geoff,

Tony and your help has been greatly appreciated. I have been able to implement much of the formatting with the exception of the zoom feature for each tab. I think each sheet needs to become active in order for the excel's worksheet zoom to be modified.

I got the margins to change with some digging (I cannot use the points-to-edge method but determined a quarter inch is in increments of 18 based on using the "margin = " per sht method. If I dig a little longer, I'm sure I can determine a method for changing the window zoom.

Thanks again! I have learned a great deal.