Excel automation pastespecial getting OLE exception

[BugZap13]

I am trying to copy cells from one workbook to a second workbook. At the line with the "PasteSpecial" I am getting:
"OLE Dispatch exception code 0 from Microsoft Excel: Unable to get the PasteSpecial property of the range class."
I have reviewed various threads specifically this one (1093592) which did a select then the copy. Below is a basic program I put together to test this which illustrated the problem. If it matters, I am using Excel 2016. TIA for any help.

#DEFINE xlPasteAll -4101
#DEFINE xlNone -4142
PRIVATE poExcel,poModel,poWorkBook,pnRow
poExcel = CREATEOBJECT("Excel.Application")
poExcel.Visible = .T.
poModel = .NULL.
poWorkBook = .NULL.
*
lcModel = 'd:\model.xlsx'
IF FILE(lcModel)
   poModel = poExcel.Workbooks.Open(lcModel)
ELSE
   poModel= poExcel.Workbooks.Add()
   poModel.SaveAs(lcModel)
ENDIF   
*
lcGMS = "d:\GMS-" + DTOS(DATE()) + '.xlsx'
IF FILE(lcGMS)
   ERASE (lcGMS)
ENDIF
poWorkBook=poExcel.Workbooks.Add()
poWorkBook.SaveAs(lcGMS)
*
poModel.Activesheet.Range("A1:BZ1").Copy
poWorkBook.Activesheet.Range("A10:BZ10").PasteSpecial(xlPasteAll,xlNone,.F.,.F.)
*
poExcel.Quit

 

[Chriss Miller]

You first Copy the Source Range, then PasteSpecial.
You have both Workbooks in the same Excel Window through the automation.

As those two boxes are checked, it should work. Unless the opening of the d:\model.xlsx fails, becuase it's already open in another Excel process you may have used before. poExcel.Quit might not work, as there are changes to save and then doing this for the second time you'd fail, likely. As you make Excel visible you will see what happens, so I guess that's not the problem or you'd already know and have an error message from open.

If d:\model.xlsx doesn't exist you create a new empty one, and copy a range from there. Why? You wouldn't copy anything, as that's a fresh now, empty workbook. PasteSpecial might fail on that, perhaps.

If that's also not the reason, try to paste to Range("A10") instead of Range("A10:BZ10").

 

[BugZap13]

Chriss you are correct that the model.xlsx is blank in my example code (I cannot attach an .xlsx file). The real spreadsheet has data in it that I want to copy to the second spreadsheet. Because I made Excel visible, I see that the range in the model gets selected and copied (dotted line around the cells). Also since Excel is visible, I should see any errors such as locks, but I get no errors thrown for user intervention.

Changing the paste range to "A10" has no effect. I have also tried instantiating two excel objects, one for the "poModel " spreadsheet and the other for "poWorkBook" spreadsheet and get the same error. Syntactically I think I have this correct. I have seen other solutions you select the ranges before the copy and paste but have not had success with that either. Something with PasteSpecial is causing the problem. I finally got it to work substituting the following code for the copy and paste lines.

poModel.Activesheet.Range("A1").EntireRow.Copy
poWorkBook.Activesheet.Range("A10").Select
poWorkBook.Activesheet.Paste

 

[Chriss Miller]

Glad you got it working. I wonder if it's just the move to Paste instead of PasteSpecial that made it work. PasteSpecial gives you options like only pasting values, only pasting formats, only pasting formulas or some combination of these.

I have also tried instantiating two excel objects

That definately doesn't work. No, your code was already alright in that aspect of only using one Excel process. The way PasteSpecial works only works within the same Excel, it doesn't only use the clipboard as transfer mechanism. I think it would also depend on source and target Workbook to be the same Excel version and that's not only xls vs xlsx, that means exactly the same Excel version.