Using WSH to populate a pivot cache with an ADO recordset

[Ben-host]

I'm trying to use windows scripting host to create an instance of excel and
then grab an ADO recordset and use it to populate a pivotcache for use in a
pivot table (It's part of a batch report generator)

I can create the instance of Excel OK, and get the ADO recordset. but for
some reason, I get an Unknown error whe I try to assign the recordset to the
pivotcache object. The same code works fine when it's re-written to be
executed as part of an Excel VBA module, so it must be due in some way to
using WSH with these objects. Can anyone shed any light on it for me? What am
I doing wrong???

The WSH code is as follows:

Dim ObjExcel, DBConnection, rstData, SQL, objPivotCache

Set objExcel = CreateObject("Excel.Application")
Set DBConnection = CreateObject("ADODB.Connection")
Set rstData = CreateObject("ADODB.Recordset")

'Make the instance of Excel visible and add a new workbook
objExcel.visible = true
objExcel.Workbooks.Add

'Open the ADO Database connection
DBConnection.ConnectionString = "DRIVER={SQL
Server};UID=UserID;pwd=Password;DATABASE=DBName;SERVER=ServerName"
DBConnection.Open

'Open the recordset
SQL = "SELECT * FROM GeneralTables.dbo.tbl_CallType"
rstData.open SQL, DBConnection, 1, 2

'Create a pivot cache and populate with recordset
Set objPivotCache = objExcel.ActiveWorkbook.PivotCaches.Add(2)
'********** This next line is the one causing the "Unknown Runtime Error"
Set objPivotCache.Recordset = rstData

'Create pivot table
objPivotCache.CreatePivotTable objExcel.ActiveSheet.Range("A3"),
"ReportOutput"

I'm using Excel 2000 on W2K pro sp4 and WSH 5.6

Many thanks,

 

[keepITcool]

probably easier to use the Pivotcache's
builtin connection to retrieve the recordset

i turned the recorder on and edited a bit:
untested as i havent got a sql server up & running

With objExcel.PivotCaches.Add(2) 'xlExternal
.Connection = "DRIVER={SQL Server};UID=UserID;" & _
"pwd=Password;DATABASE=DBName;SERVER=ServerName"
.CommandType = 2 'xlCmdSql
.CommandText = Array("SELECT * FROM GeneralTables.dbo.tbl_CallType")
.CreatePivotTable objExcel.ActiveSheet.Range("A3"), "ReportOutput"
End With







--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ben-host wrote :

 

[Ben-host]

Hi! Thanks for the reply. I've tried this method and it works OK from WSH.

However, in this case, I still need to be able to populate the pivot cache
using an ADO recordset, because I've run previous statements on the ADO data
connection to create temporary tables which are then used in the query (the
temporary tables wouldn't exist if I open another connection for the
Pivotcache's built in data access functionality).

I'm still not sure why I'm having problems with the ADO version - since it
works fine using VBA! Any more ideas?

 

[keepITcool]

hmm.. have you tried to set the builtin connection
to you adodb connection...?

set pivotcache.connection = objConn


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ben-host wrote :

 

[Ben-host]

Thanks again for the reply. No - this doesn't work - I get an error from the
pivotcache object - I think the connection property of the pivotcache only
accepts a connection string - not an ADO Connection object.

Incidentally - if anyone else reads this - the pivotcache connection string
in this example wont work for some reason - you need to use an ODBC type
connection string in the format:

"ODBC;DSN=DatabaseName;UID=Username;PWD=password;"

I'm still struggling to understand what the problem is!!! Any more ideas
would be gratefully received!