COM Excel Paste Special Topic is solved

[magicinmath]

I am doing something wrong with the syntax for the paste special and it's not working.

"Unable to get paste special property of the range class." is the error I get.

What am I missing?

Code: Select all

Data := Clipboard

Xl := ComObjCreate("Excel.Application") 
Xl := ComObjActive("Excel.Application")

filepath = %A_WorkingDir%\new.xlsx
Xl.Workbooks.Open(filepath)
Xl.Visible := True
Clipboard := Data

; tried these ;

XL.Selection.PasteSpecial(-4163)
xl.Range("a1:D10").PasteSpecial(-4163)  
XL.Selection.Range("a1:D10").PasteSpecial(-4163) 
XL.Range("a1:D10").Selection.PasteSpecial(-4163) 
XL.Selection.PasteSpecial(-4163).Range("a1:D10")
XL.Selection.Range("a1:D10") := XL.Selection.PasteSpecial(-4163)

Thank you.

[magicinmath]

Tried this and I don't get the error from COM, but it doesn't paste.

Code: Select all

Data := Clipboard

Xl := ComObjCreate("Excel.Application") 
Xl := ComObjActive("Excel.Application")

filepath = %A_WorkingDir%\new.xlsx
Xl.Workbooks.Open(filepath)
Xl.Visible := True
Xl.Sheets("Sheet1").Select   ; using this line or not makes no difference in terms of whether it pastes, thought it might.
Clipboard := Data

ActiveSheet.Range("A:D").PasteSpecial(-4163)  

[Nightwolf85]

What if you use:
Xl.ActiveSheet.Range("A:D").PasteSpecial(-4163)

I've only used PasteSpecial once and it was to transpose, however I did use the worksheet range object to call it.

that example code I posted for someone else was this:

Code: Select all

F10::
oExcel := ComObjActive("Excel.Application")
WorkSheet := oExcel.ActiveWorkbook.ActiveSheet
StartCell := StrReplace(oExcel.ActiveCell.Address,"$")
ColLetter := RegExReplace(StartCell,"[0-9]+$")
RowNumber := RegExReplace(StartCell,"^[A-Z]+")
Transform, tmp, Asc, %ColLetter%
Transform, NextColLetter, Chr, % tmp + 1
oExcel.Visible := 0
Loop 5
{
	RangeStart := ((A_Index * 6) - 6) + RowNumber
	RangeEnd := RangeStart + 4
	useRange := ColLetter . RangeStart . ":" . ColLetter . RangeEnd
	WorkSheet.Range(useRange).Copy()
	WorkSheet.Range(NextColLetter . RangeStart).PasteSpecial(,,,ComObj(0xB,-1))  ; This is the PasteSpecial Line, where the only parameter I used was COM object true for transpose.
}
WorkSheet.Range(StartCell).Select
oExcel.CutCopyMode := False
oExcel.Visible := 1
oExcel := ""
Return

[magicinmath]

What if you use:
Xl.ActiveSheet.Range("A:D").PasteSpecial(-4163)

Using Xl I get the same error as mentioned in the first post.

I found this forum post for VB:
http://www.vbaexpress.com/forum/showthr ... ial-method

States that one should not reference the workbook.

When I don't reference the workbook, I do not get the COM error, but it also does not paste...

I'm trying to use your code but it's not working

[Nightwolf85]

Well the code I pasted wasn't for your issue directly​, what it would do was go down 5 rows from the current cell and paste it transposed next to the first cell, and repeat that action 5 times. If the data doesn't match that it wouldn't work.

It was just the only example I have of me personally using PasteSpecial.

When I'm off mobile l take a closer look again and get back to you.

[Nightwolf85]

It appears the error is coming from the -4163, but it appears that is the correct value for pasting values only.

I'll keep looking into it, but that might be a place for you to test as well.

*Edit
This doesn't give any error and pastes, but it isn't values only unless that is the default.
xl.ActiveSheet.Range("A1:D10").PasteSpecial()

**Edit 2
With further testing, PasteSpecial values only ( PasteSpecial(-4163) ) will ONLY work if the copied data are cells that contain formulas, otherwise it throws this error.

[magicinmath]

Thank you very much for figuring that out.

xl.ActiveSheet.Range("A1:D10").PasteSpecial() should work for the project I'm on now.

Much appreciated.