Automating Windows Applications with win32com

1
Automating Windows Applications with win32com

• Roy H. Han
• Python Conference 2008
• Chicago

2
Next thirty minutes Intro

• Why 3 min
• Setup your computer 3 min
• Icebreaker 7 min
• Applications via COM 15 min
• Overview tutorial projects 2 min

3
Why access existing applications through Python?

• When your clients say,
• I wish (my application) could do
• I dont have time to learn (your application)
• I am more familiar using (my application)

People will use your work if it integrates
with applications with which they are comfortable
4
Why access existing applications through Python?

• Usability
• People will use your work if it integrates with
• applications with which they are comfortable.
• Shoulders of Giants
• Combine features from multiple applications.
• Less Clicking and Typing
• Make a computer do it.

5
Make your computer do it

• Create a report from a database
• Process email
• Extract data from an application file
• Populate a database

6
Icebreaker Your expectations

• While you are setting up your laptops, lets go
  around the room and introduce ourselves
• (Thirty seconds each)
• Your name
• A little about yourself
• A problem you hope to solve using what you learn
  from this tutorial

7
Setup your computer

• Install win32com
• http//sourceforge.net/projects/pywin32/
• Extract tutorial code package
• Launch IDLE
• import sys sys.path.append(ryourCodePath)
• import os os.chdir(ryourCodePath)
• Import win32com
• from win32com.client import Dispatch

8
Which applications can we access through Python?

• Applications with COM interfaces
• using Mark Hammonds
• Python for Windows Extensions
• http//sourceforge.net/projects/pywin32/

9
What do we need to automate an application?

• ProgID
• To dispatch the application
• COM documentation
• To use methods from the application

10
How do I know if my target application has a COM
interface?

• Find the ProgID corresponding to your target
  application
• Start gt Run gt regedit.exe gt Edit gt Find gt Excel
• Try connecting to your target application
• from win32com.client import Dispatch
• Examine your target application using a COM
  browser
• /Python25/Lib/site-packages/win32com/client/combr
  owse.py

11
How do I know if my target application has a COM
interface?

• Find the ProgID corresponding to your target
  application
• Start gt Run gt regedit.exe gt Edit gt Find gt Excel
• VersionIndependentProgID Excel.Application
• Try connecting to your target application
• from win32com.client import Dispatch
• Dispatch(Excel.Application)
• Examine your target application using a COM
  browser
• /Python25/Lib/site-packages/win32com/client/combr
  owse.py
• Microsoft Excel 9.0 Object Library

12
Which applications can we access through COM?

• Get a partial list of ProgIDs using WBEM
• Pros Fast
• Cons Partial
• Cycle through the registry and try to dispatch
  each ProgID using a script
• Pros Complete
• Cons Very Slow

13
Get a partial list of ProgIDsusing WBEM

• Filename getProgIDsFromWBEM.py
• Dispatch WBEM
• Extract progIDs
• Record results
• Adapted from
• http//win32com.goermezer.de/content/view/154/18
  9/

14
Get a partial list of ProgIDsusing WBEM

• Filename getProgIDsFromWBEM.py
• Dispatch WBEM
• wbem Dispatch('WbemScripting.SWbemLocator')
• wbemServer wbem.ConnectServer('.',
  'root\cimv2')
• Extract progIDs
• sql 'SELECT ProgID FROM Win32_ProgIDSpecificatio
  n'
• results wbemServer.ExecQuery(sql)
• progIDs x.ProgID for x in results
  progIDs.sort()
• Record results
• outputFile open('progIDs_partialFromWBEM.txt',
  'wt')
• outputFile.write('\n'.join(progIDs))
• outputFile.close()

15
Cycle through the registry and try to dispatch
each ProgID

• Filename getProgIDsFromRegistry.py
• Import other modules
• Initialize list of keys of search
• While we have keysToSearch,
• Get key
• For each subkey,
• Extract progIDs
• Recurse children
• Print categories
• Sort progIDs
• Try to open each progID in a separate process

16
Cycle through the registry and try to dispatch
each ProgID

• Filename getProgIDsFromRegistry.py
• Import other modules
• from _winreg import
• Initialize list of keys of search
• keysToSearch
• HKEY_CLASSES_ROOT,
• OpenKey(HKEY_CURRENT_USER, 'Software'),
• OpenKey(HKEY_LOCAL_MACHINE, 'Software'),
• categories set() progIDs set()

17
Cycle through the registry and try to dispatch
each ProgID

• While we have keysToSearch,
• while keysToSearch
• Get key
• key keysToSearch.pop() count_subkeys
  QueryInfoKey(key)0
• For each subkey,
• for index in xrange(count_subkeys)
• subkeyName EnumKey(key, index)
• try subkey OpenKey(key, subkeyName)
• except WindowsError print 'Could not
  access registry key', subkeyName
• subkeyNameLower subkeyName.lower()
• Extract progIDs
• if 'progid' in subkeyNameLower
• categories.add(subkeyNameLower)
• if 'open' in subkeyNameLower
• count_values QueryInfoKey(subkey
  )0
• for valueIndex in xrange(1,
  count_values)
• progIDs.add(EnumValue(subkey,
  index)0)
• else progIDs.add(EnumValue(subkey,
  0)1)
• Recurse children

18
Cycle through the registry and try to dispatch
each ProgID

• Print categories
• print 'Categories ' ', '.join(categories)
• Sort progIDs
• progIDs list(progIDs)
• progIDs.sort()
• Try to open each progID in a separate process
• import killableprocess Courtesy of Peter
  Astrand and Benjamin Smedberg
