MS Access data conversion - Urgent: Exporting Access into Excel

Author

Urgent: Exporting Access into Excel

mattylance@hotmail.com

2005-12-08, 8:26 pm

Hello, I need help exporting or linking MS Access queries into Excel
spreadsheets. This will be a daily task. Is there any way of doing
this? I tried using the transferspreadsheet macro but couldn't figure
it out. Any adivce is appreciated!

Thanks
Matt

Paul C

2005-12-09, 9:24 am

Hi Matt,

Hopefully you've worked it out now, as this was urgent but in case not, try
this:-

DoCmd.TransferSpreadsheet acExport, 8, "XXX", "YYY", True

where XXX is your table name, and YYY is the name (including the full path)
of the Exel file you want it saved to. The only problem I have found is that
sometimes the coversion process changes numbers in text fields (like phone
numbwers) to actual numbers in Excel, so 01293 765123 becomes 293765123.

The True at the end means that the field names go as well. If you don't need
them, then substitute false.

mattylance@hotmail.com wrote:
>Hello, I need help exporting or linking MS Access queries into Excel
>spreadsheets. This will be a daily task. Is there any way of doing
>this? I tried using the transferspreadsheet macro but couldn't figure
>it out. Any adivce is appreciated!
>
>Thanks
>Matt

Paul C

2005-12-09, 9:24 am

Hi again,

Sorry - just realised that the date is displayed in US format, and this was a
recent post. I thought it was posted in September!

Doh!

Paul C wrote:[color=darkred
]
>Hi Matt,
>
>Hopefully you've worked it out now, as this was urgent but in case not, try
>this:-
>
> DoCmd.TransferSpreadsheet acExport, 8, "XXX", "YYY", True
>
>where XXX is your table name, and YYY is the name (including the full path)
>of the Exel file you want it saved to. The only problem I have found is that
>sometimes the coversion process changes numbers in text fields (like phone
>numbwers) to actual numbers in Excel, so 01293 765123 becomes 293765123.
>
>The True at the end means that the field names go as well. If you don't need
>them, then substitute false.
>
>[quoted text clipped - 3 lines]

kev100 via AccessMonster.com

2005-12-11, 3:24 am

mattylance@hotmail.com wrote:
>Hello, I need help exporting or linking MS Access queries into Excel
>spreadsheets. This will be a daily task. Is there any way of doing
>this? I tried using the transferspreadsheet macro but couldn't figure
>it out. Any adivce is appreciated!
>
>Thanks
>Matt

WOW....what a coincidence....

If I understand your question correctly, for the first time ever, I had a
need to do the SAME THING just a few hours ago

Here's the "retail" method I used.

I found that simply creating a Macro in the Access project that contains the
query works just fine.

ALSO.......a tip to make it user friendly:

After you create the macro, you can create a Shortcut to that macro on the
WIndows desktop (or in any folder you wish). The user will just have to
click that one item and after a bit of a pause, the Excel spreadsheet file of
that query will pop open.

Look through those query commands in the drop-down menus. You can even
specifiy "Minimize" and "Quit" before and after the query export so that the
User will not even see that Access is temporarily running.

This was done in with 3 lines...

1. "Minimize"
2. "Output To" (the options in this command is where you specific which query
to use, to output to xls, and the name of the output file. If the name
option is left blank, it will prompt the user for it.
3. "Quit"

After testing it etc...just drag it to the desktop and a shortcut will be
made. That can be renamed / moved anywhere else on that PC.

It's pretty handy.

HTH

--
Message posted via http://www.webservertalk.com

Sponsored Links