Output to a Sheet (Access 2002)

Topic

New Reply

Hi All!

I was wondering if there was any way to output to a spreadsheet in an Excel workbook?

For example, I’m using this code:

DoCmd.OutputTo acOutputQuery, “qryBanks”, acFormatXLS, “Banks.xls”, False

But what I’d actually like to do is export to one workbook called Institutions.xls and have that query export to sheet Banks, another export to sheet Savings & Loans, etc.

The effect that I’m trying to get is the same as clicking File–>Export and choosing the same excel file name for each query that I’m exporting. When you do it manually, it saves each query in a different sheet with the same of the query as the tab.

TIA!

Reply | Quote

Replies

Cecilia,

Use DoCmd.TransferSpreadsheet instead of DoCmd.OutputTo. This will create tabs named after the table or query.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, “qryBanks”, “Banks.xls”

If you want to export field names to the first row as column headings, add “, True” (without the quotes; the default value is False)

Reply | Quote

Thanks, Hans…

Sometimes I can be so silly.

Interesting thing, though, is that it saves the spreadsheet to the My Documents folder…Which isn’t even my default database location! I kind of expected it to be in the same folder as my database. Kind of makes it difficult to have the file automatically open.

BTW, is it just my imagination, or is Help in XP not nearly as good as it was in 97? I seem to have a much harder time these days looking up things like this.

Thanks again!

Reply | Quote

You should be able to specify a path for the workbook: “serversharefolderBanks.xls” instead of just “Banks.xls”. If you want to use the folder in which the database resides, without specifying it explicitly, you can extract it from CurrentDb.Name.

And yes, the HTML help in Office 2000 and up is a nuisance. It has become much more difficult to find things than in older versions; most of the help is still there, but you need patience and luck to unearth it.

Reply | Quote

Thanks again, Hans,

I was just hoping that with this version of Access that things would have gotten easier. Just a simple thing like finding a path to save a spreadsheet is such a pain!

Even searching for simple code to open an excel spreadsheet is impossible! I can type in Excel and get only four results, none of which has to do with Excel. Excel Automation results in some vague references to access automation. Shell is useless because the server path is way too convoluted, and I can’t find my old familiar shell help, either. I know it’s in there–How do I unlock this completely useless help system??? Does MS expect me to memorize ALL bits of code???

I wish we could go back to 97….

Okay, sorry for complaining….I’ll be nice now 😉

Reply | Quote

Searching the help index often does not turn up what you want. Two other ways to get help in the VB Editor:

1. Type the name of an object, method, property, function or instruction in the Immediate window and press F1.
2. Activate the Object Browser and type a word in the Search box, then press Enter. If you see something you would like to know more about, click on it and press F1.

Also see post 300625

Reply | Quote

Searching the help index often does not turn up what you want. Two other ways to get help in the VB Editor:

1. Type the name of an object, method, property, function or instruction in the Immediate window and press F1.
2. Activate the Object Browser and type a word in the Search box, then press Enter. If you see something you would like to know more about, click on it and press F1.

Also see post 300625

Reply | Quote

Hi again, Hans,

#1–Thanks for the tips on using help.

#2–I actually remembered at the last minute that I’ve done this before, and once I remembered, it was so simple, I can’t believe I missed it!

Unfortunately, I don’t program *all* the time, and in between I kinda forget stuff. Urrrgh, that gets me all stressed out, when I know there’s a simple solution, but I can’t for the life of me find it.

But all’s (mostly) good with the world now, and definitely many thanks to you!!!

Reply | Quote

To clarify for others reading this thread, the Help for Functions and all VBA usage is found in the VBA Help, which isn’t pulled up when you open Help from the Access menu. You need to get into the VBA Editor in order to get that Help – and for whatever reason, all nearly al functions Help got stuck in VBA rather than in Access Help.

Reply | Quote

To clarify for others reading this thread, the Help for Functions and all VBA usage is found in the VBA Help, which isn’t pulled up when you open Help from the Access menu. You need to get into the VBA Editor in order to get that Help – and for whatever reason, all nearly al functions Help got stuck in VBA rather than in Access Help.

Reply | Quote

Hi again, Hans,

#1–Thanks for the tips on using help.

#2–I actually remembered at the last minute that I’ve done this before, and once I remembered, it was so simple, I can’t believe I missed it!

Unfortunately, I don’t program *all* the time, and in between I kinda forget stuff. Urrrgh, that gets me all stressed out, when I know there’s a simple solution, but I can’t for the life of me find it.

But all’s (mostly) good with the world now, and definitely many thanks to you!!!

Reply | Quote

Thanks again, Hans,

I was just hoping that with this version of Access that things would have gotten easier. Just a simple thing like finding a path to save a spreadsheet is such a pain!

Even searching for simple code to open an excel spreadsheet is impossible! I can type in Excel and get only four results, none of which has to do with Excel. Excel Automation results in some vague references to access automation. Shell is useless because the server path is way too convoluted, and I can’t find my old familiar shell help, either. I know it’s in there–How do I unlock this completely useless help system??? Does MS expect me to memorize ALL bits of code???

I wish we could go back to 97….

Okay, sorry for complaining….I’ll be nice now 😉

Reply | Quote

You should be able to specify a path for the workbook: “serversharefolderBanks.xls” instead of just “Banks.xls”. If you want to use the folder in which the database resides, without specifying it explicitly, you can extract it from CurrentDb.Name.

And yes, the HTML help in Office 2000 and up is a nuisance. It has become much more difficult to find things than in older versions; most of the help is still there, but you need patience and luck to unearth it.

Reply | Quote

Thanks, Hans…

Sometimes I can be so silly.

Interesting thing, though, is that it saves the spreadsheet to the My Documents folder…Which isn’t even my default database location! I kind of expected it to be in the same folder as my database. Kind of makes it difficult to have the file automatically open.

BTW, is it just my imagination, or is Help in XP not nearly as good as it was in 97? I seem to have a much harder time these days looking up things like this.

Thanks again!

Reply | Quote