Excel 4 Macro Page.Setup Error
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).
Code:
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?
