Excel 4 Macro Page.Setup Error

[nikki333]

Hi Folks

Since using Application.PageSetup is incredibly slow, I'm trying to use the ExcecuteExcel4Macro "PAGE.SETUP() Function. It works for the header, but no matter what the value of the footer is (even ""), I get a 1004 Error (unknown name).

sub test()

'Variablen dimensionieren/speichern
        Dim rngDruckbereich As Range
        Dim strDruckBereich As String
        Dim numOrientierung As Long: numOrientierung = 1    'xlPortrait (1 = xlPortrait, 2 = xlLandScape)
        Dim blnSchwarzWeiss As Boolean: blnSchwarzWeiss = False
        Dim blnZentrierungHorizontal As Boolean: blnZentrierungHorizontal = True
        Dim blnZentrierungVertikal As Boolean: blnZentrierungVertikal = False
        Dim blnSkalierungBreite As Boolean: blnSkalierungBreite = True
        Dim blnSkalierungHöhe As Boolean: blnSkalierungHöhe = False
        Dim blnEntwurf As Boolean: blnEntwurf = False
        Dim blnNotizen As Boolean: blnNotizen = False
        Dim numPapierFormat As Long: numPapierFormat = 9    'xlPaperSize (9 = DinA4)
        Dim strSeitenSetup As String
        Dim strKopfZeile As String
        Dim strFussZeile As String
        Dim numRandLinksInch As Single
        Dim numRandRechtsInch As Single
        Dim numRandObenInch As Single
        Dim numRandUntenInch As Single
        Dim numRandKopfInch As Single
        Dim numRandFussInch As Single
        With WorksheetFunction
            numRandLinksInch = .Convert(numRandLinks, "cm", "in")
            numRandRechtsInch = .Convert(numRandRechts, "cm", "in")
            numRandObenInch = .Convert(numRandOben, "cm", "in")
            numRandUntenInch = .Convert(numRandUnten, "cm", "in")
            numRandKopfInch = .Convert(numRandKopf, "cm", "in")
            numRandFussInch = .Convert(numRandFuss, "cm", "in")
        End With
        subGlobaleVariablenSpeichern
        
        'Kopfzeile
        strKopfZeile = "&L&""Calibri Light""&07&""#595959""" & "&B" & "Lindt && Sprüngli (Schweiz) AG" & "&B" _
        & vbLf & wbRüstliste.Name & "&C" & "&R" & wbRüstliste.ContentTypeProperties("Document Type Txt").Value _
        & " zum Prozess:" & vbLf & wbRüstliste.ContentTypeProperties("Processes Txt").Value
        
        'Fusszeile
         strFussZeile = "&L&""Calibri Light""&07&""#595959""" & "erstellt von: " _
         & wbRüstliste.ContentTypeProperties("Contributors").Value & vbLf & _
         "freigegeben von: " & wbRüstliste.ContentTypeProperties("Approved By").Value & vbLf & _
         "Datum: " & wbRüstliste.ContentTypeProperties("Approval Date").Value & " / Version: " & _
         wbRüstliste.ContentTypeProperties("Document Version").Value & "&C" & "&R" _
         & wbRüstliste.ContentTypeProperties("Document ID Value") & vbLf & "Rüstliste " _
         & wsHelfer.Range("B30") & vbLf & "Seite &P von &N"
        
        'Argumete für ExecuteExcel4Macro(PAGE.SETUP(head, foot, left, right, top, bot, hdng, grid, h_cntr, v_cntr, _
        orient, paper_size, scale, pg_num, pg_order, bw_cells, quality, head_margin, foot_margin, notes, draft)

'This gives an error: 

        strSeitenSetup = "PAGE.SETUP(strKopfZeile,strFussZeile,numRandLinksInch,numRandRechtsInch,numRandObenInch," _
        & "numRandUntenInch,,,blnZentrierungHorizontal,blnZentrierungVertikal,numOrientierung,numPapierFormat,,,,," _
        & "blnSchwarzWeiss,,numRandKopfInch,numRandFussInch,blnNotizen,blnEntwurf)"

'This works (leaving the 2nd argument empty)

        strSeitenSetup = "PAGE.SETUP(strKopfZeile,,numRandLinksInch,numRandRechtsInch,numRandObenInch," _        
        & "numRandUntenInch,,,blnZentrierungHorizontal,blnZentrierungVertikal,numOrientierung,numPapierFormat,,,,," _
        & "blnSchwarzWeiss,,numRandKopfInch,numRandFussInch,blnNotizen,blnEntwurf)"

       ExecuteExcel4Macro strSeitenSetup

end sub

Any ideas what I'm missing here?

[nikki333]

Just found it: There's a 255 Character limit for the string to execute