Cannot open pivottable source file when excel file opened from a email

[ikauai]

I have a macro that runs on open and one of the things it does is refreshes a pivot table based off another sheet within the same workbook and that works just fine on my pc but if someone open the file from their email then they get an error:
"Cannot open pivottable source file"

Is there a fix to this so that the pivot table doesn't think the data is from an external data source?

 

[ikauai]

Can anyone here help???

 

[Jerry Sullivan]

Where does the Pivot Source reside?

 

[ikauai]

The source data resides on the 1st sheet of the same workbook. The sheet is named "example raw data". Below is the code that I am using in the macro to update the pivot table on the "example pivot table" sheet. Note: this code is working perfectly on my pc but only gives the error if I email it and it's opened and run from the email. I am not sure if the error is related to the IE browser or what...but I need to make it work from an email or website open as well.

Sheets("example pivot table ").Select
Dim rCount As Long
Dim Rng As Range
rCount = Worksheets("example raw data").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
Set Rng = Worksheets("example raw data").Range("A1:AH" & rCount)
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=Rng

-The error looks to be caused by the activesheet.pivottablewizard line and I get the message "Runtime error 1004" "Cannot open pivottable source file" and then it lists the path of the file which says it resides in a temporary internet file folder...

 

[Jerry Sullivan]

A couple of things for you to try.

1. Reconsider whether you need to have this event code inside a workbook you are sending to others. If they aren't going to be changing the source data, then there is no reason to have something run automatically each time they open the file. As you have found this can lead to unexpected problems.

2. I haven't used .PivotTableWizard in macros, but it appears to be intended to create PT's not update them. It sounds like it works for you in your workbook, but if you need to send a macro-enabled file that auto-updates, I'd suggest you use someting like

Activeworkbook.RefreshAll

BTW, This statement isn't needed, and if the extra space at the end of the sheetname doesn't match your workbook's sheet name, that will cause an errror.

Sheets("example pivot table ").Select

Good luck!

 

[ikauai]

Thanks for the feedback. In response to point #1, the file I am in the process of creating is going to be used as a sort of template for an excel download file from a website report. I need to update the pivot table with a data source that will be a different size with each download otherwise the refreshall method would work perfectly. Is there another method you would suggest I use besides the pivottablewizard to change the datasource on open?

Re: #3, thanks, I will delete that line as well.

Going forward, it appears I am still stuck with the error caused by trying to dynamically change the datasource for the pivottable on the first onopen event for the workbook. Do you or anyone else have any other suggestions that might help workaround this error I am still having?

Thanks!

 

[Jerry Sullivan]

Going forward, it appears I am still stuck with the error caused by trying to dynamically change the datasource for the pivottable on the first onopen event for the workbook. Do you or anyone else have any other suggestions that might help workaround this error I am still having?

I suggest you define a dynamic named range in your workbok for the data source range, have the pivottable use that named range as it's datasource.

Then your open event can be simply...

Activeworkbook.RefreshAll

 

[ikauai]

Ok, I dropped the pivottablewizard method and assigned the sourcedata a range (just like I did earlier). Works fine if I run it from my pc (same as before) but if I email it to myself and run the macro after opening the file from email, I still encounter the very same error "cannot open pivottable source file" and it gives the path... Here is my code below.

Dim rCount As Long
Dim Rng As Range
rCount = Worksheets("example raw data").Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
Set Rng = Worksheets("example raw data").Range("A1:AH" & rCount)
ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Rng, Version:=xlPivotTableVersion12)

So, I am still very confused why it works from my file saved on my pc and doesnt work if i run it from the emailed file...???

 

[Jerry Sullivan]

If you follow the suggestion of post #7, there is no need for any of that code you are trying.

I understand that it's strange that it works on your computer but not others, but I'm trying to show you a better way to approach this instead of trying to find the bug in your current setup.

 

[Andrew Poulsom]

Try saving the workbook from the EMail and opening that instead of opening it directly.