copy & paste isolation with win32com & python

Tags: python excel
Question!

Is there a way of using python and win32com to copy and paste such that python scripts can run in the background and not mess up the "users" copy and paste ability?

from win32com.client import Dispatch
import win32com.client

xlApp = Dispatch("Excel.Application")
xlWb = xlApp.Workbooks.Open(filename_xls)
ws = xlWb.Worksheets(1)
xlApp.Visible=False

ws.Range('a1:k%s' % row).select
ws.Range('a1:k%s' % row).cut
ws.Range('a7').select
ws.paste

assume that the script will be running continuously on a large collection of datasets...

Ok, some more clarity to the question, I need the formating, all of it, so just grabbing t values is of course simple, but not exactly what is needed.

So then let me phrase the question as: What is there a why to grab both the value and its excel formating in python without the select, copy, and paste routine?



Answers

A little late, but I think I have your solution, and since I had trouble finding it it'll help someone anyway. This piece of code will copy everything (value, formula, formating, etc) without using selections (thus not messing with your users, and this is faster than using selections)

First I open excel like that :

xl = client.Dispatch("Excel.Application")
wb = xl.Workbooks.Open("c:/somepath/file.xls")
xl.Visible = 1

To copy and paste on the same worksheet while conserving formating, formulas, etc:

ws = wb.Sheets("someSheet") #you can put the sheet number instead of it's name
ws.Range('a1:k%s' % row).Copy() #copy works on all range objects (ws.Columns(), ws.Cells, etc)
ws.Paste(ws.Range('a7'))

To copy and paste on an other worksheet while conserving formating, formulas, etc:

source_ws = wb.Sheets("SheetContainingData")
destination_ws = wb.Sheets("SheetWhereItNeedsToGo")
source_ws.Range('a1:k%s' % row).Copy()
destination_ws.Paste(destination_ws.Range('a7'))

case sensitive.



Instead of:

ws.Range('a1:k%s' % row).select
ws.Range('a1:k%s' % row).cut
ws.Range('a7').select
ws.paste

I did:

ws.Range("A1:K5").Copy(ws.Range("A7:K11"))

according to MSDN: Excel Object Model Reference

By : ta2-1


Just read the data out into python, and then write it back.

Instead of:

ws.Range('a1:k%s' % row).select
ws.Range('a1:k%s' % row).cut
ws.Range('a7').select
ws.paste

Process the data cell-by-cell:

for r in range(1, row+1):    # I think Excel COM indexes from 1
    for c in range (1, 12):  # a--k
        val = ws.Cells(r, c).Value
        ws.Cells(r, c).Value = ''    # Blank this cell; equivalent to cut
        ws.Cells(?, ?).Value = val   # Write it somewhere ..

I'm not sure what pasting a two-dimensional selection into a single cell does, so I can't do the last line for you. You may find that you need to do two loops; one to read in the data, and then a second to write it back out.



This video can help you solving your question :)
By: admin