We help IT Professionals succeed at work.

Want to write a macro in Access to format Excel Spreadsheet

slatefamily
slatefamily asked
on
I recorded a macro in excel to format spreadsheets, instead of opening each spreadsheet to run macro can I do this from access? (this is a snipit) but i'm getting an error Compile Error (Sub or Function not defined)

Sub FormatServiceLineReport_HCH()
'
' Macro2 Macro
'

'
    Workbooks.Open FileName:= _
        "P:\Newborn Service Line Reports\NewbornServiceLineReport_HCH.xls"
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

End Sub
Comment
Watch Question

Michael VasilevskySolutions Architect
CERTIFIED EXPERT

Commented:
Try the below, and make sure you've referenced the Excel library! (Tools -> References ->Microsoft Excel XX.0 Object Library); XX depends on which version of Excel you have installed.
Function FormatExcel
    Dim xcelwb As Excel.Workbook
    Dim xcelapp As Excel.Application
    Dim Sheet As Excel.Worksheet
    Dim rng As Excel.Range
    
    Set xcelapp = New Excel.Application
    Set xcelwb = xcelapp.Workbooks.Open("C:\MyExcel.xls")
    Set Sheet = xcelapp.ActiveWorkbook.Sheets(1)
    
    '>>>>>>>>>>>>>>>>format the excel data>>>>>>>>>>>>>>>>>>>
    With Sheet
        Set rng = .Cells("A1:B10")
        With rng
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        end with
    end with    

    xcelwb.Save
    xcelapp.Visible = True
    Set xcelapp = Nothing
    Set xcelwb = Nothing
    Set Sheet = Nothing
    Set rng = Nothing
End Function

Open in new window

Analyst Assistant
CERTIFIED EXPERT
Commented:
Yes you can do this from Access.

You can either use late-binding or early binding.

For early binding you'll need to set a reference to MS Excel X.0 Object Library under Tools>References...

The X.0 will depend on which version of Excel you have, for example it's 14.0 for Excel 2010.

The code would look something like this:
Sub FormatServiceLineReport_HCH()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

      Set xlApp = New Excel.Application
       
      Set xlWB = xlApp.Workbooks.Open("P:\Newborn Service Line Reports\NewbornServiceLineReport_HCH.xls")

      Set xlWS =xlWB.Worksheets(1)

      With xlWS.UsedRange
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
     End With     

     xlWB.Close SaveChanges:= True

     Set xlWB = Nothing
 
     xlApp.Quit

     Set xlApp = Nothing

End Sub

Open in new window

If you wanted to use late-binding the code would be slightly different.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Here's the late-binding version.
Sub FormatServiceLineReport_HCH()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object

Const xlGeneral = 1
Const xlBottom = -4107
Const xlContext = -5002

      Set xlApp = CreateObject("Excel.Application")
       
      Set xlWB = xlApp.Workbooks.Open("P:\Newborn Service Line Reports\NewbornServiceLineReport_HCH.xls")

      Set xlWS = xlWB.Worksheets(1)

      With xlWS.UsedRange
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
     End With

     xlWB.Close SaveChanges:=True

     Set xlWB = Nothing
 
     xlApp.Quit

     Set xlApp = Nothing

End Sub

Open in new window

Author

Commented:
Set xlWS = xlWB.Worksheets(1)

Is that my worksheet?  Would I format it like this:

Set xlWS = xlWB.Worksheets(Pivot)

(if pivot is the name of my sheet?)

Author

Commented:
I am asking bc I am formatting 3 sheets within one workbook
Michael VasilevskySolutions Architect
CERTIFIED EXPERT

Commented:
You can use the index of the worksheet:

Set xlWS = xlWB.Worksheets(1)

for the first worksheet in the workbook, or the name

Set xlWS = xlWB.Worksheets("Pivot")
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
I assumed there was only one worksheet in the workbook, perhaps an exported table/query.

Anyway, in the code I posted the 1 is the worksheet index and refers to the 1st worksheet.

If you wanted to use a worksheet called Pivot you would use:

