Error 438 Object doesn't support this property or method

Marilync1266
Marilync1266 used Ask the Experts™
on
I'm trying to update the data source for a pivot table in an excel file using Access vba and I receive an error.  I've never done this before - so I could be way off.  Help!  :-)


Set xlApp = New Excel.Application
Set xlwb_Book = xlApp.Workbooks.Open(strBookFileName)
            xlwb_Book.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Region 20!R4C1:R1934C24" _
            , Version:=xlPivotTableVersion10)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
wild guess?


Set xlwb_Book = xlApp.Workbooks.Open(strBookFileName)
            xlwb_Book.PivotTables("PivotTable2").ChangePivotCache.ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Region 20!R4C1:R1934C24" _
            , Version:=xlPivotTableVersion10)

Author

Commented:
Actually, I open the file and manipulate the data before I get to the pivot table code - so I wouldn't be able to open it again.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
...Then I'm confused...

I am sure an Excel Expert will be along shortly...
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Thanks anyway!
Try
Activesheet instead of xlwb_Book but first select the sheet Sheets("name of sheet").select

Author

Commented:
This is what I tried:

            xlwb_Book.Sheets("Extended Forecast").Activate
                       
            ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "Region 20!R4C1:R1934C24" _
            , Version:=xlPivotTableVersion10)

I received an error "1004 Unable to get the PivotTables property of the worksheet class"
Try this

            ActiveSheet.PivotTables("PivotTable2").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            strBookFileName & "Region 20!R4C1:R1934C24" _
            , Version:=xlPivotTableVersion10)

it is looking for the full path and filename of the excel file. :)
Hi Marilyn,

Some questions:
What is it that you actually do?
Do you have a database in which data is used to update your pivot table in Excel?  
On what moment is the exception triggered?

Perhaps you can attach the files or a file so it can be studied.

Danny

Author

Commented:
Hi Danny,

I have a database that outputs data to 4 different excel files.  For each group (region), I open each file and copy that group's worksheet into another file.  In the end, each group will have their own excel file with 4 different worksheets.  I have a new requirement to copy a worksheet with a pivot table into each group's file.  I need to update the datasource.  I'm not sure if this makes sense :-)  Here is my code:
Combine-Reports-Code.txt

Author

Commented:
Thanks, that worked!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial