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

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

"Dave Peterson" wrote:

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

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.

Speedking78 wrote:

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

"Dave Peterson" wrote:

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


--

Dave Peterson

Hi Dave,

I have found the solution of my problem and I give you it (an also for all
others):

SqlToExecute = "SELECT Field1, Field2,..., Fieldn FROM Table WHERE ... GROUP
BY... ;"

Set xlApp = WScript.CreateObject("Excel.Application")
xlApp.Visible = True
set xlBook = xlApp.Workbooks.Add

set xlsheet = xlBook.sheets.add

Set xlPvtCache = xlBook.PivotCaches.Add(xlExternal)
With xlPvtCache
.Connection = "ODBC;DSN=" & Db2Id & ";UID=" & UserId & ";PWD=" & Password &
";MODE=SHARE;DBALIAS=" & Db2Id & ";"
.CommandType = xlCmdSql
.CommandText = SqlToExecute
End With

Set xlPvtTable = xlPvtCache.CreatePivotTable(xlsheet.range("A1"),"T abXdyn",1)

' Maybe you need to force the name of the returned fields of sthe SQL
request by this kind of code:
' xlPvtTable.PivotFields(1).name = "SqlField1" = RowFieldsToShow
' xlPvtTable.PivotFields(2).name = "SqlField2" = ColumnFieldsToShow
' xlPvtTable.PivotFields(3).name = "SqlField5" = DataToShow

xlPvtTable.PivotFields(RowFieldsToShow).Orientatio n = xlRowField
xlPvtTable.PivotFields(ColumnFieldsToShow).Orienta tion = xlColumnField
With xlPvtTable.PivotFields(DataToShow)
.Name = DataToShow
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "0.00"
End With

set xlChart = xlbook.Charts.Add
With xlChart
.SetSourceData xlsheet.Range("A1")
.Location xlLocationAsNewSheet
End With

Thanks for your help.

Eric

"Dave Peterson" wrote:

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.

Speedking78 wrote:

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

"Dave Peterson" wrote:

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


--

Dave Peterson

Thanks for posting the solution.

Speedking78 wrote:

Hi Dave,

I have found the solution of my problem and I give you it (an also for all
others):

SqlToExecute = "SELECT Field1, Field2,..., Fieldn FROM Table WHERE ... GROUP
BY... ;"

Set xlApp = WScript.CreateObject("Excel.Application")
xlApp.Visible = True
set xlBook = xlApp.Workbooks.Add

set xlsheet = xlBook.sheets.add

Set xlPvtCache = xlBook.PivotCaches.Add(xlExternal)
With xlPvtCache
.Connection = "ODBC;DSN=" & Db2Id & ";UID=" & UserId & ";PWD=" & Password &
";MODE=SHARE;DBALIAS=" & Db2Id & ";"
.CommandType = xlCmdSql
.CommandText = SqlToExecute
End With

Set xlPvtTable = xlPvtCache.CreatePivotTable(xlsheet.range("A1"),"T abXdyn",1)

' Maybe you need to force the name of the returned fields of sthe SQL
request by this kind of code:
' xlPvtTable.PivotFields(1).name = "SqlField1" = RowFieldsToShow
' xlPvtTable.PivotFields(2).name = "SqlField2" = ColumnFieldsToShow
' xlPvtTable.PivotFields(3).name = "SqlField5" = DataToShow

xlPvtTable.PivotFields(RowFieldsToShow).Orientatio n = xlRowField
xlPvtTable.PivotFields(ColumnFieldsToShow).Orienta tion = xlColumnField
With xlPvtTable.PivotFields(DataToShow)
.Name = DataToShow
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "0.00"
End With

set xlChart = xlbook.Charts.Add
With xlChart
.SetSourceData xlsheet.Range("A1")
.Location xlLocationAsNewSheet
End With

Thanks for your help.

Eric

"Dave Peterson" wrote:

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.

Speedking78 wrote:

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

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson