Problem using Python comtypes library to add a querytable to Excel

Question

I'm trying to create a QueryTable in an excel spreadsheet using the Python comtypes library, but getting a rather uninformative error...

In vba (in a module within the workbook), the following code works fine:

Sub CreateQuery()
    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim ws As Worksheet
    Dim qt As QueryTable

    Set ws = ActiveWorkbook.Sheets(1)

    Set con = New ADODB.Connection
    con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\to\Db.mdb;")

    Set rs = New ADODB.Recordset
    rs.Open "Select * from [tbl Base Data];", con

    Set qt = ws.QueryTables.Add(rs, ws.Range("A1"))
    qt.Refresh
End Sub

But the following Python code:

import sys
import comtypes.client as client

def create_querytable():
    constring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Path\\to\\Db.mdb"
    conn = client.CreateObject("ADODB.Connection", dynamic = True)
    rs = client.CreateObject("ADODB.Recordset", dynamic = True)

    SQL = "Select * from [tbl Base Data];"

    conn.Open(constring)
    rs.Open(SQL, conn)
    excel = client.CreateObject("Excel.Application", dynamic = True)
    excel.Visible = True
    ws = excel.Workbooks.Add().Sheets(1)
    qt = ws.QueryTables.Add(rs, ws.Range["A1"])
    qt.Refresh()
    rs.Close()
    conn.Close()

Throws the unhelpful error message:

Traceback (most recent call last):
  File "<pyshell#34>", line 1, in <module>
    create_querytable()
  File "C:/Documents and Settings/cvmne250/Desktop/temp.py", line 17, in create_querytable
    qt = ws.QueryTables.Add(rs, ws.Range["A1"])
  File "G:\ISA\SPSS\comtypes\lib\comtypes\client\lazybind.py", line 160, in caller
  File "G:\ISA\SPSS\comtypes\lib\comtypes\automation.py", line 628, in _invoke
COMError: (-2147352567, 'Exception occurred.', (None, None, None, 0, None))

Any ideas on what's happening here?

Thanks!

Answer 1

It looks like your error is on this line:

qt = ws.QueryTables.Add(rs, ws.Range["A1"])

I think your problem is that you are using python syntax to look up a value in a VBA Collection. Try changing your square brackets to parentheses.

i.e.

qt = ws.QueryTables.Add(rs, ws.Range("A1"))

The reason being that in VBA when you invoke a Collection like this, Range("A1"), you are actually calling it's default method, Range.Item("A1"). Basically, VBA Collections do not translate to python dictionaries.

I'm getting this from this forum thread, and my experience with VBA.

---

Edit due to comment:

Unfortunately, I've tried both: as noted in your link, they sometimes don't do the same thing, but my gut feeling here is that the '[' is more likely to be what I want. – mavnn

Do you know if comtypes.client.CreateObject works the same as win32com.client.Dispatch? You might try creating your com object with the win32com package and see if that makes a difference.

Answer 2

I simplified your code and this should work fine (I'll explain the changes below):

def create_querytable2():
    constring = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\to\db.mdb;"
    SQL = "Select * from tblName;"
    excel = client.CreateObject("Excel.Application", dynamic=True)
    excel.Visible = True
    ws = excel.Workbooks.Add().Worksheets(1)
    ws.QueryTables.Add(constring, ws.Range["A1"], SQL).Refresh()

The QueryTables.Add() function can create the Connection and Recordset objects for you, so that simplifies a lot of things... you just need to add what type of connection it is in the conneciton string (the "OLEDB" part).

Letting Excel do most of the work seems to solve your problem :)