Using WSH to populate a pivot cache with an ADO recordset
 

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.

Using WSH to populate a pivot cache with an ADO recordset
 

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=ServerNam e"
.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 :

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,

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=ServerNam e"
.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 :

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,

Using WSH to populate a pivot cache with an ADO recordset
 

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 :

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=ServerNam e"
.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 :

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,

Using WSH to populate a pivot cache with an ADO recordset
 

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=passwo rd;"

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

"keepITcool" wrote:


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 :

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=ServerNam e"
.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 :

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,