Export to Excel multiple existing sheets

[sunilkeswani@gmail.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

[Randy Harris]

Re: Export to Excel multiple existing sheets


<sunilkeswani@g mail.com> wrote in message
news:1131187875 .840633.71650@g 49g2000cwa.goog legroups.com...[color=blue]
> 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
>[/color]

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@gmail.com]

Re: Export to Excel multiple existing sheets

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]

Re: Export to Excel multiple existing sheets


<sunilkeswani@g mail.com> wrote in message
news:1131215867 .030086.230920@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> 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
>[/color]

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@gmail.com]

Re: Export to Excel multiple existing sheets


Randy Harris wrote:
[color=blue]
> <sunilkeswani@g mail.com> wrote in message
> news:1131215867 .030086.230920@ z14g2000cwz.goo glegroups.com.. .[color=green]
> > 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
> >[/color]
>
> 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.[/color]



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