Export from Access to Excel - Nicely!


I want to export the output of an Access query to Excel.
I think I know how to do this.

However, I want the Excel sheet nicely formatted , well spaced out, perhaps the first row might have headings in bold etc.

How do I achieve a pretty Excel output from my Access database?
Perhaps someone could point me towards a useful link or two (as I cannot find one)
Patrick O'DeaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
Typically, the strategy in cases like this is:

1) Start the VBA macro in Excel and clean up the template to get rid of any previous data;
2) Open the the source Excel file with the raw data. This can be hard coded into the VBA or you can use the Application.Dialogs(xlDialogOpen).Show function to get the basic Microsoft Open window to allow the user to select the file and open it.
3) Have Excel VBA loop to go through the opened raw data file and copy and paste it into the template in whatever way is needed.
4) Close the source file, and the template with the updated data with is good to go.

Would this work for you?
Hope this makes sense and helps.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
The other option is to create a pre-formated Excel Template.
Then import the access data to the template using the TransferSpreadsheet command.
Something like this:
    DoCmd.TransferSpreadsheet acExport, , "YourTableOrQueryName", "c:\YourFolder\YourExcelTemplateName.xlt"

Patrick O'DeaAuthor Commented:
Thanks Jeff,

Can I clarify something?
If I export to the template then does this mean that I am over-writing the template.
I.e. I will re-use this template and it seems wrong to be changing it??

Would it not be better to use the template but NOT over write it.

(I may be missing something here - i fear!)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

I typically use Walter's method because I don't want to have to maintain control over the templates but if the template is standard and not changing then Jeff's method would be better.   The only time I've ever used a template is with Word and I had to use automation.  I don't think TransferSpreadsheet is smart enough to write to a template. At least I couldn't make it work so you will need to use automation.  Open excel then open the template, copy the rows to the template, then save it as a regular spreadsheet.  Hopefully, you can find a code sample.  I don't have time to make one for you.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
As Pat mentioned, I could never make TransferSpreadsheet work with Templates. I always ended up using Automation, and my template had Named Ranges I could use to insure data went into the right places. With a Template file, I created a new Excel file based on the Template (which means I didn't overwrite the Template), and then opened recordsets to fill in that template.
Jeffrey CoachmanMIS LiasonCommented:
My post was made based on what would happen the last time I tried this. (years ago)
You would open the template and the name would increment each time you opened it

If this is not the case anymore (as Pat and Scott have mentioned), ...then I apologize, and you can continue on with them.

Patrick O'DeaAuthor Commented:
Thanks folks,

I learnt something today.
I managed to format my exported excel sheet ... within my access database.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.