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

Export to Excel Template gives Error - HELP!

P: n/a
Hi,

I have a command button which would export a table data to an pre-set
excel sheet, and save it with the data with another name...I am facing
difficulty with an error.

This is the code

Private Sub Command0_Click()

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

Dim xlObj
Set xlObj = CreateObject("excel.application")
xlObj.workbooks.Open "D:\Template.xls"
xlObj.activeworkbook.saveas "D:\mynewfile.xls"
xlObj.workbooks.Open "D:\temp1.xls"
xlObj.activeworkbook.cells.select
xlObj.activeworkbook.selection.copy
xlObj.workbooks("mynewfile.xls").Activate
xlObj.activeworkbook.sheets(1).range("a1").select
xlObj.activesheet.paste
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.Quit
Set xlObj = Nothing
End Sub
I get an error on the part "xlObj.activeworkbook.cells.select"...It
says "Run Time error 438 - Object does not support this property or
method"

I need help with this, would appreciate a prompt response

regards
Sunil

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


P: n/a
You've posted this in a newsgroup for support of Microsoft Access, not
Excel.

BTW - Cells is a property of a WorkSheet, not a Workbook.

Randy

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

I have a command button which would export a table data to an pre-set
excel sheet, and save it with the data with another name...I am facing
difficulty with an error.

This is the code

Private Sub Command0_Click()

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

Dim xlObj
Set xlObj = CreateObject("excel.application")
xlObj.workbooks.Open "D:\Template.xls"
xlObj.activeworkbook.saveas "D:\mynewfile.xls"
xlObj.workbooks.Open "D:\temp1.xls"
xlObj.activeworkbook.cells.select
xlObj.activeworkbook.selection.copy
xlObj.workbooks("mynewfile.xls").Activate
xlObj.activeworkbook.sheets(1).range("a1").select
xlObj.activesheet.paste
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.Quit
Set xlObj = Nothing
End Sub
I get an error on the part "xlObj.activeworkbook.cells.select"...It
says "Run Time error 438 - Object does not support this property or
method"

I need help with this, would appreciate a prompt response

regards
Sunil


Nov 13 '05 #2

P: n/a
Sorry for the wrong post. This was an export from MS Access, that's why
I posted it here...I have now resolved the problem with the following
code:

Private Sub Command0_Click()
DoCmd.TransferSpreadsheet acExport, 8, "Table1", "D:\Temp1.xls", True
Dim xlObj
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open "D:\Template.xls"
xlObj.activeworkbook.saveas "D:\mynewfile.xls"
xlObj.Workbooks.Open "D:\temp1.xls"
xlObj.activesheet.cells.select
xlObj.activesheet.cells.Copy
xlObj.Workbooks("mynewfile.xls").Activate
xlObj.activeworkbook.sheets(1).range("a1").select
xlObj.activesheet.paste
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.activeworkbook.Save
xlObj.activeworkbook.Close
xlObj.Quit
Set xlObj = Nothing
End Sub

Thanks for the help with the worksheet part Randy, that was causing the
problem.

Regards
Sunil

Nov 13 '05 #3

P: n/a
Another question to this...How do I insert data into 2 worksheets,
instead of just the first one? i get an error when i do

xlObj.activeworkbook.sheets(2).range("a1").select

There seems to be a problem with pasting it to sheet 2 !

Could you please help

Nov 13 '05 #4

P: n/a

<su**********@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
Another question to this...How do I insert data into 2 worksheets,
instead of just the first one? i get an error when i do

xlObj.activeworkbook.sheets(2).range("a1").select

There seems to be a problem with pasting it to sheet 2 !

Could you please help


See reply in the other thread you started with this question.

Randy

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.