By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,681 Members | 1,864 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,681 IT Pros & Developers. It's quick & easy.

Export to Excel multiple existing sheets

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a

<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

Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a

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

Nov 13 '05 #4

P: n/a

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

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.