By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

Analyze It with MS Excel

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
user_5701 wrote:
Hello,
I have an Access 2000 database that I need to export certain queries


I'd make a custom tool bar. Hold the ctrl key, click on the menu item
you want from the tool bar it's currently on and drag it to your custom
tool bar. Associate the tool bar with the form your queries are being
displayed on (don't just give the users a query, give them a form data
sheet or a form).

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #2

P: n/a
I made the toolbar ok, and have the form load and unload the toolbar
when the form is opened and closed, but when I select an item from my
listbox, and click the Analyze button on the custom toolbar, an excel
window comes up with only two rows (one column) - the name of the
listbox in first row, and the selected value from the listbox in the
2nd row. It doesn't actually show any data from any queries. I think
i'm close, but now what do I have to do to get the queries to export?

Tim Marshall <TI****@antarctic.flowerpots> wrote in message news:<cm**********@coranto.ucs.mun.ca>...
user_5701 wrote:
Hello,
I have an Access 2000 database that I need to export certain queries


I'd make a custom tool bar. Hold the ctrl key, click on the menu item
you want from the tool bar it's currently on and drag it to your custom
tool bar. Associate the tool bar with the form your queries are being
displayed on (don't just give the users a query, give them a form data
sheet or a form).

Nov 13 '05 #3

P: n/a
user_5701 wrote:
I think
i'm close, but now what do I have to do to get the queries to export?


I'm afraid I can't answer you on that one as I don't bother with Excel
integration with Access. I was hoping the ability to get the menu item
you mentioned on a tool bar would solve your issues...

Hopefully someone else can help further here...
--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.