Refreshing Excel Pivot tables from Access 2007 1

[romeerome368]

Hello Everyone,

I have an Access 2007 database that I generate reports in Excel. The data in the database gets refreshed everyday at 5 am in the morning, and I have a scheduled task that runs the refresh of my backend database, and a task that refreshs my Excel spreadsheet as well. My code stops working on the refreshall command that is in my VBA code. I checked my Excel VBA references, and they are all checked without anything MISSING. Also, I know that the simple thing to do would be to have the spreadsheet to refresh upon opening, but I don't want it to do that everytime since this is a snapshot of data that I am providing, therefore I am doing through VBA.

Below is a sample of my code:

Public Sub UpDateExcel()
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim x As Variant
Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
xlApp.Visible = True

xlApp.Workbooks.Open ("C:\Filename.XLS")

xlApp.ActiveWorkbook.RefreshAll

xlApp.Range("C8").Select
x = xlApp.Range("C8").Value
x = x + 1
xlApp.ActiveCell.Value = x

xlApp.ActiveWorkbook.Save
xlApp.ActiveWorkbook.Close
xlApp.Quit

Set xlApp = Nothing
Set xlWorkbook = Nothing
Set xlSheet = Nothing
Set x = Nothing

End Sub

Everytime I run my VBA from Access, I get the error message below.

Runtime Error '1004':
Method 'RefreshAll" of object '_Workbook' failed

I know this code should work, but please let me know if I'm missing something.

Thanking you in advance for your assistance.

 

[SkipVought]

Hi,

Why bother?

In the Excel workbook_open event, perform a refreshall.

Otherwise, in your code...

   With xlApp.Workbooks.Open ("C:\Filename.XLS")

       .RefreshAll
    
       with [red][b].Worksheets("SpecifyWhichSheetName")[/b][/red].Range("C8")
           .value = .value + 1
       end with

      .Save
      .Close
   end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[romeerome368]

The only reason I don't is because, the refresh takes about 5 to 7 minutes, and I don't want the people who are looking at the spreadsheet to have to wait that long to see the data.

That's why I'm using VBA instead so that when my scheduled task runs, it does my refresh as well.

 

[romeerome368]

Skip,

I changed my code to the way you suggested it, and it still stops on the refreshall with the same error message. There has got to be a better way to do this. I thought by going to Excel 2010 and using Access 2007 that things would be alot better as far as code execution is concern.

Thanks for your help Skip.

I guess it's just another Microsoft coding problem.

 

[SkipVought]

[b]
   dim pvc as Excel.Pivotcache[/b]
'.....
   With xlApp.Workbooks.Open ("C:\Filename.XLS")
       [b] 
       for each pvc in .pivotcaches
          pvc.refresh
       next
    [/b]
       with .Worksheets("SpecifyWhichSheetName").Range("C8")
           .value = .value + 1
       end with

      .Save
      .Close
   end with

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[romeerome368]

Hey Skip,

Thanks for your help!!!

I have chenged my code to match yours, and now Access is giving another error message.

Run-time error '1004':

The PivotTable field name is not valid. To create a
pivottable report, you must use data that is organized as
a list with labeled columns. If you are changing the name
of a pivottable field, you must type a new name for the field.

This has got me scratching my head.

Could it possibly be that since my code never gets to set xlApp, xlWorkbook, and xlSheet to nothing that it's holding all of this stuff in memory and not being cleared properly?

 

[SkipVought]

All those object must be set! Did you not insert my code into your code that does set each?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[romeerome368]

Skip,

This is what my code looks like:

    Dim xlApp As Excel.Application
    Dim xlWorkbook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    [COLOR=red]Dim pvc As Excel.PivotCache[/color]
    Dim x As Variant
    Set xlApp = CreateObject("Excel.Application")
    xlApp.DisplayAlerts = False
    xlApp.Visible = True
    
    With xlApp.Workbooks.Open("C:\Filename.XLS")
        [COLOR=red]For Each pvc In .PivotCaches[/color]
        [COLOR=red]    pvc.Refresh[/color]
        [COLOR=red]Next[/color]

    End With

I did exactly as you told me. This is maddening. (LOL)

 

[SkipVought]

The PivotTable field name is not valid.

You need to know WHAT field name is not valid.

Have you done any exploring in Debug?

Have you OPENED the workbook and tried to refresh this PT?



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[romeerome368]

Yes,

In the debug area is where the message is popping up. There are several pivottables in the workbook. I'll say that this workbook has about 18 worksheets, and there are different pivottables in each of the worksheets. There are 6 summary worksheets that are not hidden, but all of the other worksheets are hidden.

Yes to your second question, I have opened the workbook and have refreshed each of them manually. Maybe Access is having difficulty because there are so many pivottables that are being used.

I am using pivottables because they take up alot less space than a querytable does. Originally when I had querytables, it made my workbook about 20MB is size which opened another "can of worms" for me with respect to emailing it to my subscribers.

 

[SkipVought]

Can all your subscribers get to a common network drive? If so, simple send them a link.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[romeerome368]

The workbook is posted on a SharePoint so the subscribers can go and either view the data or download it, but whenever our SharePoint site is under maintenance, I have to email it.

 

[SkipVought]

In the debug area is where the message is popping up.

If you actually DEBUG (ie hit the DEBUG button) you can 'see' what objects/values may be a problem. Use the Watch Window to discover stuff.

faq707-4594

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[romeerome368]

Thanks Skip, I'll try it, and let you know what I discover.

 

[SkipVought]

Where in Texas? Grand Prairie, here.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[romeerome368]

Dallas