Re: Using WSH to populate a pivot cache with an ADO recordset

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" wrote:


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.Active***.Range("A3"), "ReportOutput"
End With







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


Ben-host wrote :

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.Active***.Range("A3"),
"ReportOutput"

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

Many thanks,

.