Excel - Mehod Paste of object worksheet failed

Question

0.00/5 (No votes)

See more:

Working on some code in MS Access 2003 vba. Automating creation of Excel file. This worked last week. Don't think I changed anything. But now I'm getting the error in the subject. The reason for the TransferSpreadsheet and then the copy/paste is that it's much faster than copying cell by cell into a new sheet. I usually use a template (xlt) file for this but this time decided to create the end workbook from scratch to avoid the need for an xlt file.

Here's my code. The error occurs on the last line shown below (I saw some suggestions regarding this error to use PasteSpecial but that didn't help).

'*****************************************************************************************
'do fast dirty copy to a grungy spreadsheet
'*****************************************************************************************

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "dbo." & sExportTempTableName, Replace(strSaveToPathAndFileName, ".xls", "_TEMP.xls"), True

'*****************************************************************************************
'Open up the grungy one so we can transfer the data over to the nice one
'*****************************************************************************************

Dim xlGrungy As New Excel.Application
Dim xlWkBkGrungy As New Excel.Workbook
Dim xlWkShtGrungy As New Excel.Worksheet
Set xlGrungy = CreateObject("Excel.Application")

xlGrungy.Workbooks.Open Replace(strSaveToPathAndFileName, ".xls", "_TEMP.xls")
Set xlWkBkGrungy = xlGrungy.ActiveWorkbook
Set xlWkShtGrungy = xlWkBkGrungy.Worksheets(1)

xlWkShtGrungy.Range("A2:AB" & Trim(str(NumFinalRows + 10))).Select 'skip the header row
xlGrungy.Selection.Copy

'*****************************************************************************************
'create fancy spreadsheet & Paste the cells from the grungy one into the nice one and format it
'*****************************************************************************************

Dim xlFinal As Excel.Application
Set xlFinal = CreateObject("Excel.Application")
Dim xlWkBkFinal As Excel.Workbook
Set xlWkBkFinal = xlFinal.Workbooks.Add

Dim xlWkShtFinal As Excel.Worksheet
Set xlWkShtFinal = xlWkBkFinal.Worksheets(1)

With xlWkShtFinal

    .Range("A2:A2").Select
    .Paste

Posted 20-Sep-11 5:41am

avianrand

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Answer 1 · 2011-09-20T06:09:00

Solution 1

If you create the end workbook from scratch you need to add the worksheets yourself. When creating a new workbook by hand using excel as desktop application there are 3 sheets created by default. Those aren't created when you do that from code.

Try change this:

Set xlWkShtFinal = xlWkBkFinal.Worksheets(1)

to this:

Set xlWkShtFinal = xlWkBkFinal.Worksheets.Add

Good luck!

Posted 20-Sep-11 6:09am

E.F. Nijboer

Comments

avianrand 21-Sep-11 9:59am

Same error. I did what you have and also tried it this way (there is "Add" code above the xlWkShtFinal declaration):

Collapse | Copy Code

Dim xlFinal As Excel.Application
Set xlFinal = CreateObject("Excel.Application")
Dim xlWkBkFinal As Excel.Workbook
Set xlWkBkFinal = xlFinal.Workbooks.Add
xlWkBkFinal.Worksheets.Add

Dim xlWkShtFinal As Excel.Worksheet
Set xlWkShtFinal = xlWkBkFinal.Worksheets(1)

With xlWkShtFinal

.Range("A2:A2").Select
.Paste

The odd thing is that my original code worked last week on my main computer. I'm running from my laptop right now as my desktop is down for the moment. I thoroughly tested the code before sending it to my client. I know it worked fine. So something else is going on here. No missing references either if anyone wonders about that one. Same version of Office on both of my machines as well.

I thought I found an answer here: http://brainof-dave.blogspot.com/2006/11/paste-method-of-worksheet-class-failed.html[^] but it didn't fix the problem.

I should also point out that when I comment out the "paste" line, all the rest of the code runs fine. The spreadsheet is created and saved with all the formatting and column headings I'm adding via code.

E.F. Nijboer 21-Sep-11 16:45pm

Looks like it only goes wrong on the pc of the client, correct? You might want to make the workbook invisible right after creation en set visible to true if you are done. If it is visible and the user clicks anywhere in the sheet the connection with your code is lost because the user takes over and the code will give an error. Could that be the problem?

avianrand 21-Sep-11 17:22pm

It's always invisible. I've got the visibility set to false the entire time. After the file is saved and closed it asks if the user wants to view it and then opens it up if he/she does. Most of the time they don't want to view it. They just grab it and send it off to their client. And to answer your question, no, it goes wrong on my laptop also. I created all this code on my main dev desktop pc last week (winxp, office 2003) and it ran fine. But on my laptop (same os and office as my other) it doesn't work as well as not working on the client machine that it's been tested on. Ugh!

ONe other odd thing is that when I stepped through the code, i stopped it right after the "copy" command and then did a manual paste into an open spreadsheet and it worked fine. It just won't paste via code. Not sure if that helps anyone figure this out or not.

E.F. Nijboer 22-Sep-11 6:03am

Found a nice website with some good tips and also some advice that it is best not to use copy paste directly but use the copy of the range object that takes the destination as parameter. I remember also using this and never had any trouble with it so I think that might be the solution to this problem. You can find it halfway the page at the text "Avoid the use of Copy and Paste whenever Possible".

http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

avianrand 6-Oct-11 9:44am

Thanks E.F. The solution of not using the clipboard for copy/paste seems to have done the trick. Much appreciated.

E.F. Nijboer 6-Oct-11 12:21pm

Very nice! :-)
Maybe you could also set the answer to solved for others that are looking for a solution for the same problem.

avianrand 6-Oct-11 12:51pm

I wanted to do that but all these comments, including your solution are under "solution 1" which is not the solution. I don't want to mark that as accepted because it's wrong. Add your solution, the one that worked, as a new solution and then I'll mark that as correct.