slatefamily
asked on
Want to write a macro in Access to format Excel Spreadsheet
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_HC H()
'
' Macro2 Macro
'
'
Workbooks.Open FileName:= _
"P:\Newborn Service Line Reports\NewbornServiceLine Report_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
Sub FormatServiceLineReport_HC
'
' Macro2 Macro
'
'
Workbooks.Open FileName:= _
"P:\Newborn Service Line Reports\NewbornServiceLine
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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?)
Is that my worksheet? Would I format it like this:
Set xlWS = xlWB.Worksheets(Pivot)
(if pivot is the name of my sheet?)
ASKER
I am asking bc I am formatting 3 sheets within one workbook
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")
Set xlWS = xlWB.Worksheets(1)
for the first worksheet in the workbook, or the name
Set xlWS = xlWB.Worksheets("Pivot")
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?
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?
ASKER
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
That code just appears to be repeating the same (or very similar) formatting.
What formatting are you trying to apply to each worksheet?
What formatting are you trying to apply to each worksheet?
ASKER
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.
ASKER
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
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.
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
That's only a very small part, but past that point it all gets a bit confusing.:)
ASKER
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
MacroError.doc
ASKER
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
Set xlWS = xlWB.Worksheets(Pivot)
Compile Error- variable not defined
ASKER
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
With xlWB.PageSetup
Run time error - object doesn't support
ASKER
Can I add in landscape orientation and my header?
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.
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.
ASKER
I got an error when I tried the binding. I looked at my references and I have objects 12.0 checked.
What error did you get?
Did you remove the declarations of the constants that I included in the late-binding version?
Did you remove the declarations of the constants that I included in the late-binding version?
ASKER
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.
Open in new window