<font size="2">
<p>Hi,</p>
<p>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):
</p>
<div>Python 2.4.1 (#65, Mar 30 2005, 09:13:57) [MSC v.1310 32 bit (Intel)] on win32</div>
<div>Type "help", "copyright", "credits" or "license" for more information.</div>
<div>>>> from win32com.client import constants, Dispatch</div>
<div>>>> xl = Dispatch('Excel.Application')</div>
<div>>>> xl.Workbooks.Add()</div>
<div><win32com.gen_py.None.Workbook></div>
<div>>>> xl.ActiveWorkbook.SaveAs(r'd:\pivottable.xls')</div>
<div>>>> pc = xl.ActiveWorkbook.PivotCaches().Add(constants.xlExternal)</div>
<div>>>> pc</div>
<div><win32com.gen_py.Microsoft Excel 11.0 Object Library.PivotCache instance at 0x29486656></div>
<div>>>> conn = Dispatch('ADODB.Connection')</div>
<div>>>> rs = Dispatch('ADODB.Recordset')</div>
<div>>>> ado = r'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\test.mdb;'</div>
<div>>>> sql = 'SELECT * FROM whatever'</div>
<div>>>> conn.Open(ado)</div>
<div>>>> rs.Open(sql,conn)</div>
<div>>>> pc.Recordset = rs</div>
<div>Traceback (most recent call last):</div>
<div>File "<stdin>", line 1, in ?</div>
<div>File "C:\Python24\lib\site-packages\win32com\client\__init__.py", line 463, in __setattr__</div>
<div>self._oleobj_.Invoke(*(args + (value,) + defArgs))</div>
<div>pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None) </div>
<p>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…
</p>
<p>Thanks in advance</p>
<p>peter</p></font>