I'm trying to create a pivot table in Excel from a vbscript file. The data in the table will come from a database. I have a macro in Excel I created that works perfectly, but there's one part of the code that does not work in vbscript, and it's really got me frustrated.
When I try to assign the recordset to the PivotCache object, that's where I'm getting an error ("Unknown runtime error"
. Here's the code:
....
Set xlApp = CreateObject("Excel.Application"
Set xlWkb = xlApp.Workbooks.Add
xlApp.Visible = True
Set conn = CreateObject("ADODB.Connection"
Set rs = CreateObject("ADODB.Recordset"
conn.Open "my connection string"
rs.Open "SELECT * FROM table", conn
Set pc = xlWkb.PivotCaches.Add(2) 'xlExternal
Set pc.Recordset = rs
'This is where I'm getting the error. Again, I'm having no problems when running this code from Excel. The database connection and query is fine.
Please offer any suggestions, thank you.
When I try to assign the recordset to the PivotCache object, that's where I'm getting an error ("Unknown runtime error"
. Here's the code:....
Set xlApp = CreateObject("Excel.Application"
Set xlWkb = xlApp.Workbooks.Add
xlApp.Visible = True
Set conn = CreateObject("ADODB.Connection"
Set rs = CreateObject("ADODB.Recordset"
conn.Open "my connection string"
rs.Open "SELECT * FROM table", conn
Set pc = xlWkb.PivotCaches.Add(2) 'xlExternal
Set pc.Recordset = rs
'This is where I'm getting the error. Again, I'm having no problems when running this code from Excel. The database connection and query is fine.
Please offer any suggestions, thank you.
