[python-win32] Creating pivot tables in excel using python

[Peter Schütz]

Hi,

I am using Python 2.4.1 and I want to retrieve data from an Access database
and display the results in an Excel pivot table. To get the data out of
Access is no problem, but I have problems creating the pivot table in Excel.
The following code is what I came up with (after running makepy on the Excel
library):
Python 2.4.1 (#65, Mar 30 2005, 09:13:57) [MSC v.1310 32 bit (Intel)] on
win32
Type "help", "copyright", "credits" or "license" for more information.
>>> from win32com.client import constants, Dispatch
>>> xl = Dispatch('Excel.Application')
>>> xl.Workbooks.Add()
<win32com.gen_py.None.Workbook>
>>> xl.ActiveWorkbook.SaveAs(r'd:\pivottable.xls')
>>> pc = xl.ActiveWorkbook.PivotCaches().Add(constants.xlExternal)
>>> pc
<win32com.gen_py.Microsoft Excel 11.0 Object Library.PivotCache instance at
0x29486656>
>>> conn = Dispatch('ADODB.Connection')
>>> rs = Dispatch('ADODB.Recordset')
>>> ado = r'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\test.mdb;'
>>> sql = 'SELECT * FROM whatever'
>>> conn.Open(ado)
>>> rs.Open(sql,conn)
>>> pc.Recordset = rs
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "C:\Python24\lib\site-packages\win32com\client\__init__.py", line 463,
in __setattr__
self._oleobj_.Invoke(*(args + (value,) + defArgs))
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None,
None, 0, -2146827284), None)

As you can see, I get the PivotCache object, but I need to set the Recordset
in order to use rs. I get the same error message when want to set
pc.OptimizeCache to True. Honestly, I have no idea what is going on, so if
someone could help me…

Thanks in advance

peter
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.python.org/pipermail/python-win32/attachments/20051117/a1f0d636/attachment.html