he Python for Excel permits in-process control of a workbook (for example,
MyTest.xls) coded in a Python script (MyTest.pfe) placed into the same
directory.
The "pfe" file has to have the same name as the "xls" file. If the MyTest.xls
is loaded into Excel then the MyTest.pfe would be executed in-process. In the
pfe script the programmer may use the predefined ExcelListener and Application
objects and the vba module to trap Excel events, access Excel object model and
thus give any desired functionality to the MyTest.xls. In effect, the pair
"MyTest.xls" and "MyTest.pfe" becomes an application with Excel front end.
Consider the following example code for the MyTest.pfe file.
def osscHandler(sht,rng) :
global
osscCounter
try
:
osscCounter
except
:
osscCounter=0
osscCounter+=1
rng.Value="("+sht.Name+","+rng.Address+")
counter="+str(osscCounter)
ExcelListener.OnSheetSelectionChange.bind(osscHandler)
Such code would put spreadsheet's name and current cell's address into the
current cell every time the user changes the current cell (selection).
All non-global variables defined in the pfe script disappear after completion
of the pfe script. This has to be so because the pfe script is executed in a
"workbook open"-event handler. Therefore, the "global" keyword and optional
arguments have to be used generously. Declare function names global if you
want to keep them or use them on higher stack levels in the pfe-script. Global
variables remain. The data structures referenced by global variables remain.
Everything else vanishes.
The are may be few questions about the above code, such as "How does one
explore and experiment with the object model to arrive to the solution?" or
"What happens in case of an error and/or exception?" or "How does one debug
the code?" These questions are answered in this tutorial. We also cover
facilities for calling Python from VBA and for in-cell execution of Python.
|