Set xlWS = xlWB.Worksheets("Pivot")

You mention 3 worksheets though, do you want to apply the same formatting to those worksheets?

Are they the only worksheets in the workbook?

Author

Commented:
I put in my whole excel macro below (I just recorded a macro as I formatted each worksheet).  I am sure there is a better way for me to do this but i'm not sure.  I added in a header and footer, bolded the top lines, made sure all the columns were formatted, and I made it printer pretty.
Sub FormatServiceLineReport_HCH()
'
' Macro2 Macro
'

'
    Workbooks.Open Filename:= _
        "P:\Newborn Service Line Reports\NewbornServiceLineReport_HCH.xls"
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("E2").Select
    Columns("E:E").ColumnWidth = 35.29
    Columns("A:A").ColumnWidth = 17.71
    Columns("I:I").ColumnWidth = 20.14
    Range("A1:I1").Select
    Range("I1").Activate
    Selection.Font.Bold = True
    Columns("C:C").ColumnWidth = 9.14
    Columns("C:C").ColumnWidth = 11.14
    Range("H7").Select
    Rows("2:2").EntireRow.AutoFit
    Cells.Select
    Cells.EntireRow.AutoFit
    ActiveWindow.View = xlPageBreakPreview
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&A"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
    Range("E5").Select
    Columns("E:E").ColumnWidth = 26.29
    ActiveWindow.View = xlNormalView
    ActiveWindow.View = xlPageLayoutView
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "Newborn Service Line Report" & Chr(10) & ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 93
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Range("A2").Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "Newborn Service Line Report" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 93
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Range("E3").Select
    Selection.Font.Bold = True
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&18Newborn Service Line Report" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 93
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Range("E11").Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 93
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Range("E7").Select
    ActiveWindow.View = xlPageBreakPreview
    ActiveWindow.View = xlNormalView
    Sheets("Pivot").Select
    Columns("A:A").EntireColumn.AutoFit
    Columns("B:B").EntireColumn.AutoFit
    Columns("C:C").EntireColumn.AutoFit
    Cells.Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("1:1").Select
    Selection.Font.Bold = True
    ActiveWindow.View = xlPageLayoutView
    Selection.Font.Bold = True
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Range("C7").Select
    Sheets("Newborn_Service_Line_Report").Select
    Cells.Select
    Cells.EntireColumn.AutoFit
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.ColumnWidth = 18.14
    Range("E9").Select
    Columns("F:F").ColumnWidth = 7.29
    Columns("G:G").ColumnWidth = 11.14
    Columns("H:H").ColumnWidth = 10
    Columns("I:I").ColumnWidth = 8.29
    Rows("1:1").EntireRow.AutoFit
    Columns("I:I").ColumnWidth = 8.86
    Rows("1:1").Select
    Selection.Font.Bold = True
    Rows("1:2").Select
    Range("A2").Activate
    ActiveWindow.FreezePanes = True
    ActiveWindow.SmallScroll Down:=-24
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&A"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Columns("C:C").ColumnWidth = 9.71
    Columns("C:C").ColumnWidth = 10.71
    Columns("D:D").ColumnWidth = 14
    Columns("B:B").ColumnWidth = 12
    ActiveWindow.View = xlPageBreakPreview
    ActiveWindow.View = xlNormalView
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&A"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = True
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Range("C7").Select
    ActiveWindow.SmallScroll Down:=-12
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveWindow.SmallScroll Down:=-15
    Range("A1").Select
    ActiveWindow.FreezePanes = False
    ActiveWindow.View = xlPageLayoutView
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "Newborn Service Line Report" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Range("D8").Select
    Selection.Font.Bold = True
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Range("E1").Select
    Sheets("Pivot").Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report- Count of Service Name" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Sheets("Needs_Correction").Select
    ActiveWindow.View = xlPageLayoutView
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report- Needs Service Name Correction" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 93
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Range("H8").Select
    Sheets("Pivot").Select
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report- Count by Service Name" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
End Sub

Open in new window

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
That code just appears to be repeating the same (or very similar) formatting.

