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

[zxo102]

Hi there,
I need your help for python <--> excel. I want to paste selected
cells (range) to different location on the same sheet in Excel through
python. I have tried it for a while but could not figure it out. Here
is my sample code:

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)).Select()
sh.Range(sh.Cells(1,1),sh.Cells(3,3)).Copy()
# sh.Range(sh.Cells(4,1),sh.Cells(6,3)).Paste()

The last line of the code does not work.

Thanks you very much.

Ouyang

 

[zxo102]

I found the solution for this. It needs to select a new location and
paste from "sh". Thank you for your reading this.

import win32com.client
xl=win32com.client.Dispatch("E­xcel.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.Cell­s(3,3)).Select()
sh.Range(sh.Cells(1,1),sh.Cell­s(3,3)).Copy()
sh.Cells(4,1).Select()
sh.Paste()


Ouyang

 

[mimicico]

I think you just need do:
sh.Range(sh.Cells(4,1),sh.Cell­s(6,3)).Value =
sh.Range(sh.Cells(1,1),sh.Cell­s(3,3)).Value

 

[Stuart Corrie]

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.