Python – Problem using Python comtypes library to add a querytable to Excel

comcomtypesexcelpythonvba

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!

Best Solution

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 :)