By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,361 Members | 1,955 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,361 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Aug 4 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Aug 4 '05 #2

P: n/a
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

Aug 4 '05 #3

P: n/a
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.Cell s(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.
Aug 9 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.