Access/Excel Custom Print Headers

[cubs1969]

I run the following code to open up a macro from access to excel:

Set appxcelA1 = GetObject(Full_macro1)
appxcelA1.Application.Run "'" & Full_macro & "'" & "!fixheaders"

Then it runs an excel macro to change the date on the custom print headers for each sheet. When i run on my computer it works. when i run the following code on another PC, I get a runtime error of 1004: The program stops where the For loop starts....

Sub fixHeaders()
    Dim sht As Worksheet
 
    'wkEndDte = InputBox("Enter a date")
 
    D = Date - 7 + (7 - (Weekday(Date)))    'Saturday
 
    wkEndDte = Mid(D, 1, 2) & "-" & Mid(D, 4, 2) & "-" & Mid(D, 7, 4)
 
    For Each sht In Sheets
        n = sht.Name
 
        If n <> "Sheet1" And n <> "TOH-Productivity" And n <> "Glossary" Then
            Sheets(n).Select
 
            With ActiveSheet.PageSetup
                .PrintTitleRows = "$1:$3"
                .PrintTitleColumns = ""
            End With
 
            ActiveSheet.PageSetup.PrintArea = "$B$1:$AA$116"
 
            With ActiveSheet.PageSetup
                .LeftHeader = ""
                .CenterHeader = "&16&A"
                .RightHeader = "&""Arial,Bold""&14Week Ending" & " " & wkEndDte
                '.RightHeader = "&""Arial,Bold""&14Week Ending 03-26-2011"
                .LeftFooter = ""
                .CenterFooter = "Page &P of &N"
                .RightFooter = ""
                .LeftMargin = Application.InchesToPoints(0.25)
                .RightMargin = Application.InchesToPoints(0.25)
                .TopMargin = Application.InchesToPoints(0.75)
                .BottomMargin = Application.InchesToPoints(0.75)
                .HeaderMargin = Application.InchesToPoints(0.5)
                .FooterMargin = Application.InchesToPoints(0.5)
                .PrintHeadings = False
                .PrintGridlines = False
                .PrintComments = xlPrintNoComments
                .PrintQuality = 600
                .CenterHorizontally = True
                .CenterVertically = False
                .Orientation = xlLandscape
                .Draft = False
                .PaperSize = xlPaperLetter
                .FirstPageNumber = xlAutomatic
                .Order = xlDownThenOver
                .BlackAndWhite = False
                .Zoom = False
                .FitToPagesWide = 1
                .FitToPagesTall = 2
                .PrintErrors = xlPrintErrorsDisplayed
            End With
 
        End If
 
    Next sht
 
End Sub

 

[boblarson]

(Next time please use code tags when posting large bits of code)

I am not sure why it would fail on that line but I am wondering why you are trying to run an Excel macro from Access when you could do the same formatting from within Access and therefore take Excel and its macros out of the equation.