Writing to Excel header w/A2000 VBA

  • Thread starter Thread starter Pastor Del
  • Start date Start date
P

Pastor Del

I need to transfer data from an Access table to an Excel spreadsheet for a
customer. This was easy until the customer asked me to put the first data in
A1 cell and put some text and barcode above it. That forces me to write to
the spreadsheet header and I'm not sure how to do that. Can someone point me
to a Help topic for this or give me an example to work with. Thanks.
 
Look here:

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "This is it"
.RightHeader = ""
.HeaderMargin = Application.InchesToPoints(0.5)
End With

Above copied from recorded macro. Easy.

Regards

Kevin
 
This works with text but I need to use a variable. Here is the code: (The
left & center headers are working but there is a problem with the right
header)

ws.pagesetup.LeftHeader = "&B&15Technical Services, Inc." & Chr(13) &
"&15Advanced Shipping Notice"
ws.pagesetup.centerheader = ""
ws.pagesetup.rightheader = "&B&14" & strFileName & Chr(13) & "&B&""3 of 9
Barcode""strFileName"
 
That works, thanks. But I don't understand why the variable strFileName is
treated differently. The first time it occurs in the code it is enclosed
with ampersands but the second time it is enclosed with quotation marks
 
One other thing. How can I use VBA to change the header height? I found the
code yesterday when I was researching for something else but can't find it
again.
 
Back
Top