EXPORT TO THE SAME EXCEL FILE

[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

 

[John Nurick]

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.

 

[Tamer]

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

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

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

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[onedaywhen]

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
;

 

[John Nurick]

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
;