I looked at VBA's help and saw this for .add for .pivottables:
Adds a new PivotTable report. Returns a PivotTable object.
expression.Add(PivotCache, TableDestination, TableName, ReadData,
DefaultVersion)
expression Required. An expression that returns a PivotTables object.
PivotCache Required PivotCache. The PivotTable cache on which the new PivotTable
report is based. The cache provides data for the report.
TableDestination Required Variant. The cell in the upper-left corner of the
PivotTable report's destination range (the range on the worksheet where the
resulting report will be placed). You must specify a destination range on the
worksheet that contains the PivotTables object specified by expression .
TableName Optional Variant. The name of the new PivotTable report.
ReadData Optional Variant. True to create a PivotTable cache that contains all
records from the external database; this cache can be very large. False to
enable setting some of the fields as server-based page fields before the data is
actually read.
So this line has a few problems:
Set xlptTable _
= xlsheet.PivotTable.Add(xlptCache, xlSheet.Range("A1"), "TabXdyn" , 1)
First, it should be .pivottables.add (plural, not singular)
xlptcache has to be a specific pivotcache--not all the pivotcaches
maybe replace this:
Set xlptCache = xlBook.PivotCaches
with:
Set xlptCache = xlBook.PivotCaches(1) 'or some name???
I'd suggest that you re-record that macro when you're building the pivottable.
Hi Dave,
the problem is not a SQL problem. It is typically an convertion VBA to VBS
problem and it is not easy to to that kind of thing when you want to use hard
function. I have read the offical MS documentation and the CreatePivotTable
method say that it has three parameters. But I don't really understand how to
pass these parameters to the function when it is coded in this kind of
structure of coding. I can't test this code from home, I need to wait until
tomorrow, but I will try this new writing:
.CreatePivotTable(xlsheet.range("A1"),"TabXdyn",1)
If you have a better idea...
Eric
:
Well, it's good to post the current version of your code after you've made
changes, but I don't use SQL enough to help.
ps. Do make sure you replace all the excel constants with their equivalent
number.
Speedking78 wrote:
Hi Dave,
I give you my code:
' Define the variable
Option Explicit
Dim WhichGraph, WhichTime, WhichLpar
Dim fso, xlApp, xlBook, xlSheet, xlChart, xlptCache, xlptTable
Dim Db2Id, Db2Prefix, UserId, Password, SqlToExecute
' Open Excel
Set xlApp = WScript.CreateObject("Excel.Application")
xlApp.Visible = True
' Create a workbook
set xlBook = xlApp.Workbooks.Add
' Create a sheet called "Data" in this workbook
With xlBook
set xlsheet = .sheets.add
xlsheet.name = "Data"
End With
' Create a Pivottable from a DB2 request
Set xlptCache = xlBook.PivotCaches
With xlptCache.Add(2)
.Connection = "ODBC;DSN=" & Db2Id & ";UID=" & UserId & ";PWD=" & Password
& ";MODE=SHARE;DBALIAS=" & Db2Id & ";"
.CommandType = 2
.CommandText = SQLToExecute
.CreatePivotTable "'Data'!R1C1" , "TabXdyn", 1 <== Error 800A0005
(incorrect calling ???)
End With
' Fill the PivotTable with the result of the DB2 request
Set xlptTable = xlsheet.PivotTable.Add(xlptCache, xlSheet.Range("A1"),
"TabXdyn" , 1)
With xlptTable.AddFields
.RowFields= RowFieldsToShow
.ColumnFields=ColumnFieldsToShow
With .PivotFields(DataToShow)
.Orientation = 4
.NumberFormat = "0.00"
End With
End With
I run this script on Windows XP2 and MS Office Excel 2003.
Many thanks
Eric
:
I'd try to record a macro while in excel and compare that with your .VBS code.
If you're having trouble, try posting the recorded code and the version of the
..VBS code that you tried.
And try to be more specific with the error you get.
Speedking78 wrote:
Hi Dave,
Many many thanks. I'll never find what you suggest to me. Now , I don't have
anymore syntax error. But my problem still continue now with the line
.CreatePivotTable TableDestination:="'Data'!R1C1", TableName:="TabXdyn",
DefaultVersion:=xlPivotTableVersion10. I have looked in Excel VBE and found
the syntax of CreatePivotTable function, that is:
Function CreatePivotTable(TableDestination, [TableName], [ReadData],
DefaultVersion]) As PivotTable
I have tried many syntax( I mean with (), without (), and so on..), each
time a get an error. My last test is:
.CreatePivotTable "'Data'!R1C1", "TabXdyn", 1
but no way. Have you a solution for a desperate home men ???
Thank. Eric
:
I don't use VBS very often, but I didn't think you could use named parms in VBS.
I'd try:
With xlBook.PivotCaches.Add(2)
SourceType is the first parm in that command (check VBA's help)
And VBS won't know what value xlExternal is.
I opened excel, went to the VBE, showed the immediate window (ctrl-g) and then
typed:
?xlExternal
to get the 2.
And I think you'll need some more, too:
?xlexternal
2
?xlCmdSql
2
?xlPivotTableVersion10
1
?xlDataField
4
Speedking78 wrote:
Hi,
I need to translate VBA code in an Excel Macro to an VBScript code. Every
thing is alright except when I try to make a call to the function
PivotCaches.Add(SourceType:=xlExternal). At this step I get an 800A03EE with
the ":". Why ??? I have read that some Excel variable are not visible from a
VBScript. AM I in this case ? I give my code:
Dim xlApp, xlBook, xlSheet, xlChart, xlptCache, xlptTable
Dim Db2Id, Db2Prefix, UserId, Password, SqlToExecute
Dim RowFieldsToShow, ColumnFieldsToShow, DataToShow
Set xlApp = WScript.CreateObject("Excel.Application")
xlApp.Visible = True
set xlBook = xlApp.Workbooks.Add
With xlBook
set xlsheet = .sheets.add
xlsheet.name = "Data"
End With
With xlBook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = "ODBC;DSN=" & Db2Id & ";UID=" & UserId & ";PWD=" &
Password & ";MODE=SHARE;DBALIAS=" & Db2Id & ";"
.CommandType = xlCmdSql
.CommandText = SQLToExecute
.CreatePivotTable TableDestination:="'Data'!R1C1",
TableName:="TabXdyn", DefaultVersion:=xlPivotTableVersion10
.Refresh
End With
With xlptTable.AddFields
.RowFields= RowFieldsToShow
.ColumnFields=ColumnFieldsToShow
With .PivotFields(DataToShow)
.Orientation = xlDataField
.NumberFormat = "0.00"
End With
End With
I have tried many things but all wrong. If someone has the way to the
paradise...
Speedking78
