2

I am creating a Python script to paste values pulled from another file into an Excel template.

The Excel template contains many macros that process the data it is given. This data is then sent to the clipboard, and the unsaved Excel file is then closed.

I have used a combo of xlwt and xlrd (it's a .xls file) to try and write to the Excel file, but it seems that these commands access the file without explicitly opening it (the Excel file never comes on screen).

I was curious if anyone could point me in the right direction of some tools that would allow me to explicitly open an Excel file (e.g. subprocess.call()), paste the data into some cells, then let the user do the rest.

Any help or criticism is appreciated.

EDIT: I have been trying to use pywin32 for my purposes. It's not quite working. While it can successfully open the Excel file and run the macro, it cannot pass any values into the textbox of the macro.

Here is my current code:

import win32com.client as win32
import time

excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(r'C:\Users\me\Desktop\file.xlsm')
excel.Visible = True

#if I run wb.Application.SendKeys(1) here, it places a 1 into a cell on the worksheet

wb.Application.Run('Set_Up_Sheet') #running the macro, which successfully launches
time.sleep(2)
wb.Application.SendKeys(1) #this seems to have no effect

How can I send values to this macro's textbox?

FelixTheNub
  • 181
  • 2
  • 10
  • 1
    How is your question connect with its title? (you can't have variable keys, by the way). – cdarke Jun 08 '16 at 19:37
  • Sorry about that. I was going to ask a question before about a separate topic. Stack overflow saved it, and I overlooked it. It's changed now. – FelixTheNub Jun 08 '16 at 19:47
  • 1
    take a look at pyAutoIt ( https://pypi.python.org/pypi/PyAutoIt/0.3 ) – dot.Py Jun 08 '16 at 19:56
  • 1
    this may be useful too: http://seleniumsimplified.com/2016/01/can-i-use-selenium-webdriver-to-automate-a-windows-desktop-application/ – dot.Py Jun 08 '16 at 19:57
  • Thank you very much Dot_Py. I will check those out now. – FelixTheNub Jun 08 '16 at 20:00
  • 2
    See this answer: http://stackoverflow.com/a/1067842/774651 – Lol4t0 Jun 08 '16 at 20:01
  • Thank you Lol4t0! That looks like exactly what I was imagining. – FelixTheNub Jun 08 '16 at 20:11
  • I tried a few things, but pywin32 seems to be the most promising. I edited my original post to reflect my progress. – FelixTheNub Jun 14 '16 at 17:36
  • Seems like you have no cell selected, so sending a key is sending it somewhere else... try: `wb.Cells(1,1).Value = "1"` ([source](http://pythonexcels.com/python-excel-mini-cookbook/)) – pekapa Jun 14 '16 at 17:56
  • Thanks for your time pekapa. I'm actually not interested in placing a value into a cell. When I start the macro, a new textbox window appears, and that is where I'd like to pass some keyboard inputs. – FelixTheNub Jun 14 '16 at 18:09

0 Answers0