export two queries to two tabs in one excel file

[Geometra76]

I need to export two access queries to one excel file with two tabs

 

[Ken Snell \(MVP\)]

Use TransferSpreadsheet method (macro or VBA code). If you export one query
to a file, it goes on the first spreadsheet. If you then export a second
query to that same file, so long as the query has a different name (the
first 31 characters of the name), that second query will go on a second
sheet in the file.

 

[Geometra76]

I forgot to mention that the two tabs are formatted. I tried the
TransferSpreadsheet ( macro) method,but when I ran the macro the queries are
copied to the next available worksheet in the workbook.

 

[Ken Snell \(MVP\)]

An undocumented feature is that you can use the Range argument of the
TransferSpreadsheet method to specify the worksheet onto which the data are
exported. Try that. A worksheet is defined this way for the syntax:

"WorksheetName!"

or, if the worksheet name has a space character in it:

"'Worksheet Name'!"

 

[Geometra76]

Ken,
I tried your suggestion and it did not work. My worksheets name are
"Provauno" and "Provadue". When I ran the macro, it creates two new tabs
"Provauno1" and Provadue1". Below is the detailed of my TransferSpreadsheet
set up:
Transfer type---------------Export
Spreadsheet Type----------Microsoft Excel8-9
Table Name-----------------Provauno
File Name-------------------G:\Excel_Programs\Book2.xls
Has Field Name-------------None
Range-----------------------Provauno

Transfer type---------------Export
Spreadsheet Type----------Microsoft Excel8-9
Table Name-----------------Provadue
File Name-------------------G:\Excel_Programs\Book2.xls
Has Field Name-------------None
Range-----------------------Provadue
Thank you for your help

 

[Ken Snell \(MVP\)]

Look again at what I typed for the Range argument.

Try this for your Range argument:

Range-----------------------Provadue!

 

[Geometra76]

I tried using Provadue! and I got the following error message 'Provadue$'
already exists.

 

[Ken Snell \(MVP\)]

Intriguing....well, as an undocumented feature, I guess it has some
drawbacks.

I have found that TransferSpreadsheet will export onto an existing
spreadsheet when the spreadsheet's name is the same as the name of the query
being exported (and leaving the Range argument blank). Perhaps try making a
copy of your query and name it Provadue.. then export it to that file.

It's possible that this won't work either because you already have more than
one sheet in the file. Honestly, I've not used this approach for any
application. The "normal" approaches that others have posted involve
exporting to an EXCEL file and then using Automation to add the desired
formatting, or using EXCEL to import the data from ACCESS.
--

Ken Snell
<MS ACCESS MVP>

 

[John Nurick]

Hi Ken,

I have found that TransferSpreadsheet will export onto an existing
spreadsheet when the spreadsheet's name is the same as the name of the query
being exported (and leaving the Range argument blank). Perhaps try making a
copy of your query and name it Provadue.. then export it to that file.

This doesn't work reliably (if at all) for me. I think it may relate to
what if anything is on the existing sheet. Even when I do specify a
sheet name and the data is exported to the existing sheet, the sheet's
formatting is replaced by the default MS Sans Serif 10.

It's possible that this won't work either because you already have more than
one sheet in the file. Honestly, I've not used this approach for any
application. The "normal" approaches that others have posted involve
exporting to an EXCEL file and then using Automation to add the desired
formatting, or using EXCEL to import the data from ACCESS.

.... or using Automation to place the data in cells in a pre-formatted
template.

 

[Ken Snell \(MVP\)]

Hi Ken,



This doesn't work reliably (if at all) for me. I think it may relate to
what if anything is on the existing sheet. Even when I do specify a
sheet name and the data is exported to the existing sheet, the sheet's
formatting is replaced by the default MS Sans Serif 10.

John, your experience sounds about right. I've found this "approach" to be a
bit haphazard as well.

 

[Geometra76]

Hi Ken,

I started everything from the scratch and when I ran the macro it did export
the queries into one excel formatted file with two tabs. I named the
spreadsheet tabs same as the queries and left the range argument blank. I do
not understand why it did not work before since I performed the same steps. I
am happy that is working now.
Is it possible to use the excel file as a template?

Thank you for your help

 

[Ken Snell \(MVP\)]

You'll have to try using the file as a template to see if it might work.
Based on your previous results, it may not. This "feature" can be a bit
unpredictable.
--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I started everything from the scratch and when I ran the macro it did
export
the queries into one excel formatted file with two tabs. I named the
spreadsheet tabs same as the queries and left the range argument blank. I
do
not understand why it did not work before since I performed the same
steps. I
am happy that is working now.
Is it possible to use the excel file as a template?

Thank you for your help

< snipped >