Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

In Access, I know that there is a "Analyze it with Microsoft Office Excel" option, but i am creating a database and the person requesting this wants a command button on a form to do the same thing that the "Analyze it with Microsoft Office Excel" button does. So my question is can anyone help me write vba code to export a MS Access query into Excel, formatted the exact way the "Analyze it with Microsoft Office Excel" does?

share|improve this question
up vote 0 down vote accepted

this worked on the Northwinds db:

DoCmd.OutputTo acTable, "Customers", "MicrosoftExcelBiff8(*.xls)", "", False, "", 0

http://msdn.microsoft.com/en-us/library/aa141534(v=office.10).aspx

share|improve this answer
    
Thanks, Chris. Had to change False to true to open Excel, but it worked – Edmond Jul 13 '11 at 22:05

If you want a lot more flexibility as to exactly what is being exported where to Excel then see

I've used such code as a starting point to, for example, include some heading and footer lines, starting the export below the above lines, remove columns from the spreadsheet which were placed there from the query, create multiple worksheets, etc, etc.

However if you want just something basic then Chris gave you the answer.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .