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.TransferS preadsheet acExport, 8, "Table1", "D:\Test.xl s", True


Dim xlObj
Set xlObj = CreateObject("e xcel.applicatio n")
xlObj.Workbooks .Open "D:\Template.xl s"
xlObj.activewor kbook.saveas "D:\Date.xl s"
xlObj.Workbooks .Open "D:\Test.xl s"
xlObj.activeshe et.cells.select
xlObj.activeshe et.cells.Copy
xlObj.Workbooks ("Date.xls").Ac tivate
xlObj.activewor kbook.sheets(1) .range("a1").se lect
xlObj.activeshe et.paste
xlObj.activeshe et.Visible = False
xlObj.activewor kbook.Save
xlObj.activewor kbook.Close
xlObj.activewor kbook.Save
xlObj.activewor kbook.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.activewor kbook.sheets(2) .range("a1").se lect, 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**********@g mail.com> wrote in message
news:11******** *************@g 49g2000cwa.goog legroups.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.TransferS preadsheet acExport, 8, "Table1", "D:\Test.xl s", True


Dim xlObj
Set xlObj = CreateObject("e xcel.applicatio n")
xlObj.Workbooks .Open "D:\Template.xl s"
xlObj.activewor kbook.saveas "D:\Date.xl s"
xlObj.Workbooks .Open "D:\Test.xl s"
xlObj.activeshe et.cells.select
xlObj.activeshe et.cells.Copy
xlObj.Workbooks ("Date.xls").Ac tivate
xlObj.activewor kbook.sheets(1) .range("a1").se lect
xlObj.activeshe et.paste
xlObj.activeshe et.Visible = False
xlObj.activewor kbook.Save
xlObj.activewor kbook.Close
xlObj.activewor kbook.Save
xlObj.activewor kbook.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.activewor kbook.sheets(2) .range("a1").se lect, 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 TransferSpreads heet directly into the copy of the template.

DoCmd.TransferS preadsheet acExport, 8, "Table1", "D:\date.xl s", True
DoCmd.TransferS preadsheet acExport, 8, "Table2", "D:\date.xl s", 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:\d ate.xls")
With xlObj.Applicati on
.Sheets("Table1 ").Select
.Range("a1:b2") .Select
.Selection.Copy
.Sheets("Destin ation").Select
.Range("a1").Se lect
.ActiveSheet.Pa ste
.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**********@g mail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.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**********@g mail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.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