Hello,
I have an Access 2000 database that I need to export certain queries
to Excel 2000. The problem is that I have to take the toolbars away
from the users for security purposes, but still let the users have the
ability to export to Excel using forms and buttons, using vba code I
write. I have attempted using the DoCmd.TransferSpreadsheet option,
but it does not look formatted the way the "Analyze It with MS Excel"
button does.
To compensate, I have recorded a macro in Excel and put some of the
vba code in my Access function after it exports and then opens the
Excel file. The freeze panes code works, but code for setting the
background color of cells or putting a border on them does not.
Is there a better way to export so instead of being so plain looking
with DoCmd.Transferspreadsheet, it will look exactly like the "Analyze
It with MS Excel". Also, the Analyze button spaces the column names
out, where the Docmd. option does not.
I have tried looking at the following page:
http://www.microsoft.com/technet/pro...art3/ch15.mspx
Under the heading "Creating Dynamic Reports with Query Tables in
Excel" but have been unsuccessfull in using the code provided, as it
throws errors about user type errors, and trying to get external data
in Excel doens't work right, as it won't let me add queries or tables
in.
Does anyone know of a better solution for exporting queries to excel
other than the Docmd.transferspreadhseet option, so it will look
exactly like the Analyze it button on the toolbar?
If able to do so in code, I will even use the
docmd.transferspreadsheet option, if I could use code to doctor it up
some so it will do formatting of cells.
