win32com Excel PasteSpecial

Question

I'm having some trouble with PasteSpecial in python. Here's the sample code:

import win32com.client as win32com
from win32com.client import constants

xl = win32com.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
wb = xl.Workbooks.Add ()
Sheet1 = wb.Sheets("Sheet1")

# Fill in some summy formulas
for i in range(10):
    Sheet1.Cells(i+1,1).Value = "=10*"+str(i+1)

Sheet1.Range("A1:A16").Copy()
Sheet1.Range("C1").Select()
Sheet1.PasteSpecial(Paste=constants.xlPasteValues)

I'm getting the following error:

TypeError: Paste() got an unexpected keyword argument 'Paste'

I know that paste is a keyword argument because of the MSDN here: http://msdn.microsoft.com/en-us/library/office/ff839476(v=office.15).aspx

Any idea why it won't let me do this? Can't really find much on the web.

Edit for solution(s):

import win32com.client as win32com
from win32com.client import constants

xl = win32com.gencache.EnsureDispatch('Excel.Application')
xl.Visible = True
wb = xl.Workbooks.Add ()
Sheet1 = wb.Sheets("Sheet1")

# Fill in some summy formulas
for i in range(10):
    Sheet1.Cells(i+1,1).Value = "=10*"+str(i+1)

Sheet1.Range("A1:A16").Copy()
Sheet1.Range("C1").PasteSpecial(Paste=constants.xlPasteValues)
# OR this I just found right after I posted this works as well:
xl.Selection.PasteSpecial(Paste=constants.xlPasteValues)

I don't work with python but try this `Sheet1.Range("C1").PasteSpecial(Paste=constants.xlPasteValues)` — Siddharth Rout, Oct 30 '14 at 23:42
Regarding your edit: You should avoid the use of `.Select/Selection`. You may want to see [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros/10718179#10718179) Excel-VBA link — Siddharth Rout, Oct 30 '14 at 23:48
Based upon other lines of code that you were not having a problem with, this should eliminate the copy & paste special altogether. `Sheet1.Range("C1:C16") = Sheet1.Range("A1:A16").Value`. — , Oct 31 '14 at 00:11

score 3 · Answer 1 · answered Sep 09 '15 at 07:33

You can get value for xlPasteFormats by execute macro in Excel vb:

Sub Macro2()
    Range("A7").Select
    ActiveCell.FormulaR1C1 = xlPasteFormats
End Sub

The value for xlPasteFormats is -4122

In Python script you can use

xlSheet.Range("A7:H7").Copy()
xlSheet.Range("A%s:H%s"%(r,r)).PasteSpecial(Paste=-4122)

score 2 · Accepted Answer · answered Oct 30 '14 at 23:47

I don't work with python but to do a PasteSpecial in Excel-VBA, you have to mention the cell where you want to perform the pastespecial, so try like

Sheet1.Range("C1").PasteSpecial(Paste=constants.xlPasteValues)

If you want a simple paste then I guess this should work

Sheet1.Paste

win32com Excel PasteSpecial

2 Answers2