Issue with Macro While running in Excel 2003

[Alam, Shamshad]

Hi Team,

I have a problems running with Macro.
I have created macro using module( module1.bas) added to the Excel visual
Basic Editor to access the mailboxes of Exchange Servers from the extracted
notepad file.

The macro file was created in Excel 2007 but it does not work with Excel 2003.

Macro commands work as to create Piovot table using mailboxes based on
servers.

When I run the Macro file in Excel 2003, it gives an error as Run time error
"438"


When I debug the program it catches the errors on this below line coded-

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=
_"Sheet1!R1C1:R28448C7", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet3!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10


Please help me to resolve this issue using Excel 2003.

Thanks in Advance!
Shamshad
HCL

 

[David Heaton]

Hi Team,

I have a problems running with Macro.
I have created macro using module( module1.bas) added to the Excel visual
Basic Editor to access the mailboxes of Exchange Servers from the extracted
notepad file.

The macro file was created in Excel 2007 but it does not work with Excel 2003.

Macro commands work as to create Piovot table using mailboxes based on
servers.

When I run the Macro file in Excel 2003, it gives an error as Run time error
"438"

When I debug the program it catches the errors on this below line coded-

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=
_"Sheet1!R1C1:R28448C7", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet3!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10

Please help me to resolve this issue using Excel 2003.

Thanks in Advance!
Shamshad
HCL

Hi,

If you are using Excel 2003 then your pivottable version isnt 10.
Remove all references to the version like this

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=

_"Sheet1!R1C1:R28448C7").CreatePivotTable _
TableDestination:="Sheet3!R3C1", TableName:="PivotTable1")

HTH

David

 

[Alam, Shamshad]

Hi David,

Many thanks for you kind support!
Yes, it worked but I had to change the code as per below -
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R28448C7").CreatePivotTable TableDestination:= _
"Sheet3!R3C1", TableName:="PivotTable1"

Thanks again!
Shamshad
HCL