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

Discussion in 'Python' started by zxo102, Aug 4, 2005.

  1. zxo102

    zxo102 Guest

    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, Aug 4, 2005
    #1
    1. Advertising

  2. zxo102

    zxo102 Guest

    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
     
    zxo102, Aug 4, 2005
    #2
    1. Advertising

  3. zxo102

    Guest

    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, 2005
    #3
  4. Re: Python-Excel: How to paste selected cells (range) to differentlocation on the same sheet in Excel

    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.
     
    Stuart Corrie, Aug 9, 2005
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    1
    Views:
    745
  2. Peder Ydalus
    Replies:
    0
    Views:
    327
    Peder Ydalus
    Nov 16, 2003
  3. Christopher Brewster
    Replies:
    5
    Views:
    359
    John Machin
    Nov 14, 2008
  4. bluebaron
    Replies:
    3
    Views:
    768
    Jonathan N. Little
    Nov 4, 2009
Loading...

Share This Page