Export to Excel with Header and Footer Info

[blue3123]

Greetings,

First, what I know about Access is minimal. The Access expert in our
office retired and somehow I inherited his job. I've got training
coming up, but not till next year.

Anyway, he created an Access database that exports the data into
multiple Excel Files, using the Transferspreadsheet option. This is
done by a group that does not have any programming skill so it has to
be automated for them. Now they want to be able to put some
information in the header/footer of each Excel File. I can't find an
option in the Transferspreadsheet, I even looked at VBA briefly and
couldn't see anything that resembled what we want to do.

I tried creating an Excel template to start all new workbooks with, in
the XLStart folder, but that doesn't seem to work either. The only
thing put into the header/footer seems to be the sheet name and the
page number, and I have no idea how that gets in there.

So the question is, can Access be set up to include header and footer
information when it creates the workbook? If so, is via VBA? If I
know it can be done and I have a place to start, I can usually muddle
my way through things, but I'm at a loss as to how to even start this.
Searches here and other places have not come up with anything, but I
could not be searching on the right information.

Thanks,
Christine

 

[John Nurick]

Hi Christine,

Can be done. Requires VBA. The page header and/or footer you are now
seeing in Excel are default settings; there doesn't seem to be any way
of using a custom Excel template when exporting from Access.

One way to go is

1) export data using TransferSpreadsheet as now.

2) write VBA that launches the newly-created workbook and manipulates
the header and footer. Here's some elementary code which should get you
started automating Excel http://www.mvps.org/access/modules/mdl0006.htm

For actually sorting out the header and footer, start by recording a
macro in Excel, then paste the resulting code into your Access VBA
procedure and adjust it as required.