Writing to Excel header w/A2000 VBA

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

 

[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

 

[trevorC via AccessMonster.com]

Do this first,

Dim Excel_Application As Excel.Application
Dim Excel_Workbook As Excel.Workbook
Dim Current_Worksheet As Excel.Worksheet
Set Excel_Workbook = GetObject(gg)
Set Excel_Application = Excel_Workbook.Parent

then you can access the excel object as shown by kc-mass

 

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

 

[trevorC via AccessMonster.com]

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"

Unsure if the ' B ' is text or a variant, if it's text then do this

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

or for a variable use

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

 

[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

 

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

 

[trevorC via AccessMonster.com]

For any task you need to do from access, record a macro in excel for the task
you want then convert it to access vba. usually this just means putting the
reference to the app in front of the code.

 

[trevorC via AccessMonster.com]

Thats your code so i cant help with that, i just cleaned it up.

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

ws.pagesetup.LeftHeader = "&B&15Technical Services, Inc." & Chr(13) &
"&15Advanced Shipping Notice"

[quoted text clipped - 17 lines]

ws.pagesetup.rightheader = B & "14" & strFileName & Chr(13) & B & "3 of 9
Barcode" & strFileName