Build Pivot Table Using VBA. Works Fine on One Machine But Not on Another

Question

Sheets("Daily Reconciliation").Select
Last = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Daily Reconciliation!R3C1:R" & Last & "C20", Version:=xlPivotTableVersion10) _
        .CreatePivotTable TableDestination:=Sheets("Pivot_Sheet").Range("A3"), TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion10
    Sheets("Pivot_Sheet").Select
    Cells(3, 1).Select
    
    Sheets("Pivot_Sheet").Select
    Range("A3").Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Project ID")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Project Description")
        .Orientation = xlRowField
        .Position = 2
    End With

etc . . .

Does anyone have any idea whi the code above would work fine on one machine, but not on another.  Two people are accessing the same file on the network drive (at different times); the Pivot Table is generated just fine on my computer, but not on a colleague's computer.  Both of use use BOTH Excel 2010 and 2003 (not sure why they even have 2003 installed, but it is installed).  Excel 2010 is the default.  Is there some setting that I can check?  Is there some kind of configuration that would make this work on one machine, but not another?

As an aside, the process of closing files, using VBA is different on these two machines.  Again, I'm wondering if there is a setting or configuration that I can check to make the performance identical.

Dim dtaWbk As Workbook

Set dtaWbk = Workbooks.Open(dataFileName, , , , , "thepassword")

. . . lots of stuff here . . . . . .

dtaWbk.Close SaveChanges:=False

I'm prompted with the MessageBox, asking my whether I want to save the files or not; it even happens when I tried this:

Application.DisplayAlerts = False

dtaWbk.Close savechanges:=False

Application.DisplayAlerts = True

Again, on one machine the user is prompted to save the multiple files that are opened, and on the other machine, the user is NOT prompted to save the multiple files that are opened.

Any thoughts anyone???

---

Ryan Shuell

Answer 1

Making the Excel environment uniform is quite a challenge.

I'd start by enumerating every Application level property you can...and comparing the two machines.
Next up: References with the VBE...enumerate them.

Finally, check to insure the Trust Center settings are the same.

Answer 2

I just looked at the PivotCaches object in Excel 2003 here and I didn't see a Create method.  I do see an "Add" method.  I do see the "Create" method in the 2010 version here.

I'm guessing that's the issue.

---

HTH,

Eric

Tu ne cede malis sed contra audentior ito.

Explicit hoc totum,
Pro Christo da mihi potum.

Answer 3

Wait ! I thought you said both were running 2010 and that 2003 just happened to me on there.

You're not comparing 2003 vs. 2010, are you ?

Answer 4

Thanks for all the help, everyone.  Sorry for the last reply.  I was in Mexico for the past week, and it's almost impossible to get an Internet connection here.  I'm back in business now.  Thanks for everything!!

---

Ryan Shuell