Driving Excel from Python in Windows

Driving Excel from Python in Windows

For controlling Excel, use pywin32, like @igowen suggests.

Note that it is possible to use static dispatch. Use makepy.py from the pywin32 project to create a python module with the python wrappers. Using the generated wrappers simplifies development, since for instance ipython gives you tab completion and help during development.

Static dispatch example:

x:> makepy.py "Microsoft Excel 11.0 Object Library"
...
Generating...
Importing module
x:> ipython

> from win32com.client import Dispatch
> excel = Dispatch("Excel.Application")
> wb = excel.Workbooks.Append()
> range = wb.Sheets[0].Range("A1")
> range.[Press Tab]
range.Activate                 range.Merge
range.AddComment               range.NavigateArrow
range.AdvancedFilter           range.NoteText
...
range.GetOffset                range.__repr__
range.GetResize                range.__setattr__
range.GetValue                 range.__str__
range.Get_Default              range.__unicode__
range.GoalSeek                 range._get_good_object_
range.Group                    range._get_good_single_object_
range.Insert                   range._oleobj_
range.InsertIndent             range._prop_map_get_
range.Item                     range._prop_map_put_
range.Justify                  range.coclass_clsid
range.ListNames                range.__class__
> range.Value = 32
...

Documentation links:

• The O'Reilly book Python Programming on Win32 has an Integrating with Excel chapter.
• Same book, free sample chapter Advanced Python and COM covers makepy in detail.
• Tutorials
• win32com documentation, I suggest you read this first.

Python 3.0 Import Excel file to Access File

Consider pure SQL as the JET/ACE engine allows querying external workbooks and databases directly. You can do so from either an MS Access connection or Excel connection since same underlying engine is used. No need for row by row cursor append.

Below assumes ALL Excel worksheet columns matches one-to-one with ALL Access table columns in same order. If you use an autonumber field in Access table, consider explicitly stating columns in INSERT INTO and SELECT clauses of append query.

Access Connection (local DB table append)

DBFile = r'C:\Users\nyeung\Documents\wsp.codingworkshop.python\ClassNotebooks\ETABS.accdb'
exFile = r'C:\Users\nyeung\Documents\wsp.codingworkshop.python\ClassNotebooks\ETABS.xlsx'

conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ='+DBFile)

# APPEND TO LOCAL TABLE
sql = """INSERT INTO ETABS
         SELECT * FROM [Excel 12.0 Xml; HDR = Yes;Database={myfile}].[Sheet1$];
      """

curs = conn1.cursor()
curs.execute(sql.format(myfile = exFile))
conn.commit()

By the way, you can even run a make-table query instead of appending to an existing table:

SELECT * INTO myNewTable FROM [Excel 12.0 Xml; HDR=Yes; Database={myfile}].[Sheet1$]

---

Excel Connection (external DB table append)

DBFile = r'C:\Users\nyeung\Documents\wsp.codingworkshop.python\ClassNotebooks\ETABS.accdb'
exFile = r'C:\Users\nyeung\Documents\wsp.codingworkshop.python\ClassNotebooks\ETABS.xlsx'

conn = pyodbc.connect('DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ='+exFile)
curs1 = conn1.cursor()

# APPEND TO EXTERNAL TABLE
sql = """INSERT INTO [{myfile}].[ETABS]
         SELECT * FROM [Sheet1$];
      """

curs = conn.cursor()
curs.execute(sql.format(myfile = DBFile))
conn.commit()

fail to make pyodbc-4.0 64bit find excel driver (windows10-64bit)

it works if I use pyodbc4- 32 bit and python-3.6 32 bit... so how to make it work in 64 bit ?

If the script is running under 64-bit Python then it needs to have the 64-bit version of the Excel ODBC driver installed.

There is an old office 2007 32 bit on this pc

It is possible to do a "forced install" of the 64-bit Access Database Engine (which includes the Access and Excel ODBC drivers) alongside a 32-bit version of Office, but that is not recommended because in some circumstances it can break the Office install. (It is called a "forced install" because the default behaviour of the Access Database Engine installer is to prevent it from happening.)

If the "old office 2007" is no longer being used then you can uninstall it and then install the 64-bit version of the Access Database Engine. If it is still being used then you probably should just stick with running the Python script as 32-bit.

---

Related Topics