Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add method of "Sheets" class fails
Hi all,
I am trying to insert a worksheet from one excel workbook to another excel workbook. The source excel worksheet is of the size 102 MB. So opening the worksheets in the destination excel workbook and then copying is taking too much time. So I tried saving the source worksheet as an excel template (.xlt file) and then tried adding this template to the destination workbook with the following code : objExcel = New Excel.Application objBooks = objExcel.Workbooks ... objBooks.Open("D:\destExcel.xls") objBooks.Item(1).Sheets.Add( "D:\sourceExcel.xlt") .... The Sheets.Add method fails in the above piece of code. Does anybody know the reason ? I would also like to know if there is an efficient method of copying a large sized excel worksheet from one workbook to another without actually having to open the source worksheet. Thanks in anticipation, CodeKracker. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add method of "Sheets" class fails
I think you are mixing up the Workbooks.Add and Worksheets.Add methods.
Try opening the template file, and copying across that sheet, then close it. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "CodeKracker" wrote in message ... Hi all, I am trying to insert a worksheet from one excel workbook to another excel workbook. The source excel worksheet is of the size 102 MB. So opening the worksheets in the destination excel workbook and then copying is taking too much time. So I tried saving the source worksheet as an excel template (.xlt file) and then tried adding this template to the destination workbook with the following code : objExcel = New Excel.Application objBooks = objExcel.Workbooks .. objBooks.Open("D:\destExcel.xls") objBooks.Item(1).Sheets.Add( "D:\sourceExcel.xlt") ... The Sheets.Add method fails in the above piece of code. Does anybody know the reason ? I would also like to know if there is an efficient method of copying a large sized excel worksheet from one workbook to another without actually having to open the source worksheet. Thanks in anticipation, CodeKracker. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add method of "Sheets" class fails
Not very good at Excel, but know Microsoft and their operating system
extremely well. Excel can really take up a lot of system resources. In your control panel go to System Resources and increase your virtual memory by no less than 33% of what it currently is. This will definately increase your overall performance and free up resources that Excel demands! Sorry I cannot help on the sheets problem, my excel knowledge is very limited! Good Luck Len "CodeKracker" wrote: Hi all, I am trying to insert a worksheet from one excel workbook to another excel workbook. The source excel worksheet is of the size 102 MB. So opening the worksheets in the destination excel workbook and then copying is taking too much time. So I tried saving the source worksheet as an excel template (.xlt file) and then tried adding this template to the destination workbook with the following code : objExcel = New Excel.Application objBooks = objExcel.Workbooks .. objBooks.Open("D:\destExcel.xls") objBooks.Item(1).Sheets.Add( "D:\sourceExcel.xlt") ... The Sheets.Add method fails in the above piece of code. Does anybody know the reason ? I would also like to know if there is an efficient method of copying a large sized excel worksheet from one workbook to another without actually having to open the source worksheet. Thanks in anticipation, CodeKracker. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Add method of "Sheets" class fails
If you're running this from excel, why create a new instance of excel?
Couldn't you just do: Dim objBook As Workbook Set objBook = Workbooks.Open(Filename:="D:\destExcel.xls") objBook.Sheets.Add Type:="D:\sourceexcel.xlt" And if you really, really need two instances of excel, I'd use another object variable that held the workbook that I opened. Then work on that object. CodeKracker wrote: Hi all, I am trying to insert a worksheet from one excel workbook to another excel workbook. The source excel worksheet is of the size 102 MB. So opening the worksheets in the destination excel workbook and then copying is taking too much time. So I tried saving the source worksheet as an excel template (.xlt file) and then tried adding this template to the destination workbook with the following code : objExcel = New Excel.Application objBooks = objExcel.Workbooks .. objBooks.Open("D:\destExcel.xls") objBooks.Item(1).Sheets.Add( "D:\sourceExcel.xlt") ... The Sheets.Add method fails in the above piece of code. Does anybody know the reason ? I would also like to know if there is an efficient method of copying a large sized excel worksheet from one workbook to another without actually having to open the source worksheet. Thanks in anticipation, CodeKracker. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Select method of Range class failed" Error | Excel Discussion (Misc queries) | |||
XL2003 "paste method of worksheet class failed" | Excel Programming | |||
Error Message "Select method of worksheet class failed" | Excel Programming | |||
VBA error 1004 "Select method of Range class failed" | Excel Programming | |||
Error 1004, "select method of range class failed" | Excel Programming |