Python-Excel: How to paste selected cells (range) to different location on the same sheet in Excel

Stuart Corrie scorrie at nildram.co.uk
Tue Aug 9 16:41:27 CEST 2005


Did you use the excel macro recorder and look at the results to help you 
write that program? It seems to bear all the hallmarks of code generated 
that way. The macro recorder can be great, but almost always it is 
possible to speed up code by altering it afterwards, to condense and 
speed up the VBA code. A slightly tighter version of your code is shown 
below:

import win32com.client
xl=win32com.client.Dispatch("Excel.Application")
xl.Visible=1
wb = xl.Workbooks.Add( )
sh=wb.Worksheets(1)
sh.Cells(1,1).Value = "Hello World!"
sh.Cells(3,3).Value = "Hello World!"
sh.Range(sh.Cells(1,1),sh.Cells(3,3)).Copy(sh.Cells(4,1))
sh.Range(sh.Cells(4,1),sh.Cells(6,3)).Select()

The first 7 lines are identical to yours. The way you work with excel in 
when entering data and formulae interactively, and so the way the macro 
recorder must work, involves lots of selection (or activation for 
switching workbooks) which can be slow and often not required when using 
code to manipulate excel's objects through COM from an external language 
or VBA in excel. Line 7 in your program 
"sh.Range(sh.Cells(1,1),sh.Cell­s(3,3)).Select()" actually does nothing 
because the range is specified again in line 8.

The copy method of a range object can take a parameter for a paste 
destination. The macro recorder will never generate code like this 
because it must record the selection between the copy and the paste 
operations.

The only minor drawback of the revised line 7 is that the range selected 
after the paste operation is the parameter given to copy not the data 
pasted, if this is required, line 8 fixes this.



More information about the Python-list mailing list