How do you Export Data from Access into Multiple Tabs in Excel from one query

[blackmanofsteel40]

I am using a Macro with the TransferSpreadsheet Action and I am trying
to Export Data from a query to Multiple Worksheets within one workbook
in Excel. The query is grouped by date so when I change the criteria
for the date in the query I get different results i.e. I need a
different worksheet to export to, although the query still has the
same name when I save it with the new date in the criteria. As of
right now, the Macro keeps overwriting the previous data instead of
going to a new worksheet. Any ideas ??

 

[John Nurick]

Hi Blackman,

Can you post the SQL view of the query, and explain the rules for what
records go on what worksheet?

 

[blackmanofsteel40]

Hi Blackman,

Can you post the SQL view of the query, and explain the rules for what
records go on what worksheet?

Here is the SQL:

SELECT [Plant 01 Raw Warehouse Flow Parts].Date, [Plant 01 Raw
Warehouse Flow Parts].Part, [Flow Parts Cost].[Last Cost], [Plant 01
Raw Warehouse Flow Parts].Serv, [Plant 01 Raw Warehouse Flow
Parts].Raw, [Plant 01 Raw Warehouse Flow Parts].[Offsite Serv], [Plant
01 Raw Warehouse Flow Parts].[Offsite Raw], [Serv]+[Raw]+[Offsite Serv]
+[Offsite Raw] AS [Inv Pcs], [Flow Parts Cost].[Last Cost]*[Inv Pcs]
AS [Inv $]
FROM [Plant 01 Raw Warehouse Flow Parts] LEFT JOIN [Flow Parts Cost]
ON [Plant 01 Raw Warehouse Flow Parts].Part = [Flow Parts Cost].Part
GROUP BY [Plant 01 Raw Warehouse Flow Parts].Date, [Plant 01 Raw
Warehouse Flow Parts].Part, [Flow Parts Cost].[Last Cost], [Plant 01
Raw Warehouse Flow Parts].Serv, [Plant 01 Raw Warehouse Flow
Parts].Raw, [Plant 01 Raw Warehouse Flow Parts].[Offsite Serv], [Plant
01 Raw Warehouse Flow Parts].[Offsite Raw]
HAVING ((([Plant 01 Raw Warehouse Flow Parts].Date)=#1/23/2007#));


The only difference between the worksheets is the #1/23/2007# which I
will go in and manually change to #1/30/2007#, and then #2/6/2007# and
so on...........When I change the date in the query I get new results
because my table has for example 50 records for 1/30/2007, 50 records
for 2/6/2007 and so on, yet the query has the same name when I save
it.

Does this help ?? thanks

 

[blackmanofsteel40]

Hi Blackman,

Can you post the SQL view of the query, and explain the rules for what
records go on what worksheet?

On 7 Mar 2007 09:37:25 -0800, (e-mail address removed) wrote:

Please respond in the newsgroup and not by email.

Here is the SQL:

SELECT [Plant 01 Raw Warehouse Flow Parts].Date, [Plant 01 Raw
Warehouse Flow Parts].Part, [Flow Parts Cost].[Last Cost], [Plant 01
Raw Warehouse Flow Parts].Serv, [Plant 01 Raw Warehouse Flow
Parts].Raw, [Plant 01 Raw Warehouse Flow Parts].[Offsite Serv], [Plant
01 Raw Warehouse Flow Parts].[Offsite Raw], [Serv]+[Raw]+[Offsite Serv]
+[Offsite Raw] AS [Inv Pcs], [Flow Parts Cost].[Last Cost]*[Inv Pcs]
AS [Inv $]
FROM [Plant 01 Raw Warehouse Flow Parts] LEFT JOIN [Flow Parts Cost]
ON [Plant 01 Raw Warehouse Flow Parts].Part = [Flow Parts Cost].Part
GROUP BY [Plant 01 Raw Warehouse Flow Parts].Date, [Plant 01 Raw
Warehouse Flow Parts].Part, [Flow Parts Cost].[Last Cost], [Plant 01
Raw Warehouse Flow Parts].Serv, [Plant 01 Raw Warehouse Flow
Parts].Raw, [Plant 01 Raw Warehouse Flow Parts].[Offsite Serv], [Plant
01 Raw Warehouse Flow Parts].[Offsite Raw]
HAVING ((([Plant 01 Raw Warehouse Flow Parts].Date)=#1/23/2007#));

The only difference between the worksheets is the #1/23/2007# which I
will go in and manually change to #1/30/2007#, and then #2/6/2007# and
so on...........When I change the date in the query I get new results
because my table has for example 50 records for 1/30/2007, 50 records
for 2/6/2007 and so on, yet the query has the same name when I save
it.

Does this help ?? thanks- Hide quoted text -

- Show quoted text -

yes, to clarify...........I want to put the new results from the same
query which I went in and changed the date in the criteria onto a new
worksheet. The query will have the same name but different results
after I change it and save it. I need a new worksheet for the
different results, i.e. 52 worksheets for 52 weeks in the year

 

[blackmanofsteel40]

Here is the SQL:

SELECT [Plant 01 Raw Warehouse Flow Parts].Date, [Plant 01 Raw
Warehouse Flow Parts].Part, [Flow Parts Cost].[Last Cost], [Plant 01
Raw Warehouse Flow Parts].Serv, [Plant 01 Raw Warehouse Flow
Parts].Raw, [Plant 01 Raw Warehouse Flow Parts].[Offsite Serv], [Plant
01 Raw Warehouse Flow Parts].[Offsite Raw], [Serv]+[Raw]+[Offsite Serv]
+[Offsite Raw] AS [Inv Pcs], [Flow Parts Cost].[Last Cost]*[Inv Pcs]
AS [Inv $]
FROM [Plant 01 Raw Warehouse Flow Parts] LEFT JOIN [Flow Parts Cost]
ON [Plant 01 Raw Warehouse Flow Parts].Part = [Flow Parts Cost].Part
GROUP BY [Plant 01 Raw Warehouse Flow Parts].Date, [Plant 01 Raw
Warehouse Flow Parts].Part, [Flow Parts Cost].[Last Cost], [Plant 01
Raw Warehouse Flow Parts].Serv, [Plant 01 Raw Warehouse Flow
Parts].Raw, [Plant 01 Raw Warehouse Flow Parts].[Offsite Serv], [Plant
01 Raw Warehouse Flow Parts].[Offsite Raw]
HAVING ((([Plant 01 Raw Warehouse Flow Parts].Date)=#1/23/2007#));

The only difference between the worksheets is the #1/23/2007# which I
will go in and manually change to #1/30/2007#, and then #2/6/2007# and
so on...........When I change the date in the query I get new results
because my table has for example 50 records for 1/30/2007, 50 records
for 2/6/2007 and so on, yet the query has the same name when I save
it.

Does this help ?? thanks- Hide quoted text -

- Show quoted text -

yes, to clarify...........I want to put the new results from the same
query which I went in and changed the date in the criteria onto a new
worksheet. The query will have the same name but different results
after I change it and save it. I need a new worksheet for the
different results, i.e. 52 worksheets for 52 weeks in the year- Hide quoted text -

- Show quoted text -

Yeah sorry, forgot this too........I have 4 Excel files for 4
different plants that each contain the"52 worksheets"

 

[John Nurick]

There are no aggregate functions in your query, so I don't understand
why you're using GROUP BY and HAVING. As far as I know this is a simpler
equivalent:

SELECT
[Plant 01 Raw Warehouse Flow Parts].Date,
[Plant 01 Raw Warehouse Flow Parts].Part,
[Flow Parts Cost].[Last Cost],
[Plant 01 Raw Warehouse Flow Parts].Serv,
[Plant 01 Raw Warehouse Flow Parts].Raw,
[Plant 01 Raw Warehouse Flow Parts].[Offsite Serv],
[Plant 01 Raw Warehouse Flow Parts].[Offsite Raw],
[Serv]+[Raw]+[Offsite Serv]+[Offsite Raw] AS [Inv Pcs],
[Flow Parts Cost].[Last Cost]*[Inv Pcs] AS [Inv $]
FROM [Plant 01 Raw Warehouse Flow Parts] LEFT JOIN [Flow Parts Cost]
ON [Plant 01 Raw Warehouse Flow Parts].Part = [Flow Parts Cost].Part
WHERE [Plant 01 Raw Warehouse Flow Parts].Date=#1/23/2007# ;

Anyway, I think the way to do what you want is to a form to supply the
date and worksheet name. You'll need a form (maybe you're using one
already to display the data); I'll call it "frmX".

Put two textboxes on frmX; call one of them txtExportDate and the other
txtSheetName. Also a commandbutton; let's call this cmdExport.

Turn the query into a parameter query that gets the date the user enters
on the form:

PARAMETERS [Forms]![frmX]![txtExportDate] DateTime;
SELECT
[Plant 01 Raw Warehouse Flow Parts].Date,
[Plant 01 Raw Warehouse Flow Parts].Part,
[Flow Parts Cost].[Last Cost],
[Plant 01 Raw Warehouse Flow Parts].Serv,
[Plant 01 Raw Warehouse Flow Parts].Raw,
[Plant 01 Raw Warehouse Flow Parts].[Offsite Serv],
[Plant 01 Raw Warehouse Flow Parts].[Offsite Raw],
[Serv]+[Raw]+[Offsite Serv]+[Offsite Raw] AS [Inv Pcs],
[Flow Parts Cost].[Last Cost]*[Inv Pcs] AS [Inv $]
FROM [Plant 01 Raw Warehouse Flow Parts] LEFT JOIN [Flow Parts Cost]
ON [Plant 01 Raw Warehouse Flow Parts].Part = [Flow Parts Cost].Part
WHERE [Plant 01 Raw Warehouse Flow Parts].Date=
[Forms]![frmX]![txtExportDate];

Then use something like this in the Click event procedure of cmdExport,
replacing qryXXX with the actual name of the query and using the actual
location of the destination file:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryXXX", "D:\Folder\Filename.xls", True, _
Me.txtSheetName.Value & "$"

(I can never remember whether the last character needs to be a $ or a !)

Then it's just a matter of entering the date and worksheet name on the
form and clicking the button.

The next stage of automation is to generate the worksheet names
automatically.

Hi Blackman,

Can you post the SQL view of the query, and explain the rules for what
records go on what worksheet?

Here is the SQL:

SELECT [Plant 01 Raw Warehouse Flow Parts].Date, [Plant 01 Raw
Warehouse Flow Parts].Part, [Flow Parts Cost].[Last Cost], [Plant 01
Raw Warehouse Flow Parts].Serv, [Plant 01 Raw Warehouse Flow
Parts].Raw, [Plant 01 Raw Warehouse Flow Parts].[Offsite Serv], [Plant
01 Raw Warehouse Flow Parts].[Offsite Raw], [Serv]+[Raw]+[Offsite Serv]
+[Offsite Raw] AS [Inv Pcs], [Flow Parts Cost].[Last Cost]*[Inv Pcs]
AS [Inv $]
FROM [Plant 01 Raw Warehouse Flow Parts] LEFT JOIN [Flow Parts Cost]
ON [Plant 01 Raw Warehouse Flow Parts].Part = [Flow Parts Cost].Part
GROUP BY [Plant 01 Raw Warehouse Flow Parts].Date, [Plant 01 Raw
Warehouse Flow Parts].Part, [Flow Parts Cost].[Last Cost], [Plant 01
Raw Warehouse Flow Parts].Serv, [Plant 01 Raw Warehouse Flow
Parts].Raw, [Plant 01 Raw Warehouse Flow Parts].[Offsite Serv], [Plant
01 Raw Warehouse Flow Parts].[Offsite Raw]
HAVING ((([Plant 01 Raw Warehouse Flow Parts].Date)=#1/23/2007#));


The only difference between the worksheets is the #1/23/2007# which I
will go in and manually change to #1/30/2007#, and then #2/6/2007# and
so on...........When I change the date in the query I get new results
because my table has for example 50 records for 1/30/2007, 50 records
for 2/6/2007 and so on, yet the query has the same name when I save
it.

Does this help ?? thanks

 

[blackmanofsteel40]

There are no aggregate functions in your query, so I don't understand
why you're using GROUP BY and HAVING. As far as I know this is a simpler
equivalent:

SELECT
[Plant 01 Raw Warehouse Flow Parts].Date,
[Plant 01 Raw Warehouse Flow Parts].Part,
[Flow Parts Cost].[Last Cost],
[Plant 01 Raw Warehouse Flow Parts].Serv,
[Plant 01 Raw Warehouse Flow Parts].Raw,
[Plant 01 Raw Warehouse Flow Parts].[Offsite Serv],
[Plant 01 Raw Warehouse Flow Parts].[Offsite Raw],
[Serv]+[Raw]+[Offsite Serv]+[Offsite Raw] AS [Inv Pcs],
[Flow Parts Cost].[Last Cost]*[Inv Pcs] AS [Inv $]
FROM [Plant 01 Raw Warehouse Flow Parts] LEFT JOIN [Flow Parts Cost]
ON [Plant 01 Raw Warehouse Flow Parts].Part = [Flow Parts Cost].Part
WHERE [Plant 01 Raw Warehouse Flow Parts].Date=#1/23/2007# ;

Anyway, I think the way to do what you want is to a form to supply the
date and worksheet name. You'll need a form (maybe you're using one
already to display the data); I'll call it "frmX".

Put two textboxes on frmX; call one of them txtExportDate and the other
txtSheetName. Also a commandbutton; let's call this cmdExport.

Turn the query into a parameter query that gets the date the user enters
on the form:

PARAMETERS [Forms]![frmX]![txtExportDate] DateTime;
SELECT
[Plant 01 Raw Warehouse Flow Parts].Date,
[Plant 01 Raw Warehouse Flow Parts].Part,
[Flow Parts Cost].[Last Cost],
[Plant 01 Raw Warehouse Flow Parts].Serv,
[Plant 01 Raw Warehouse Flow Parts].Raw,
[Plant 01 Raw Warehouse Flow Parts].[Offsite Serv],
[Plant 01 Raw Warehouse Flow Parts].[Offsite Raw],
[Serv]+[Raw]+[Offsite Serv]+[Offsite Raw] AS [Inv Pcs],
[Flow Parts Cost].[Last Cost]*[Inv Pcs] AS [Inv $]
FROM [Plant 01 Raw Warehouse Flow Parts] LEFT JOIN [Flow Parts Cost]
ON [Plant 01 Raw Warehouse Flow Parts].Part = [Flow Parts Cost].Part
WHERE [Plant 01 Raw Warehouse Flow Parts].Date=
[Forms]![frmX]![txtExportDate];

Then use something like this in the Click event procedure of cmdExport,
replacing qryXXX with the actual name of the query and using the actual
location of the destination file:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryXXX", "D:\Folder\Filename.xls", True, _
Me.txtSheetName.Value & "$"

(I can never remember whether the last character needs to be a $ or a !)

Then it's just a matter of entering the date and worksheet name on the
form and clicking the button.

The next stage of automation is to generate the worksheet names
automatically.

Here is the SQL:

SELECT [Plant 01 Raw Warehouse Flow Parts].Date, [Plant 01 Raw
Warehouse Flow Parts].Part, [Flow Parts Cost].[Last Cost], [Plant 01
Raw Warehouse Flow Parts].Serv, [Plant 01 Raw Warehouse Flow
Parts].Raw, [Plant 01 Raw Warehouse Flow Parts].[Offsite Serv], [Plant
01 Raw Warehouse Flow Parts].[Offsite Raw], [Serv]+[Raw]+[Offsite Serv]
+[Offsite Raw] AS [Inv Pcs], [Flow Parts Cost].[Last Cost]*[Inv Pcs]
AS [Inv $]
FROM [Plant 01 Raw Warehouse Flow Parts] LEFT JOIN [Flow Parts Cost]
ON [Plant 01 Raw Warehouse Flow Parts].Part = [Flow Parts Cost].Part
GROUP BY [Plant 01 Raw Warehouse Flow Parts].Date, [Plant 01 Raw
Warehouse Flow Parts].Part, [Flow Parts Cost].[Last Cost], [Plant 01
Raw Warehouse Flow Parts].Serv, [Plant 01 Raw Warehouse Flow
Parts].Raw, [Plant 01 Raw Warehouse Flow Parts].[Offsite Serv], [Plant
01 Raw Warehouse Flow Parts].[Offsite Raw]
HAVING ((([Plant 01 Raw Warehouse Flow Parts].Date)=#1/23/2007#));

The only difference between the worksheets is the #1/23/2007# which I
will go in and manually change to #1/30/2007#, and then #2/6/2007# and
so on...........When I change the date in the query I get new results
because my table has for example 50 records for 1/30/2007, 50 records
for 2/6/2007 and so on, yet the query has the same name when I save
it.

Does this help ?? thanks

--
John Nurick [Microsoft Access MVP]

Please respond in the newsgroup and not by email.- Hide quoted text -

- Show quoted text -

Adding a form only complicates things, I simply want to add a tab to
the excel worksheet that is essentially the same query each week, yet
I manually go in and change the date in the query thereby getting new
results. This is what I'm trying to do, from the TransferSpreadSheet
Action help menu in a macro: (THE LAST SENTENCE IS WHAT I WANT TO
DO !!!!!!!!)

Access creates a new spreadsheet when you export data from Access. If
the file name is the same as the name of an existing spreadsheet,
Access replaces the existing spreadsheet, unless you're exporting to
an Excel version 5.0 or later workbook. In that case, Access copies
the exported data to the next available new worksheet in the workbook.

 

[John Nurick]

Adding a form only complicates things, I simply want to add a tab to
the excel worksheet that is essentially the same query each week, yet
I manually go in and change the date in the query thereby getting new
results. This is what I'm trying to do, from the TransferSpreadSheet
Action help menu in a macro: (THE LAST SENTENCE IS WHAT I WANT TO
DO !!!!!!!!)

Access creates a new spreadsheet when you export data from Access. If
the file name is the same as the name of an existing spreadsheet,
Access replaces the existing spreadsheet, unless you're exporting to
an Excel version 5.0 or later workbook. In that case, Access copies
the exported data to the next available new worksheet in the workbook.

What's not mentioned in that passage is that when you use
TransferSpreadsheet to export a query without specifying a worksheet
name in the Range argument, and the workbook already contains a
worksheet with the same name as the query, the new data will replace the
data on the existing sheet. If you want to export a query with the same
name multiple times so it creates multiple worksheets in the same
workbook, you have to specify the worksheet name each time you call
TranferSpreadsheet (or change the name of the query each time).

 

[blackmanofsteel40]

What's not mentioned in that passage is that when you use
TransferSpreadsheet to export a query without specifying a worksheet
name in the Range argument, and the workbook already contains a
worksheet with the same name as the query, the new data will replace the
data on the existing sheet. If you want to export a query with the same
name multiple times so it creates multiple worksheets in the same
workbook, you have to specify the worksheet name each time you call
TranferSpreadsheet (or change the name of the query each time).

Good stuff, Somebody figured it out in my office around the same time.
thanks a mil though !!!!!