VBScipt ExecuteExcel4Macro PAGE.SETUP Font, Style not working

[lcmcmillin]

Hello All,

I am currently using Excel 2007 on Windows 7. It should be noted that I am developing this script to run on multiple versions of Excel (2003,2007,2010) as well as multiple versions of Windows (XP,7 and possibly 8)

I really need someone's help with a "Working" example of an ExecuteExcel4Macro("PAGE.SETUP("")") command specifically when called from a Visual Basic Script.
Ultimately I am using VBScript to automate the creation and formatting of an excel file (Excel 2007).

The command needs to demonstrate:

1. A centered header, let's say Test Header
2. Specifying a 14pt Bold Arial Font​

I have tried dozens of combination attempting to achieve this, but most result in a Windows Scripting Host error:

The formula you typed contains an error. (So informative and helpful??)
Code: 800A03EC
Source: Microsoft Office Excel
​

While the error window is typed Windows Scripting Host, I believe the error is in either Excel or the Print driver
In my trials I have found that the PAGE.SETUP command can talk directly to the window printer driver.
Some of the parameters seems to be for Excel while others are specific to the printer driver.

In the code below the following MyString assignments work as follows: Where Qt = Chr( 34 )

Works correctly​

MyString = Qt & "&B&14Test Header" & Qt
Actual string: "Test Header"
Formatted as 14pt Bold Default Font​

Does Not work​

MyString = Qt & Qt & "&Arial,Bold" & Qt & "Test Header" & Qt
Actual string: ""&Arial,Bold"Test Header"​

Does Not work​

MyString = Qt & "&" & Qt & "Arial,Bold" & Qt & "Test Header" & Qt
Actual string: "&"Arial,Bold"Test Header"​

Does Not work​

MyString = Qt & "& " & Qt & "Arial,Bold" & Qt & "Test Header" & Qt
Actual string: "& "Arial,Bold"Test Header"​

From what documentation I could find for PAGE.SETUP, Font and Style should be: & "Font,Style"
Whether a space should follow the ampersand or not seems to be ambiguous.
The entire parameter should be "Quoted"

Nothing I have tried works??

Lastly does anyone know of a definitive reference for ExecuteExcel4Macro commands/options?

Any help would be greately appreciated.
Thanks


'Create Object

Dim objExcelApp
Set objExcelApp = Nothing
Set objExcelApp = CreateObject("Excel.Application") 'Create Excel​

'Open and Format an empty Sheet

With objExcelApp​

.ScreenUpdating = vbFalse
.Visible = vbFalse 'Make Excel visable
.Workbooks.Add() 'Open "Empty" new workbook
.Range("A1:M10").Select
With .Selection​

.Font.Name = "Arial"
.Font.Size = "10"​

End With
.DisplayAlerts = vbFalse​

End With​

'Add something to the worksheet to allow Print Preview to show a Header/Footer

objExcelApp.Range( "A1:A1" ).Value = "Put some text in a cell"​

'Attempt to format a Page Header

Qt = Chr( 34 )
MyString = Qt & "Test Header" & Qt
' The first parameter of PAGE.SETUP is CenterHeader,
' so in this example on the single parameter is being passed.
abc = objExcelApp.ExecuteExcel4Macro("PAGE.SETUP(" & MyString & ")")​

'Show excel

objExcelApp.Visable = vbTrue
objExcelApp.ScreenUpdating = vbTrue​

'Destroy object

Set objExcelApp = Nothing​

 

[John_w]

This is VBA, and also works when ported to VBScript.

Sub Excel4_Page_Setup()

    Dim head As String, result As Variant
    Dim Q As String, QQ As String
    
    'Format page header using ExecuteExcel4Macro PAGE.SETUP
    
    Q = Chr(34)
    QQ = Q & Q
    
    '&C = Centred heading; &16 = font size 16; &""Times New Roman,Bold Italic"" = font name and style
    head = Q & "&C&16&" & QQ & "Times New Roman,Bold Italic" & QQ & "Test Header 123" & Q
    
    Debug.Print head
    Range("A2").Value = "Excel 4.0 PAGE.SETUP"
    
    result = ExecuteExcel4Macro("PAGE.SETUP(" & head & ")")
    
    ActiveWindow.ActiveSheet.PrintPreview

End Sub

I've used Times New Roman font to easily distinguish the header from the Excel default Arial font.

The only Excel 4.0 Macro documentation I could find is the official MS Help file - Macrofun.exe.

 

[lcmcmillin]

John,

Thank you so much for your response. This was driving me nuts... All I was missing were a couple of quotes???

This is what I was sending: '"&"Times New Roman,Bold Italic"Test Header 123"
This is what I needed: '"&""Times New Roman,Bold Italic""Test Header 123"

I cannot tell you how many permutations I tried, all to no avail. Again thanks.

Regarding teh Macrofun.exe file. I had downloaded it a while back, but I couldn't find anything. I just opened the Macrofun.hlp file again and a search for EXECUTEEXCEL4MACRO returns no results. The same for PAGE.SETUP or at least to macro help. I browsed it a bit but it seems to be a reference for Windows in general. Any suggestions?


Thanks again... Lance

 

[John_w]

The Help for ExecuteExcel4Macro is in the normal Excel VBA help. Just press F1 whilst the cursor is on the keyword.

Macrofun.hlp is the reference for Excel 4.0 macros and describes the arguments for PAGE.SETUP, and other functions.

 

[lcmcmillin]

John,

I figured out the problem... I did not have WinHlp32 installed correctly. Once I downloaded and installed the MSU the helf for Excel Macro Functions displayed properly.

Thanks again.
Lance