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;SE RVER=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,
--
Ben.