使用Python comtypes库向Excel添加查询表的问题

Question

我正在尝试使用Python的comtypes库在Excel电子表格中创建一个查询表，但遇到了一个不太好理解的错误...

在VBA（工作簿中的一个模块）中，以下代码运行得很好：

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

但是以下的Python代码：

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

却出现了一个不太有用的错误信息：

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

有没有人知道这是怎么回事？

谢谢！

Answer 1

看起来你的错误出现在这一行：

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

我觉得你的问题是，你在用Python的语法来查找VBA集合中的值。试着把方括号换成圆括号。

也就是说：

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

原因是，在VBA中，当你这样调用一个集合，比如 Range("A1")，实际上是在调用它的默认方法 Range.Item("A1")。简单来说，VBA的集合和Python的字典是不一样的。

这些信息我是在这个论坛帖子上看到的，还有我自己对VBA的经验。

---

根据评论的编辑：

不幸的是，我都试过了：正如你链接中提到的，它们有时并不做同样的事情，但我直觉觉得 '[' 更可能是我想要的。 – mavnn

你知道 comtypes.client.CreateObject 和 win32com.client.Dispatch 是否是一样的吗？你可以试着用win32com包来创建你的com对象，看看这样是否有区别。

Answer 2

我简化了你的代码，这样应该可以正常工作（下面我会解释做了哪些修改）：

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

QueryTables.Add()这个函数可以帮你创建连接和记录集对象，所以这简化了很多事情……你只需要在连接字符串中添加连接的类型（就是“OLEDB”那部分）。

让Excel来处理大部分工作似乎解决了你的问题 :)