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
4  6099 
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
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
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
<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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Steven Stewart |
last post by:
I have a user who has been using Excel for a while to keep statistics
and print reports. She finds using it cumbersome because of long
formulas and a lot of copying and pasting.
I have designed...
|
by: Robin Cushman |
last post by:
Hi all,
I need some help -- I'm working with an A2K database, using DAO, and
am trying to read records into a Crystal Report and then export it to
a folder on our network as an Excel...
|
by: sunilkeswani |
last post by:
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",...
|
by: sweeney_jones |
last post by:
Hi,
I am really struggling to figure out how to do this.
I have a SQL view that contains 3 columns and upto 4000 rows. This is a
series of question numbers and answers for a questionnaire....
|
by: Prasad Dannani |
last post by:
Hi,
We have to export our data in sql server table to any excel sheet. We have a
template to export, using oledb we are inserting rows to this template and
generating the excel output.
The...
|
by: mina |
last post by:
One of my client want to export data into excel file but he can not
installed office just installed excel and want to export file into
..xls format at that time he got error ....
Retrieving the...
|
by: =?Utf-8?B?bWFuaWthMDI=?= |
last post by:
Hi,
I have a GridView control in page called eventslisting which is inheriting
from a MasterPage. The normal code to export to GridView does not work and
gives me an error - "Control of type...
|
by: RobinAG |
last post by:
Hello,
I'm trying to create a new Excel document from a template I've created (Export.xls). I'm having trouble getting it to work, sometimes it works, sometimes it gives me a run-time error '70',...
|
by: CoolFactor |
last post by:
MY CODE IS NEAR THE BOTTOM
I want to export this Access query into Excel using a command button on an Access form in the following way I describe below.
Below you will find the simple query I am...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
