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

  • Thread starter blackmanofsteel40
  • Start date
B

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 ??
 
J

John Nurick

Hi Blackman,

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

blackmanofsteel40

Hi Blackman,

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

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
 
B

blackmanofsteel40

Hi Blackman,
Click to expand...
Can you post the SQL view of the query, and explain the rules for what
records go on what worksheet?
Click to expand...
On 7 Mar 2007 09:37:25 -0800, (e-mail address removed) wrote:
Click to expand...
Please respond in the newsgroup and not by email.
Click to expand...

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 -
Click to expand...

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
 
B

blackmanofsteel40

Here is the SQL:
Click to expand...
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#));
Click to expand...
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.
Click to expand...
Does this help ?? thanks- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

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 -
Click to expand...

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

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?
Click to expand...

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
Click to expand...
 
B

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:
Click to expand...
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#));
Click to expand...
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.
Click to expand...
Does this help ?? thanks
Click to expand...

--
John Nurick [Microsoft Access MVP]

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

- Show quoted text -
Click to expand...

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.
 
J

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.
Click to expand...

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).
 
B

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).
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Exporting Data to Multiple Tabs in Excel 1
Export to excel file in Access 1
Exporting from Access to Excel keeping EXCEL formatting 2
Transfer Spreadsheet into Excel 5
Export Data from Report or Query to Excel 1
Exporting multiple queries into Excel workbook tabs 10
Exporting separate query results to same workbook diff worksheets 2
Export query to Excel use data from the query as part of file nam 5

Top