• progIDs_dispatchable
• outputFile open('progIDs_completeFromRegistry.tx
  t', 'wt')
• for progID in progIDs
• print 'd/d s' (progIDs.index(progID),
  len(progIDs), progID),
• returnCode killableprocess.call('python
  dispatch.py ' progID, timeout30)
• if not returnCode
• print 'yes'
• outputFile.write(progID '\n')
• outputFile.flush()
• else print 'no'

19
Cycle through the registry and try to dispatch
each ProgID

• Filename dispatch.py
• Import modules
• Try to dispatch the specified ProgID
• If we have an error, exit with a negative value

20
Cycle through the registry and try to dispatch
each ProgID

• Filename dispatch.py
• Import modules
• from win32com.client import Dispatch
• import sys, pywintypes
• Try to dispatch the specified ProgID
• try x Dispatch(sys.argv1) del x
• If we have an error, exit with a negative value
• except pywintypes.com_error sys.exit(-1)

21
ProgIDs for some applications

• Skype4COM.Skype
• Excel.Application
• Word.Application
• Lotus.NotesSession
• MAPI.Session
• Outlook.Application
• Vim.Application
• Matlab.Application
• SAPI.SpVoice
• SAPI.SpSharedRecognizer
• Speech.VoiceCommand
• Python.Interpreter
• Access.Application
• GoogleEarth.ApplicationGE

• EsriGeoprocessing.GpDispatch
• AcroExch.App
• WMPlayer.OCX
• PowerPoint.Application
• Nero.Nero
• Visio.Application
• Eudora.EuApplication
• InternetExplorer.Application
• AutoCAD.Application
• WbemScripting.SWbemLocator
• WScript.Shell
• OmniPage14
• Intuit.Quicken
• Bloomberg.Data

22
How do we know what methods and attributes are
available?

• Find COM documentation for the target application
• Run makepy
• /Python25/Lib/site-packages/win32com/client/makep
  y.py
• See hints (CTRL-space in IDLE, Tab in IPython)
• You can use program constants
• Your COM interface runs a little faster
• Examine your target application using a COM
  browser
• /Python25/Lib/site-packages/win32com/client/combr
  owse.py

23
Review What do we need to automate an
application?

• ProgID
• To dispatch the application
• COM documentation
• To use methods from the application

24
Mini Projects Tutorial Format

• My demo
• We pseudocode
• We code through IDLE
• Your demo

25
Basics Mail Events Add-Ins More

• Make an Excel spreadsheet from a database
• Update a database using an Excel spreadsheet
• Make a Word document from a database
• Run Python commands through Vim
• Get contacts from Outlook

26
Basics Mail Events Add-Ins More

• Update a database using Outlook
• Reply to email with Outlook
• Reply to email with Lotus Notes

27
Basics Mail Events Add-Ins More

• Make a toolbar button in Excel
• Run Python commands from an Excel spreadsheet
• Update a database using an Excel spreadsheet in
  real-time

28
Basics Mail Events Add-Ins More

• Write an add-in for Excel
• Write an add-in for Word
• Write an add-in for Outlook

29
Basics Mail Events Add-Ins More

• Speak using SpVoice
• Call using Skype
• Convert a document to PDF
• Do calculations in Matlab
• Clip images from shapefiles through ArcGIS
• Extract data from an Access database
• Move your mouse

30
Next thirty minutes Basics

• Make an Excel spreadsheet from a database
  10 min
• Update a database using an Excel spreadsheet
  5 min
• Make a Word document from a database
  5 min
• Run Python commands through Vim
  5 min
• Get contacts from Outlook
  5 min

31
Make an Excel spreadsheet from a database

• Get results from database
• Dispatch Excel
• Put results into cells
• Format cells
• Release Excel to prevent hanging instances

32
Make an Excel spreadsheet from a database

• Get results from database
• from model_database import Model
• database Model()
• results database.seeInvoices()

33
Make an Excel spreadsheet from a database

• Dispatch Excel
• from win32com.client import Dispatch, constants
• excel Dispatch('Excel.Application')
• excel.Visible 1
• excel.WindowState constants.xlMaximized
• excel.Workbooks.Add()

34
Make an Excel spreadsheet from a database

• Make shortcut
• sheet excel.ActiveSheet
• Add title
• cell sheet.Cells(1, 1)
• cell.Value 'Invoices'
• cell.Font.Size 20
• cell.Font.Bold True

35
Make an Excel spreadsheet from a database

• Put results into cells
• def drawText(row, items, isBold False)
• column 1
• for item in items
• cell sheet.Cells(row, column)
• cell.Value item
• cell.Font.Bold isBold
• column 1
• return row 1
• row 3
• for x in results row drawText(row, x)

36
Make an Excel spreadsheet from a database

• Put results into cells
• formulaString 'sum(cdcd)' (3, row - 1)
• items '', 'Total', formulaString
• row drawText(row, items, True)
• Insert header
• sheet.Rows(3).Insert(constants.xlDown)
• drawText(3, 'Invoice ', 'Date', 'Amount',
  'City', 'Paid', True)

37
Make an Excel spreadsheet from a database

• Format cells
• sheet.Columns(2).NumberFormat 'mmm d, yyyy'
• sheet.Columns(3).NumberFormat '0.00'
• sheet.Columns(5).NumberFormat '0.00'
• sheet.Columns(4).AutoFit()
• sheet.Rows(3).HorizontalAlignment
  constants.xlCenter
• Center title
• sheet.Range('A1E1').Merge()
• sheet.Rows(1).HorizontalAlignment
  constants.xlCenter
• sheet.Name 'Invoices
• Release Excel to prevent hanging instances
• del excel

38
Update a databaseusing an Excel spreadsheet

• Dispatch Excel
• Load rows from spreadsheet
• Update entries in database
• Release Excel

39
Update a databaseusing an Excel spreadsheet

• Dispatch Excel
• excel Dispatch(Excel.Application)

40
Update a databaseusing an Excel spreadsheet

• Load line from spreadsheet
• line excel.ActiveSheet.Rows(7).Value05
• line list(line)
• Note that dates are returned as PyTime
• date line1
• date
• Convert PyTime to datetime
• import datetime
• line1 datetime.date.fromtimestamp(int(date))

41
Update a databaseusing an Excel spreadsheet

• Update entries in database
• database.editInvoice(line)
• Get results from database
• invoiceNumber line0
• database.seeInvoice(invoiceNumber)
• Release Excel to prevent hanging instances
• del excel

42
Make a Word documentfrom a database

• Dispatch Word
• Add cities to document
• Release Word

43
Make a Word documentfrom a database

• Dispatch Word
• word Dispatch(Word.Application)
• word.Visible 1
• word.Documents.Add()
• document word.ActiveDocument
• Add cities to document
• cities list(set(x3 for x in results))
• cities.sort()
• for x in cities
• document.Content.InsertAfter(x \n)
• Release Word
• del word

44
Run Python commands from Vim

• Connect to Vim
• Send keys to Vim
• Get current line from Vim
• Connect to Python
• Execute and evaluate line in Python
• Create Vim proxy using wxPython and wxGlade

45
Run Python commands from Vim

• Connect to Vim
• vim Dispatch('Vim.Application')
• Send keys to Vim
• vim.SendKeys(ac 6)
• Get current line from Vim
• line vim.Eval(getline(1))
• Release Vim
• del vim

46
Run Python commands from Vim

• Connect to Python
• python Dispatch('Python.Interpreter')
• Execute line
• python.Exec(line)
• Evaluate
• python.Eval(c)
• Release Python
• del python
• You can use wxPython wxGlade to
• build a Vim interface to Python

47
Run Python commands from Vim with wxPython
wxGlade

• wx.EVT_CHAR(self.text_ctrl, self.onKey)
• def onKey(self, event)
• Get the most recent key
• recentKey chr(event.GetKeyCode())
• self.text_ctrl.SetValue(recentKey)
• Send key to Vim
• vim.SendKeys(recentKey)

48
Run Python commands from Vim with wxPython
wxGlade

• def onClick(self, event)
• Get current line from Vim
• line vim.Eval('getline(".")')
• Send line to Python
• python.Exec(line)

49
Run Python commands from Vim with wxPython
wxGlade

• import wx
• from win32com.client import Dispatch
• vim Dispatch('Vim.Application')
• python Dispatch('Python.Interpreter')

50
Run Python commands from Vim with wxPython
wxGlade

• class MyFrame(wx.Frame)
• def __init__(self, args, kwds)
• begin wxGlade MyFrame.__init__
• kwds"style" wx.DEFAULT_FRAME_STYLE
• wx.Frame.__init__(self, args, kwds)
• self.text_ctrl wx.TextCtrl(self, -1,
  "", stylewx.TE_PROCESS_ENTERwx.TE_PROCESS_TAB)
• self.button wx.Button(self, -1, "Run")
• self.__set_properties()
• self.__do_layout()
• self.Bind(wx.EVT_BUTTON, self.onClick,
  self.button)
• end wxGlade
• wx.EVT_CHAR(self.text_ctrl, self.onKey)

51
Run Python commands from Vim with wxPython
wxGlade

• def __set_properties(self)
• begin wxGlade MyFrame.__set_properties
• self.SetTitle("Python from Vim")
• end wxGlade
• def __do_layout(self)
• begin wxGlade MyFrame.__do_layout
• sizer_1 wx.BoxSizer(wx.HORIZONTAL)
• sizer_1.Add(self.text_ctrl, 1, wx.EXPAND,
  0)
• sizer_1.Add(self.button, 0, 0, 0)
• self.SetSizer(sizer_1)
• sizer_1.Fit(self)
• self.Layout()
• end wxGlade

52
Run Python commands from Vim with wxPython
wxGlade

• def onKey(self, event)
• Get the most recent key
• recentKey chr(event.GetKeyCode())
• self.text_ctrl.SetValue(recentKey)
• Send key to Vim
• vim.SendKeys(recentKey)
• def onClick(self, event)
• Get current line from Vim
• line vim.Eval('getline(".")')
• Send line to Python
• python.Exec(line)

53
Run Python commands from Vim with wxPython
wxGlade

• if __name__ "__main__"
• app wx.PySimpleApp(0)
• wx.InitAllImageHandlers()
• frame_1 MyFrame(None, -1, "")
• app.SetTopWindow(frame_1)
• frame_1.Show()
• app.MainLoop()

54
Get contacts from Outlook

• Connect to Outlook
• Open contacts folder
• Extract contacts
• Release Outlook

55
Get contacts from Outlook

• Connect to Outlook
• outlook Dispatch(Outlook.Application)
• ns outlook.GetNamespace('MAPI')
• ns.Logon()
• Open contacts folder
• x constants.olFolderContacts
• folder ns.GetDefaultFolder(x)

56
Get contacts from Outlook

• Extract contacts
• items folder.Items
• count Items.Count
• for index in xrange(1, count 1)
• item itemsindex
• value item.FullName, item.EmailAddress
• print s (s) value
• Release Outlook
• ns.Logoff()
• del outlook

57
Explore handles

• import win32gui
• def callback(handle, results)
• klass win32gui.GetClassName(handle)
• title win32gui.GetWindowText(handle)
• results.append((title, klass))
• def showChildren(handle)
• results
• win32gui.EnumChildWindows(handle, callback,
  results)
• results.sort()
• for x in results print x
• showChildren(0)

58
Explore dialog boxes

• klass '32770'
• title 'Microsoft Outlook'
• handle_dialog win32gui.FindWindow(klass, title)
• if handle_dialog showChildren(handle_dialog)
• else print The dialog is not open

59
Interact with dialog boxes

• def clickHandle(handle)
• win32gui.SendMessage(handle,
  win32con.WM_ACTIVATE, win32con.MA_ACTIVATE, 0)
• win32gui.SendMessage(handle,
  win32con.BM_CLICK, 0, 0)
• h handle_dialog
• k 'Button
• t 'Allow access for'
• handle_button win32gui.FindWindowEx(h, 0, k, t)
• clickHandle(handle_button)

60
Bypass the Outlook security dialog box

• Thanks to Samy http//www.lucidx.com/outsmart.tx
  t
• Thanks to Misha Verplak http//mail.python.org/p
  ipermail/python-win32/2002-December/000635.html
• import win32gui, win32con, win32api
• def clickHandle(h)
• win32gui.SendMessage(h, win32con.WM_ACTIVATE,
  win32con.MA_ACTIVATE, 0)
• win32gui.SendMessage(h, win32con.BM_CLICK, 0,
  0)

61
Bypass the Outlook security dialog box

• def autoclick()
• Get h_dialog
• h_dialog win32gui.FindWindow('32770',
  'Microsoft Outlook')
• if not h_dialog return
• Click Allow
• h_button win32gui.FindWindowEx(h_dialog, 0,
  'Button', 'Allow access for')
• clickHandle(h_button)

62
Bypass the Outlook security dialog box

• Select 10 minutes
• h_combobox win32gui.FindWindowEx(h_dialog,
  0, 'ComboBox', '')
• win32gui.SendMessage(h_combobox,
  win32con.CB_SELECTSTRING, -1, '10 minutes')
• Save mouse position
• x, y win32api.GetCursorPos()

63
Bypass the Outlook security dialog box

• For security, Outlook ignores mouse events
• unless you click in the area of the dialog
• coords win32gui.GetWindowRect(h_dialog)
• x1, y1 coords2
• win32api.SetCursorPos((x1 10, y1 10))
• method win32api.mouse_event
• method(win32con.MOUSEEVENTF_LEFTDOWN,0,0,0,0)
• method(win32con.MOUSEEVENTF_LEFTUP,0,0,0,0)

64
Bypass the Outlook security dialog box

• Click Yes
• h_button_yes win32gui.FindWindowEx(h_dialog,
  0, 'Button', 'Yes')
• clickHandle(h_button_yes)
• Restore mouse position
• win32api.SetCursorPos(x, y)

65
Bypass the Outlook security dialog box

• import threading, time
• class Autoclicker(threading.Thread)
• def run(self)
• while True
• time.sleep(1)
• try autoclick()
• except pass
• if __name__ '__main__'
• autoclicker Autoclicker()
• autoclicker.start()

66
Catching COM errors

• import pywintypes
• try
• Your COM code
• pass
• except pywintypes.com_error
• Your COM error handling
• pass

67
Next thirty minutes Mail

• Update a database using Outlook
  10 min
• Reply to email using Outlook
  10 min
• Reply to email using Lotus Notes
  10 min

68
Update a database using Outlook

• Connect to Outlook
• Open inbox folder
• Append inbox subfolders recursively
• For each folder,
• For each message,
• If the subject of the message matches,
• Update database
• Release Outlook

69
Update a database using Outlook

• Connect to Outlook
• outlook Dispatch(Outlook.Application)
• ns outlook.GetNamespace('MAPI')
• ns.Logon()

70
Update a database using Outlook

• Open inbox folder
• x constants.olFolderInbox
• inbox ns.GetDefaultFolder(x)
• Examine a message
• messages inbox.Items
• message messages1
• print message.SenderName
• print message.SentOn.Format()

71
Update a database using Outlook

• Append inbox subfolders recursively
• def getSubFolders(parentFolder)
• count parentFolder.Folders.Count subFolders
  
• if count
• for whichFolder in xrange(1, count 1)
• subFolder folderwhichFolder
• subFolders.append(subFolder)
• subFolders.extend(getSubfolders(subFolder
  ))
• return subFolders
• Append inbox subfolders recursively
• folders inbox
• folders.extend(getSubFolders(inbox))

72
Update a database using Outlook

• For each folder,
• for folder in folders
• For each message,
• message folder.Items.GetFirst()
• while message
• If the subject of the message matches,
• if message.Subject Add Me
• Update database
• database.addText(message.Body)
• Get next message
• message messages.GetNext()
• Release Outlook
• ns.Logoff() del outlook

73
Reply to email with Outlook

• Connect to Outlook
• Open inbox folder
• For each message,
• Extract query terms from subject
• Execute query
• Write email with attachments
• Release Outlook

74
Reply to email with Outlook

• Connect to Outlook
• outlook Dispatch(Outlook.Application)
• ns outlook.GetNamespace('MAPI')
• ns.Logon()
• Open inbox folder
• x constants.olFolderInbox
• inbox ns.GetDefaultFolder(x)

75
Reply to email with Outlook

• Prepare regular expression
• import re pattern_query re.compile(\?(.))
• Prepare writeReply
• def writeReply(toWhom, subject)
• write(toWhom, Re subject, Hi)
• Prepare move
• def move(destinationFolder, message)
• message.Move(destinationFolder)

76
Reply to email with Outlook

• def write(to, subj, body, attachments )
• x constants.olMailItem
• m outlook.CreateItem(x)
• m.To to m.Subject subj m.Body body
• for attachment in attachments
• p attachment, constants.olByValue, 1
• m.Attachments.Add(p)
• message.Send()

77
Reply to email with Outlook

• For each message,
• messages inbox.Items
• m messages.GetFirst()
• while m
• Test the subject for a query
• match pattern_query.match(m.Subject)
• If we have a match, reply
• if match writeReply(m.SenderName, m.Subject)
• Get next message
• m messages.GetNext()
• ns.Logoff() del a Release Outlook

78
Reply to email with Lotus Notes

• Connect to Lotus Notes
• Open inbox folder
• For each message,
• Extract query terms from subject
• Execute query
• Write email with attachments
• Release Lotus Notes

79
Reply to email with Lotus Notes

• Connect to Lotus Notes
• import pywintypes
• notes Dispatch('Lotus.NotesSession')
• try
• notes.Initialize(dbPassword)
• notesDB notes.GetDatabase(dbHost, dbName)
• Open inbox folder
• inbox notesDB.GetView('(Inbox)')
• except pywintypes.com_error raise

80
Reply to email with Lotus Notes

• def write(to, subj, htmlBody, attachments )
• notes.ConvertMime False Prevent rich text
• m notesDB.CreateDocument()
• m.ReplaceItemValue('SendTo', to)
• m.ReplaceItemValue('Subject', subj)
• stream notes.CreateStream()
• stream.WriteText(htmlBody)
• m_body m.CreateMIMEEntity()
• strHead 'text/html charsetutf-8
• parcel stream, strHead, constants.ENC_NONE
• m_body.SetContentFromText(parcel)

81
Reply to email with Lotus Notes

• Add attachments
• for index in xrange(len(attachments))
• attach attachmentsindex
• if not attach continue
• strID 'attachmentd' index
• m_attach m.CreateRichTextItem(strID)
• x constants.EMBED_ATTACHMENT
• m_attach.EmbedObject(x, '', attach, None)
• message.Send(False)
• notes.ConvertMime True Restore setting

82
Reply to email with Lotus Notes

• def move(destinationFolderName, message)
• message.PutInFolder(destinationFolderName)
• message.RemoveFromFolder('(Inbox)')
• Prepare writeReply
• def writeReply(toWhom, subject)
• html 'lthtmlgtltbodygtHeylt/bodygtlt/htmlgt
• write(toWhom, Re subject, html)

83
Reply to email with Lotus Notes

• For each message,
• m inbox.GetFirstDocument()
• while m
• fromWhom m.GetItemValue('From')0.strip()
• subj m.GetItemValue('Subject')0.strip()
• Test the subject for a query
• match pattern_query.match(subj)
• If we have a match, reply
• if match writeReply(fromWhom, subj)
• Get next message
• m inbox.GetNextDocument(m)

84
Next thirty minutes Events

• Make a toolbar button in Excel
  10 min
• Update a database
• from an Excel spreadsheet in real-time
  10 min
• Run Python commands from an Excel spreadsheet
  10 min

85
Make a toolbar button in Excel

• Dispatch Excel
• excel Dispatch(Excel.Application)
• Add a toolbar
• toolbar excel.CommandBars.Add(
• Name myToolbar',
• Position constants.msoBarTop,
• MenuBar constants.msoBarTypeNormal,
• Temporary True)
• toolbar.Visible True

86
Make a toolbar button in Excel

• Define button handler
• class ButtonEvent(object)
• def OnClick(self, button, cancel)
• import win32ui
• import win32con
• win32ui.MessageBox('Welcome',
• 'Message', win32con.MB_OK)
• return cancel

87
Make a toolbar button in Excel

• Add a button to the toolbar
• button toolbar.Controls.Add(1)
• button DispatchWithEvents(button,
• ButtonEvent)
• button.Width 34
• button.Caption Welcome
• button.Style constants.msoButtonCaption

88
Debugging using win32traceutil

• import win32traceutil
• Run PythonWin
• gt Tools gt Trace Collector Debugging Tool
• before trying the next statement
• print welcome

89
Update database from an Excel spreadsheet in
real-time

• Define event handler
• Dispatch Excel with event handler
• Listen for events
• If we have an event,
• Get row and update database using row
• Give feedback in a cell of the row
• Adapted from /Python25/Lib/site-packages/win32co
  m/test/testMSOfficeEvents.py
• Courtesy of Mark Hammond

90
Update database from an Excel spreadsheet in
real-time

• Define event handler
• class EventManager(object)
• def OnSheetBeforeDoubleClick(self, sheet,
• target, cancel)
• print Updating database
• Update database here
• pass

91
Update database from an Excel spreadsheet in
real-time

• Dispatch Excel with event handler
• from win32com.client import DispatchWithEvents
• excel DispatchWithEvents('Excel.Application',
  EventManager)
• excel.Visible 1

92
Update database from an Excel spreadsheet in
real-time

• Listen for events
• import threading, pythoncom
• stopEvent threading.Event()
• while True
• pythoncom.PumpWaitingMessages()
• Necessary so that python doesn't hog CPU
• stopEvent.wait(.2)
• if stopEvent.isSet()
• stopEvent.clear()
• break

93
Run Python commandsfrom an Excel spreadsheet

• Import other modules
• Define event handler
• Dispatch Python
• Dispatch Excel with event handler
• Listen for events
• Prevent hanging references

94
Run Python commandsfrom an Excel spreadsheet

• Import other modules
• from win32com.client import Dispatch,
  DispatchWithEvents, constants
• from pywintypes import com_error
• Define event handler
• class EventManager(object)
• User double-clicks a cell
• def OnSheetBeforeDoubleClick(self, sheet,
  target, cancel)
• inputCell excel.ActiveCell
• inputValue str(inputCell.Value)
• if inputValue
• outputCell excel.ActiveSheet.Cells(i
  nputCell.Row, inputCell.Column 1)
• Try executing it
• try python.Exec(inputValue)
• except com_error, err
  outputCell.Value str(err)

95
Run Python commandsfrom an Excel spreadsheet

• Dispatch Python
• python Dispatch('Python.Interpreter')
• Dispatch Excel with event handler
• excel DispatchWithEvents('Excel.Application',
  EventManager)
• excel.Visible 1
• excel.Workbooks.Add()
• excel.ActiveSheet.Cells(1, 1).Value 'Commands'
• excel.ActiveSheet.Columns('A').ColumnWidth 20
• excel.ActiveSheet.Cells(1, 2).Value 'Errors'
• excel.ActiveSheet.Columns('B').ColumnWidth 60
• excel.ActiveSheet.Columns('B').WrapText True
• excel.ActiveSheet.Columns('B').HorizontalAlignment
  constants.xlLeft
• excel.ActiveCell.Offset(2).Activate()

96
Run Python commandsfrom an Excel spreadsheet

• Listen for events
• import threading, pythoncom
• stopEvent threading.Event()
• while True
• pythoncom.PumpWaitingMessages()
• Necessary so that python doesn't hog CPU
• stopEvent.wait(.2)
• if stopEvent.isSet()
• stopEvent.clear()
• break
• Prevent hanging references
• del excel
• del python

97
Next ten minutes Add-ins

• Write an add-in for Excel
  8 min
• Write an add-in for Word
  1 min
• Write an add-in for Outlook
  1 min

98
How to write an add-in

• Find a CLSID for your add-in
• Cut and paste the add-in class definition
• Define the behavior for your add-in by overriding
  methods in the class definition
• Register your add-in

99
Write an add-in for Excel

• Bring COM modules into Python
• Define add-in class
• Register add-in
• Courtesy of Eric Koome

100
Write an add-in for Excel

• Import other modules
• from win32com import universal
• from win32com.client import gencache,
  DispatchWithEvents, constants
• import pythoncom, sys
• Bring COM modules into Python
• gencache.EnsureModule('00020813-0000-0000-C000-00
  0000000046', 0, 1, 3, bForDemandTrue) Excel 9
• gencache.EnsureModule('2DF8D04C-5BFA-101B-BDE5-00
  AA0044DE52', 0, 2, 1, bForDemandTrue) Office
  9
• universal.RegisterInterfaces('AC0714F2-3D04-11D1-
  AE7D-00A0C90F26F4', 0, 1, 0, '_IDTExtensibility2
  ')

101
Write an add-in for Excel

• Define button handler
• class ButtonEvent(object)
• def OnClick(self, button, cancel)
• import win32ui, win32con
• win32ui.MessageBox('Welcome', 'Message',
  win32con.MB_OK)
• return cancel
• Define add-in class
• class Addin(object)
• _com_interfaces_ '_IDTExtensibility2'
• _public_methods_
• _reg_clsctx_ pythoncom.CLSCTX_INPROC_SERVER
• _reg_clsid_ '2F1E606F-2A7B-46F9-AF6A-267C00
  36C348'
• _reg_progid_ 'Python.Test.Addin'
• _reg_policy_spec_ 'win32com.server.policy.Ev
  entHandlerPolicy'
• def __init__(self) import win32traceutil
  self.application None

102
Write an add-in for Excel

• def OnConnection(self, application, connectMode,
  addin, custom)
• print 'OnConnection', application,
  connectMode, addin, custom
• try
• self.application application
• myToolbar self.application.CommandBa
  rs.Add(Name 'Run Toolbar', Position
  constants.msoBarTop, MenuBar constants.msoBarTyp
  eNormal, Temporary True)
• myButton myToolbar.Controls.Add(1)
• self.myButton DispatchWithEvents(myB
  utton, ButtonEvent)
• self.myButton.Width '34'
• self.myButton.Caption 'Run'
• self.myButton.Style
  constants.msoButtonCaption
• myToolbar.Visible True

103
Write an add-in for Excel

• except pythoncom.com_error, (hr, msg,
  exc, arg)
• print 'The Excel call failed with
  code d s' (hr, msg)
• if exc is None print 'There is no
  extended error information'
• else
• wcode, source, text, helpFile,
  helpId, scode exc
• print 'The source of the error
  is', source
• print 'The error message is',
  text
• print 'More info can be found in
  s (idd)' (helpFile, helpId)
• def OnDisconnection(self, mode, custom)
• print 'OnDisconnection'
• self.application.CommandBars('Run
  Toolbar').Delete
• del self.application
• def OnAddInsUpdate(self, custom) print
  'OnAddInsUpdate', custom
• def OnStartupComplete(self, custom) print
  'OnStartupComplete', custom
• def OnBeginShutdown(self, custom) print
  'OnBeginShutdown', custom

104
Write an add-in for Excel

• Register add-in
• def RegisterAddin(klass)
• import _winreg
• key _winreg.CreateKey(_winreg.HKEY_CURRENT_U
  SER, 'Software\\Microsoft\\Office\\Excel\\Addins')
  
• subkey _winreg.CreateKey(key,
  klass._reg_progid_)
• _winreg.SetValueEx(subkey, 'CommandLineSafe',
  0, _winreg.REG_DWORD, 0)
• _winreg.SetValueEx(subkey, 'LoadBehavior', 0,
  _winreg.REG_DWORD, 3)
• _winreg.SetValueEx(subkey, 'Description', 0,
  _winreg.REG_SZ, 'Run Toolbar')
• _winreg.SetValueEx(subkey, 'FriendlyName', 0,
  _winreg.REG_SZ, 'Runs the contents of the cell in
  Python')

105
Write an add-in for Excel

• Unregister add-in
• def UnregisterAddin(klass)
• import _winreg
• try _winreg.DeleteKey(_winreg.HKEY_CURRENT_US
  ER, 'Software\\Microsoft\\Office\\Excel\\Addins\\'
  klass._reg_progid_)
• except WindowsError pass
• if __name__ '__main__'
• import win32com.server.register
• win32com.server.register.UseCommandLine(Addin)
  
• if '--unregister' in sys.argv
• UnregisterAddin(Addin)
• else RegisterAddin(Addin)

106
Getting a CLSID for your add-in

• import pywintypes
• clsid pywintypes.CreateGuid()
• class Addin(object)
• _reg_clsid_ '2F1E606F-2A7B-46F9-AF6A-267C00
  36C348'

107
Write an add-in for WordWrite an add-in for
Outlook

• Register your add-in in the Word subdirectory
• 'Software\\Microsoft\\Office\\Word\\Addins
• And similarly for Outlook
• 'Software\\Microsoft\\Office\\Outlook\\Addins'

108
Next thirty minutes More

• Speak using SpVoice
  4 min
• Call using Skype
  5 min
• Convert a document to PDF
  5 min
• Clip images from shapefiles through ArcGIS
  5 min
• Do calculations in Matlab
  5 min
• Extract data from an Access database
  3 min
• Move your mouse
  3 min

109
Speak using SpVoice

• Connect to SpVoice
• speech Dispatch('SAPI.SpVoice')
• Speak
• speech.Speak('hey')

110
Call using Skype

• skype Dispatch('Skype4COM.Skype')
• skype.Client.Start()
• skype.PlaceCall(18004664411') 1 800 GOOG 411
• speech Dispatch('SAPI.SpVoice')
• import time time.sleep(5)
• speech.Speak(Chicago Illinois)

111
Convert a document to PDF

• NOSAVE -1 PDSAVEFULL 1
• avdoc Dispatch('AcroExch.AVDoc')
• doc avdoc.Open(sourcePath, 'doc2pdf')
• pddoc avdoc.GetPDDoc()
• pddoc.Save(PDSAVEFULL, destinationPath)
• pddoc.Close() del pddoc
• avdoc.Close(NOSAVE) del avdoc
• Courtesy of Justin Ezequiel

112
Do calculations in Matlab

• Dispatch Matlab
• matlab Dispatch(Matlab.Application)
• Execute commands
• matlab.Execute(a 1 2 3 4 5 6 7 8)
• real, imag matlab.GetFullMatrix('a', 'base')
• Import numpy
• array(real)

113
Clip images from shapefiles using ArcGIS

• Dispatch ArcGIS
• gp Dispatch(EsriGeoprocessing.GpDispatch')
• rows gp.SearchCursor(path_points_shapefile)
• Prepare to clip
• import Image
• length_x_halved 10 length_y_halved 10
• def makeString_rectangle(x, y)
• return 's s s s' (x length_x_halved,
• y - length_y_halved, x length_x_halved,
• y length_y_halved)

114
Clip images from shapefiles using ArcGIS

• Get row
• row rows.next()
• Get coordinates
• point row.shape.GetPart() x, y point.X,
  point.Y
• Make rectangle
• string_rectangle makeString_rectangle(x, y)
• Clip image around point
• try gp.Clip_management(path_image,
  string_rectangle,
• path_temporary)
• except pywintypes.com_error print
  gp.GetMessages()
• Open image
• image Image.open(path_temporary)

115
Extract datafrom an Access database

• Use ADODB.Connection
• Refer to http//www.ecp.cc/

116
Move your mouse

• import random, win32api
• def jiggle()
• Get random motion
• choices (1,0), (-1,0), (0,1), (0,-1)
• motion random.choice(choices)
• Get cursor position
• oldPosition win32api.GetCursorPos()
• Set cursor position
• newX oldPosition0 motion0
• newY oldPosition1 motion1
• win32api.SetCursorPos((newX, newY))

117
Click (Simple)

• DOWN win32con.MOUSEEVENTF_LEFTDOWN
• UP win32con.MOUSEEVENTF_LEFTUP
• def click(x, y)
• win32api.SetCursorPos((x, y))
• win32api.mouse_event(DOWN,0,0,0,0)
• win32api.mouse_event(UP,0,0,0,0)

118
Type (Simple)

• def type(vk, scan)
• win32api.keybd_event(vk, scan, 0, 0)
• Keycodes
• http//msdn2.microsoft.com/en-us/library/ms64630
  4(VS.85).aspx

119
Click and Type (Complex)

• Courtesy of Daniel F
• http//mail.python.org/pipermail/python-win32/20
  05-April/003131.html
• from ctypes import
• PUL POINTER(c_ulong)
• class KeyBdInput(Structure) _fields_ ('wVk',
  c_ushort), ('wScan', c_ushort), ('dwFlags',
  c_ulong), ('time', c_ulong), ('dwExtraInfo',
  PUL)
• class HardwareInput(Structure) _fields_
  ('uMsg', c_ulong), ('wParamL', c_short),
  ('wParamH', c_ushort)
• class MouseInput(Structure) _fields_ ('dx',
  c_long), ('dy', c_long), ('mouseData', c_ulong),
  ('dwFlags', c_ulong), ('time',c_ulong),
  ('dwExtraInfo', PUL)
• class Input_I(Union) _fields_ ('ki',
  KeyBdInput), ('mi', MouseInput), ('hi',
  HardwareInput)
• class Input(Structure) _fields_ ('type',
  c_ulong), ('ii', Input_I)
• class POINT(Structure) _fields_ ('x',
  c_ulong), ('y', c_ulong)

120
Click and Type (Complex)

• def Click(x, y)
• orig POINT()
• windll.user32.GetCursorPos(byref(orig))
• windll.user32.SetCursorPos(x,y)
• FInputs Input 2
• extra c_ulong(0)
• ii_ Input_I()
• ii_.mi MouseInput( 0, 0, 0, 2, 0,
  pointer(extra) )
• ii2_ Input_I()
• ii2_.mi MouseInput( 0, 0, 0, 4, 0,
  pointer(extra) )
• x FInputs( ( 0, ii_ ), ( 0, ii2_ ) )
• windll.user32.SendInput(2, pointer(x),
  sizeof(x0))
• return orig.x, orig.y

121
Click and Type (Complex)

• def Type(wVk, wScan)
• FInputs Input 1
• extra c_ulong(0)
• ii_ Input_I()
• ii_.ki KeyBdInput( wVk, wScan, 0, 0,
  pointer(extra) )
• x FInputs( ( 1, ii_ ) )
• windll.user32.SendInput(1, pointer(x),
  sizeof(x0))
• return windll.kernel32.GetLastError()

122
Reference win32com

• Dispatch
• from win32com.client import Dispatch
• Dispatch with event handling
• from win32com.client import DispatchWithEvents
• Catch COM errors
• from pywintypes import com_error
• try pass
• except com_error pass
• PythonWin gt Tools gt Trace Collector Debugging
  Tool
• import win32traceutil print hey

123
Reference Microsoft Excel

• Connect
• excelApplication win32com.client.Dispatch('Excel
  .Application')
• Make it visible
• excelApplication.Visible 1
• Adjust the application window
• excelApplication.WindowState win32com.client.con
  stants.xlNormal
• excelApplication.WindowState win32com.client.con
  stants.xlMinimized
• excelApplication.WindowState win32com.client.con
  stants.xlMaximized
• Move the application window
• excelApplication.Application.Top 50
• excelApplication.Application.Left 50
• Resize the application window
• excelApplication.Application.Width 500
• excelApplication.Application.Height 500
• Set the number of sheets in a new workbook
• excelApplication.SheetsInNewWorkbook 1
• Add a workbook
• excelApplication.Workbooks.Add()

124
Reference Microsoft Excel

• Write a value to a cell
• row 1 column 2 text 'Welcome'
• cell excelApplication.ActiveSheet.Cells(row,
  column)
• cell.Value text cell.Font.Size size
  cell.Font.Bold isBold
• Read a value from a cell
• print cell.Value
• Add a worksheet after the current worksheet
• excelApplication.Worksheets.Add(After
  excelApplication.ActiveSheet)
• Choosing rows and columns
• rowObject excelApplication.ActiveSheet.Rows(1)
• columnObject excelApplication.ActiveSheet.Column
  s('A')
• Merge cells in a range
• excelApplication.ActiveSheet.Range(A1B3).Merge(
  )
• Format dates
• cell.NumberFormat 'm/d/yyyy'
• Wrap text
• rowObject.WrapText True
• Autofit cells
• rowObject.AutoFit()

125
Reference Microsoft Excel

• Align cells
• cell.VerticalAlignment win32com.client.constants
  .xlVAlignTop
• cell.HorizontalAlignment win32com.client.constan
  ts.xlRight
• Change page orientation
• sheet excelApplication.ActiveSheet
• sheet.PageSetup.Orientation xlLandscape
• sheet.PageSetup.PaperSize xlPaperLegal
• Change margins
• margin excelApplication.InchesToPoints(0.5)
• sheet.PageSetup.TopMargin margin
• Change headers
• sheet.PageSetup.RightHeader 'A\nD\nT\nPage
  P/N'
• Hide columns
• hiddenColumns 'A', 'D'
• for hiddenColumn in hiddenColumns
  columns(hiddenColumn).Hidden True

126
Reference Lotus Notes

• Import other modules
• from win32com.client import Dispatch
• import pywintypes
• Interface for Lotus Notes
• class Store(object)
• Constructor
• def __init__(self, databaseHost,
  databaseName, databasePassword)
• Connect
• self.notesSession Dispatch('Lotus.NotesS
  ession')
• try
• self.notesSession.Initialize(databaseP
  assword)
• self.notesDatabase
  self.notesSession.GetDatabase(databaseHost,
  databaseName)
• self.inbox self.notesDatabase.GetVie
  w('(Inbox)')
• except pywintypes.com_error raise
  ConnectionError
• Set constants
• self.ENC_NONE win32com.client.constants.
  ENC_NONE

127
Reference Lotus Notes

• Write
• def write(self, toWhom, subject, body,
  attachments )
• Don't convert MIME to rich text
• self.notesSession.ConvertMime False
• Prepare message
• message self.notesDatabase.CreateDocumen
  t()
• message.ReplaceItemValue('SendTo',
  toWhom)
• message.ReplaceItemValue('Subject',
  subject)
• stream self.notesSession.CreateStream()
• stream.WriteText(body)
• message_body message.CreateMIMEEntity()
• message_body.SetContentFromText(stream,
  'text/html charsetutf-8', self.ENC_NONE)
• Add attachments
• for index in xrange(len(attachments))
• attachment attachmentsindex
• if not attachment continue
• message_attachment
  message.CreateRichTextItem('attachmentd'
  index)
• message_attachment.EmbedObject(self.EM
  BED_ATTACHMENT, '', attachment, None)

128
Reference Lotus Notes

• Move
• def move(self, destinationFolderName,
  document)
• document.PutInFolder(destinationFolderName
  )
• document.RemoveFromFolder('(Inbox)')
• Queue
• def clearQueues(self)
• self.writes self.moves
• def queueWrite(self, parcel)
• self.writes.append(parcel)
• def queueMove(self, parcel)
• self.moves.append(parcel)
• def processQueues(self)
• for parcel in self.writes
  self.write(parcel)

129
Reference Microsoft Outlook

• Connect to Microsoft Outlook using Dispatch
• from win32com.client import Dispatch, constants
• session win32com.client.Dispatch('Outlook.Applic
  ation')
• namespace session.GetNamespace('MAPI')
• namespace.Logon()
• Browse folders
• rootFolder namespace.Folders1.Folders
• count rootFolder.Count
• for whichFolder in xrange(1, count 1)
• folder rootFolderwhichFolder
• print folder.Name
• Read mail
• folder namespace.GetDefaultFolder(constants.olFo
  lderInbox)
• messages folder.Items
• message messagesmessageIndex
• print message.Subject

130
Reference Microsoft Outlook

• def write(to, subj, body, attachments )
• x constants.olMailItem
• m outlook.CreateItem(x)
• m.To to m.Subject subj m.Body body
• for attachment in attachments
• p attachment, constants.olByValue, 1
• m.Attachments.Add(p)
• message.Send
• def move(destinationFolderName, message)
• message.Move(destinationFolderName)

131
Reference Databases and dates

• import pyodbc
• When inserting records, SQL server
• only accepts dates in a certain format
• now datetime.date.today().strftime('m/d/Y')
• import sqlite3
• self.connection sqlite3.connect(databasePath,
  detect_types sqlite3.PARSE_DECLTYPES)
• sql 'CREATE TABLE invoices (invoiceNumber
  INTEGER PRIMARY KEY, invoiceDate DATE, amount
  REAL, description TEXT, isPaid INTEGER)'

132
Acknowledgments

• Python!
• Mark Hammond for developing win32com
• Tutorial organizer Greg Lindstrom
• Eric Koome for code on the Excel add-in
• Python Conference 2008 organizers
• All Python developers for being so helpful and
  willing to share what they have learned

133
Contact

• See if someone has already solved it.
• http//python.org/about/help
• You can also ask me!
• Roy H. Han
• starsareblueandfaraway at gmail.com
• [phone]