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


"Dave Peterson" wrote:

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

--

Dave Peterson