Writing to Excel header w/A2000 VBA

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.
 
K

kc-mass

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
 
P

Pastor Del

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"
 
P

Pastor Del

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
 
P

Pastor Del

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top