Export to Excel multiple existing sheets

[sunilkeswani]

I need help with exporting data from 2 access tables, into 2 existing
spreadsheets in a single Excel file.

Currently, I am using this code:

DoCmd.TransferSpreadsheet acExport, 8, "Table1", "D:\Test.xls", True


Dim xlObj
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open "D:\Template.xls"
xlObj.activeworkbook.saveas "D:\Date.xls"
xlObj.Workbooks.Open "D:\Test.xls"
xlObj.activesheet.cells.select
xlObj.activesheet.cells.Copy
xlObj.Workbooks("Date.xls").Activate
xlObj.activeworkbook.sheets(1).range("a1").select
xlObj.activesheet.paste
xlObj.activesheet.Visible = False
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.Quit
Set xlObj = Nothing
MsgBox "Report exported successfully"
Dim stAppName As String

stAppName = "Excel.exe D:\date.xls"
Call Shell(stAppName, 1)

End Sub
This works fine, but just with one table. When I try to export another
table with the same code to
xlObj.activeworkbook.sheets(2).range("a1").select, then I get an error
about subcscipt out of range...
I am wondering if I should create a temp.xls file on the second table
instead, and have an access macro do the copying between the 2
workbooks. I am still unable to get this for the past 3 days.

Please help !
Cheers!
Sunny

[Randy Harris]

<su**********@gmail.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com...

I need help with exporting data from 2 access tables, into 2 existing
spreadsheets in a single Excel file.

Currently, I am using this code:

DoCmd.TransferSpreadsheet acExport, 8, "Table1", "D:\Test.xls", True


Dim xlObj
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open "D:\Template.xls"
xlObj.activeworkbook.saveas "D:\Date.xls"
xlObj.Workbooks.Open "D:\Test.xls"
xlObj.activesheet.cells.select
xlObj.activesheet.cells.Copy
xlObj.Workbooks("Date.xls").Activate
xlObj.activeworkbook.sheets(1).range("a1").select
xlObj.activesheet.paste
xlObj.activesheet.Visible = False
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.Quit
Set xlObj = Nothing
MsgBox "Report exported successfully"
Dim stAppName As String

stAppName = "Excel.exe D:\date.xls"
Call Shell(stAppName, 1)

End Sub
This works fine, but just with one table. When I try to export another
table with the same code to
xlObj.activeworkbook.sheets(2).range("a1").select, then I get an error
about subcscipt out of range...
I am wondering if I should create a temp.xls file on the second table
instead, and have an access macro do the copying between the 2
workbooks. I am still unable to get this for the past 3 days.

Please help !
Cheers!
Sunny

This is, no doubt, another one of those cases with lots of different ways to
do it.
I start with a template workbook, similar to the way you are doing it. I do
the TransferSpreadsheet directly into the copy of the template.

DoCmd.TransferSpreadsheet acExport, 8, "Table1", "D:\date.xls", True
DoCmd.TransferSpreadsheet acExport, 8, "Table2", "D:\date.xls", True

You'll end up with 2 new worksheets in that workbook, one named Table1, the
other Table2.

Address the individual worksheets with:
Set xlObj = GetObject("D:\date.xls")
With xlObj.Application
.Sheets("Table1").Select
.Range("a1:b2").Select
.Selection.Copy
.Sheets("Destination").Select
.Range("a1").Select
.ActiveSheet.Paste
.Sheets("Table2").Select
.etc, etc
You should get the idea at this point. You might want to delete, move or
rename the new worksheets. Set fonts, colors, column widths and borders. You
can also turn on autofilters and freeze panes.

HTH,
Randy Harris

[sunilkeswani]

Thanks..But the problem is that I have already existing sheets on that
file, which are referenced to the exported sheet with a formula...I
might do better with an excel macro which will copy data from the
exported table data...

Cheers!
Sunny

[Randy Harris]

<su**********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Thanks..But the problem is that I have already existing sheets on that
file, which are referenced to the exported sheet with a formula...I
might do better with an excel macro which will copy data from the
exported table data...

Cheers!
Sunny

To me it seems logical to simply import the data to a new sheet and copy it
over to the existing sheet. But, suit yourself.

[sunilkeswani]

Randy Harris wrote:

<su**********@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

Thanks..But the problem is that I have already existing sheets on that
file, which are referenced to the exported sheet with a formula...I
might do better with an excel macro which will copy data from the
exported table data...

Cheers!
Sunny

To me it seems logical to simply import the data to a new sheet and copy it
over to the existing sheet. But, suit yourself.

Yes. i have written a macro which will import into the required sheet.
I can run the macro through access.

Thanks for the help!

Cheers
Sunny