EXPORT TO THE SAME EXCEL FILE

  • Thread starter Thread starter TAMER
  • Start date Start date
T

TAMER

I have 3 Access2K queries that I want to export to a
microsoft Excel File that has 3 worksheets. Each query
results in Access should be appended (No Overriding) to
one of the three worksheets.
In another word, I will have a cmd button on a form, when
I click, I want to be able to run each one of the 3
queries and add the queries results to 3 correspondent
worksheets in an Excel file.
Please help
 
Hi Tamer,

The simplest way to do this is to use File|Get External Data|Link to
create three linked tables connected to the three worksheets, and then
use ordinary append queries to append the data to these tables.
 
Thanks for your reply. Can you send me the code for this?
I know the code to output data to Excel, but I don't know
how to link and append data coming from an append query to
three different worksheets in an Excel file. Can you help
a little with the code.
Thanks in advance

-----Original Message-----
Hi Tamer,

The simplest way to do this is to use File|Get External Data|Link to
create three linked tables connected to the three worksheets, and then
use ordinary append queries to append the data to these tables.
I have 3 Access2K queries that I want to export to a
microsoft Excel File that has 3 worksheets. Each query
results in Access should be appended (No Overriding) to
one of the three worksheets.
In another word, I will have a cmd button on a form, when
I click, I want to be able to run each one of the 3
queries and add the queries results to 3 correspondent
worksheets in an Excel file.
Please help
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
I can't write the code for you because I don't know all the details. But
I'd do it somewhat like this:


Dim strFileSpec as String
Dim dbD as DAO.Database

'link to three worksheets in an Excel file
'adjust file, table and sheet names as needed
strFileSpec = "D:\folder\file.xls"
DoCmd.TransferSpreadsheet acLink, _
acSpreadsheetTypeExcel9, "Table1", _
strFileSpec, True, "Sheet1!"
DoCmd.TransferSpreadsheet acLink, _
acSpreadsheetTypeExcel9, "Table2", _
strFileSpec, True, "Sheet2!"
DoCmd.TransferSpreadsheet acLink, _
acSpreadsheetTypeExcel9, "Table3", _
strFileSpec, True, "Sheet3!"

'Run three append queries
'each of which appends the data you want to
'put in one of the three worksheets
Set dbD = CurrentDB()
dbD.Execute "qryAppendToTable1", dbFailOnError
...
...

'Unlink the three tables
dbD.TableDefs.Delete "Table1"

'Tidy up
Set dbD = Nothing



Thanks for your reply. Can you send me the code for this?
I know the code to output data to Excel, but I don't know
how to link and append data coming from an append query to
three different worksheets in an Excel file. Can you help
a little with the code.
Thanks in advance

-----Original Message-----
Hi Tamer,

The simplest way to do this is to use File|Get External Data|Link to
create three linked tables connected to the three worksheets, and then
use ordinary append queries to append the data to these tables.
I have 3 Access2K queries that I want to export to a
microsoft Excel File that has 3 worksheets. Each query
results in Access should be appended (No Overriding) to
one of the three worksheets.
In another word, I will have a cmd button on a form, when
I click, I want to be able to run each one of the 3
queries and add the queries results to 3 correspondent
worksheets in an Excel file.
Please help
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
I disagree. I think the *simplest* way is to change the queries from
SELECT queries to INSERT INTO..SELECT queries, specifying the Excel
workbook/worksheets in the queries.

For example, if your three queries look like this:

SELECT Col1,Col2
FROM MyTable
;
SELECT Col3,Col4
FROM MyTable
;
SELECT Col5,Col6
FROM MyTable
;

And you want to append to Sheet1, Sheet2 and Sheet3 respectively,
change the queries as follows:

INSERT INTO [Excel 8.0;database=C:\MyWorkbook.xls;].[Sheet1$]
SELECT Col1,Col2
FROM MyTable
;
INSERT INTO [Excel 8.0;database=C:\MyWorkbook.xls;].[Sheet2$]
SELECT Col3,Col4
FROM MyTable
;
INSERT INTO [Excel 8.0;database=C:\MyWorkbook.xls;].[Sheet3$]
SELECT Col5,Col6
FROM MyTable
;
 
Up to a point. Certainly it's neater, but IME it can't be relied on to
work on all Access installations.

I disagree. I think the *simplest* way is to change the queries from
SELECT queries to INSERT INTO..SELECT queries, specifying the Excel
workbook/worksheets in the queries.

For example, if your three queries look like this:

SELECT Col1,Col2
FROM MyTable
;
SELECT Col3,Col4
FROM MyTable
;
SELECT Col5,Col6
FROM MyTable
;

And you want to append to Sheet1, Sheet2 and Sheet3 respectively,
change the queries as follows:

INSERT INTO [Excel 8.0;database=C:\MyWorkbook.xls;].[Sheet1$]
SELECT Col1,Col2
FROM MyTable
;
INSERT INTO [Excel 8.0;database=C:\MyWorkbook.xls;].[Sheet2$]
SELECT Col3,Col4
FROM MyTable
;
INSERT INTO [Excel 8.0;database=C:\MyWorkbook.xls;].[Sheet3$]
SELECT Col5,Col6
FROM MyTable
;
Click to expand...
 
Back
Top