Hi,

I am writing a VB program to creat PivotTables and Charts. The source data
is in a Excel file and the output with pivot tables and charts should be
stored in another excel file. My code fails in the following command

oWorkBookOut.PivotCaches.Add(xlDatabase,
"'dec'!R1C1:R179C20").CreatePivotTable oWorkSheetOut.Range("A3"),
PivotTableName

I am getting a "Application-defined error or object-defined error"

I am using VB 6.0 with SP6 and Office 2000 with SP3.

This code works well in Win XP and Office 2003.

Please help

Cheers,
Vijay

I don't know why this works in XP and 2003 but fails in 2000. XP certainly
added a lot of new properties & methods to Pivot-related objects, but none
of those appear in the code you posted.

However, if the Source data isn't in the same workbook as the Output (i.e.,
the Pivot Table) then I would think that "'dec'!R1C1:R179C20" would require
a workbook reference, since I assume sheet "dec" does not exist in the
Output book. But, if this is the problem, I don't know why the code would
only fail in 2000 and not in XP & 2003 as well...

This is one of those times where recording a macro - in Excel 2000 - and
looking at the results might answer a lot of questions...

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"Vijay" wrote in message
...
Hi,

I am writing a VB program to creat PivotTables and Charts. The source data
is in a Excel file and the output with pivot tables and charts should be
stored in another excel file. My code fails in the following command

oWorkBookOut.PivotCaches.Add(xlDatabase,
"'dec'!R1C1:R179C20").CreatePivotTable oWorkSheetOut.Range("A3"),
PivotTableName

I am getting a "Application-defined error or object-defined error"

I am using VB 6.0 with SP6 and Office 2000 with SP3.

This code works well in Win XP and Office 2003.

Please help

Cheers,
Vijay

A number of Pivot Table functions are not available in Excel 2000 VBA -
introduced in 2002 iirc


--
chubster264
------------------------------------------------------------------------
chubster264's Profile: http://www.excelforum.com/member.php...o&userid=30164
View this thread: http://www.excelforum.com/showthread...hreadid=509998

Hi Guys,

I have fixed the problem. I specified "[WorkBook1]dec!R1C1:R179C20" and it
worked. I am not sure why the PivotCaches.Add method is not taking a Range
object for the SourceData argument. Though the help says that a Range object
could be passed.

Cheers,
Vijay

"chubster264" wrote:


A number of Pivot Table functions are not available in Excel 2000 VBA -
introduced in 2002 iirc


--
chubster264
------------------------------------------------------------------------
chubster264's Profile: http://www.excelforum.com/member.php...o&userid=30164
View this thread: http://www.excelforum.com/showthread...hreadid=509998