Exporting query set to Excel - Best Method?

[WildHare]

Hi gang - I just need a quick shove here. I need to export a query result set into an existing Excel workbook, specifically an existing SHEET in that workbook. This will be done on a weekly basis.

I've checked out the TransferSpreadsheet method, but that does not appear to allow me to specify a particular SHEET name in a workbook. Am I correct?

And if so, would I probably need to investigate OLE objects for this kind of thing? I know the Excel object contains the necessary pieces for this, and I have played with this guy before, but that was to create a NEW workbook and shove a chart into it. I thought if I could do it quickly with TransferSpreadsheet I could save a bit of work..(ever the lazy one. They don't pay me by the line)

Any thoughts?

Thx Mucho In Advance

Jim





Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[ETID]

ODBC...

in a blank excel worksheet, from the menu...

Data>get external data>new database query, select your data dase type from the list in the left tab...follow the wizard, or select the use MS query option,...carefully select options you want on each window,...such as "read only" check boxes and what not...
then return data to ms excel.

when the data is returned, you can right click in the data area and set properies such as refresh on open..fill down formulas etc.


now when ever the database is updated the workbook will reflect the changes if you refresh...auto(on open) or manually)

 

[xlbo]

Correct - transfer spreadsheet can't do this for you - incredibly annoying - I mean - how hard would it be for MS to provide that functionality. Anyway, as ETID has mentioned, you can use MSQuery but that means that excel is controlling the extract. If you want to control it from access, you'll need to use ADO and the recordset object (which I'm sure you are familiar with)
If you need any help referring to excel objects etc, please post back and I'm sure I'll be able to help

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[WildHare]

Thanks both of you - I'll check out ETIDs method when I get in to work today. I want to set it up in such a manner that the user opens an Access db, and runs some fairly simple queries that pull data from various tables, and then automate the process of shipping this data to the existing workbook. Then when the workbook is opened, it has a few newly updated sheets that are used as references in other sheets. I need to automate this process as much as possible because the person to whom I have to hand off this task is not so 'technically proficient', as they say, to carry this off by herself.

As Geoff noted, it's a shame that TransferSpreadsheet can't do this right off the bat.

Thanks again.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm