Variable in Excel 4 Macro (Excel 2000)

Topic

New Reply

Page Setup in VBA executes very slowly, so I am accepting Bullen/Bovey/Green suggestion to use the Page.Setup Excel4 macro. My problem is, I can’t see how to use a variable in the Header.
The code looks like this:
ExecuteExcel4Macro “page.setup(“”&LCheck Log for &D &RBatch # “”,,,.25,.25,.5,.25,,true,false,false,2,1,85,,,,,,,,)”
This works, but what I want to do is include a variable after “Batch #”. I can’t seem to get any combination of quotes and ampersands to work. Am I simply SOL?
Thanks

Reply | Quote

Replies

As far as I can tell, you’ve got an extra comma between the header string and the margins. Try something like this:

Dim lngBatchNo As Long
lngBatchNo = 37
ExecuteExcel4Macro "page.setup(""&LCheck Log for &D &RBatch # " & _
lngBatchNo & """,,.25,.25,.5,.25,,true,false,false,2,1,85)"

The variable lngBatchNo is concatenated into the header string.

Reply | Quote

Hans,
Brilliant, as usual. I guess I did not try all the combinations.
Thank you ver much.

Reply | Quote