What formatting are you trying to apply to each worksheet?

Author

Commented:
Seems when I add in the rest of my code it is hanging up on Range (compile error sub or function not defined)  If this question is outside of what I was asking just let me know and I'll create a new question.  

Author

Commented:
I am added in header and footer, bolding the top line, put the layout to landscape, put the top row to repeat on top, made sure that it was page break was good for printing
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
It's not really outside the question, though it does take things a little further.

Also, a couple of things aren't quite clear.

Which worksheets do you want to apply the formatting to?

Is it basically the same formatting but with some changes, eg different headers?

Where should the page breaks go?

There's quite a bit of code to go through as well, but that's one of the bad things about using the macro recorder - it records everything you do.

You can probaby start tidying up the code a bit.

Here's a small example of how you can do that.
Option Explicit

Sub FormatServiceLineReport_HCH()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet


    Set xlApp = New Excel.Application

    Set xlWB = xlApp.Workbooks.Open("P:\Newborn Service Line Reports\NewbornServiceLineReport_HCH.xls")

    Set xlWS = xlWB.Worksheets("Pivot")

    With xlWS.UsedRange
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

    With xlWS
        .Range("A1:I1").Font.Bold = True
        .Columns("A:A").ColumnWidth = 17.71
        .Columns("C:C").ColumnWidth = 9.14
        .Columns("E:E").ColumnWidth = 35.29
        .Columns("I:I").ColumnWidth = 20.14
        .Rows("2:2").EntireRow.AutoFit
        .Cells.EntireRow.AutoFit
    End With

Open in new window

That's only a very small part, but past that point it all gets a bit confusing.:)

Author

Commented:
This is the code I came up with, now I am getting an error that the macros are disabled in this project.
Sub FormatServiceLineReport_HCH()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object

Const xlGeneral = 1
Const xlBottom = -4107
Const xlContext = -5002

      Set xlApp = CreateObject("Excel.Application")
       
      Set xlWB = xlApp.Workbooks.Open("P:\Newborn Service Line Reports\NewbornServiceLineReport_HCH.xls")

      Set xlWS = xlWB.Worksheets(Pivot)

      With xlWS.UsedRange
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = xlLandscape
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False

    With xlWS
        .Range("A1:I1").Font.Bold = True
        .Columns("A:A").ColumnWidth = 17.71
        .Columns("C:C").ColumnWidth = 9.14
        .Columns("E:E").ColumnWidth = 35.29
        .Columns("I:I").ColumnWidth = 20.14
        .Rows("2:2").EntireRow.AutoFit
        .Cells.EntireRow.AutoFit
    End With

     
 
    With xlWB.PageSetup

        .LeftHeader = ""
        .CenterHeader = _
        "&""MS Sans Serif,Bold""&12Newborn Service Line Report- Count by Service Name" & Chr(10) & "Holy Cross Hospital"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With

xlWB.Close SaveChanges:=True

     Set xlWB = Nothing
 
     xlApp.Quit

     Set xlApp = Nothing

End Sub

Open in new window

MacroError.doc

Author

Commented:
ok I figured out the macro error.  Now I am getting hung on this part:

     Set xlWS = xlWB.Worksheets(Pivot)

Compile Error- variable not defined

Author

Commented:
ok I figured out the Pivot error (it wasn't in quotations).  Now I am getting hung on this part:

 With xlWB.PageSetup
Run time error - object doesn't support

Author

Commented:
Can I add in landscape orientation and my header?
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Pivot shuld be in quotes.

Yes you can add orientation and the header.

Try recording a macro when you do only that, nothing else.

By the way, you really should try using early binding as the first example I posted does.

That will make it easier to write the code as you'll get Intellisense and all the constants, eg xlLandscape, will be defined.

Author

Commented:
I got an error when I tried the binding.  I looked at my references and I have objects 12.0 checked.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
What error did you get?

Did you remove the declarations of the constants that I included in the late-binding version?

Author

Commented:
I think I got my question answered, and should move on to another question since this is multi-faceted.  Thank you so much for all the information you provided me.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.