Hi,

I'm trying to write a VB 6 app that updates the source
range for a set of pivot tables. Specifically, I'm using
the following code:

Set xlOutputSheet = xlOutputWorkbook.Sheets("MySheet")
Set xlPivot = xlOutputSheet.PivotTables _
("MyPivotTable")
Set xlPivotCache = xlPivot.PivotCache
RangeString = "MyOtherSheet!R2C1:R" & _
(Table_Dimension) & "C10"
With xlPivotCache
.SourceData = RangeString
.Refresh
End With

For most of the pivot tables, this works. However,
occasionally, for a reason I can't deduce I get the
following error:

"Application-defined or object-defined error"
at ".SourceData = RangeString". I'm pretty certain the
RangeString is correct.

Any insight would be helpful.

Thanks,

Craig

Try adding the following code to make debugging easier:

Set wsMyOtherSheet = Sheets("MyOtherSheet")
Set rngData = wsMyOtherSheet.Range("R2C1:R2C10").Resize(Table_Di mension)

then change to some variation of the following line (single step the
debugger and double-check that the string includes the worksheet name) :
.SourceData = rngData.Address

If the worksheet name has spaces in it, then I think quotes or brackets
around the worksheet name are required. The Resize method is an easier way
of extending the data area rather than trying to concantenate strings
together to define the range of the data.

When the error occurs, you should then be able to double-check what rngData
is set to. It might be that Table_Dimension happens to be wrong, or 0, or
something. I have seen this error myself in the past, but can't quite
remember the exact nature, except in the case where there is actually no
data for some reason. Also, make sure that you actually have 10 columns of
labels (R1C1 to R1C10), otherwise the pivot table methods will throw an
error because they can't resolve the fields.
--
Regards,
Bill


"CRL" wrote in message
...
Hi,

I'm trying to write a VB 6 app that updates the source
range for a set of pivot tables. Specifically, I'm using
the following code:

Set xlOutputSheet = xlOutputWorkbook.Sheets("MySheet")
Set xlPivot = xlOutputSheet.PivotTables _
("MyPivotTable")
Set xlPivotCache = xlPivot.PivotCache
RangeString = "MyOtherSheet!R2C1:R" & _
(Table_Dimension) & "C10"
With xlPivotCache
.SourceData = RangeString
.Refresh
End With

For most of the pivot tables, this works. However,
occasionally, for a reason I can't deduce I get the
following error:

"Application-defined or object-defined error"
at ".SourceData = RangeString". I'm pretty certain the
RangeString is correct.

Any insight would be helpful.

Thanks,

Craig