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

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

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

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

"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


--

Dave Peterson

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

"Dave Peterson" wrote:

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

"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


--

Dave Peterson

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

"Dave Peterson" wrote:

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

"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


--

Dave Peterson


--

Dave Peterson