none
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

    Tuesday, December 11, 2012 9:31 PM

Answers

  • 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.

    • Marked as answer by ryguy72 Thursday, December 20, 2012 1:32 PM
    Wednesday, December 12, 2012 4:10 PM
  • 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.

    Thursday, December 13, 2012 3:49 PM
  • 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 ?

    • Proposed as answer by Syswizard Saturday, December 15, 2012 2:24 AM
    • Marked as answer by ryguy72 Thursday, December 20, 2012 1:32 PM
    Thursday, December 13, 2012 9:57 PM

All replies

  • 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.

    • Marked as answer by ryguy72 Thursday, December 20, 2012 1:32 PM
    Wednesday, December 12, 2012 4:10 PM
  • 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.

    Thursday, December 13, 2012 3:49 PM
  • 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 ?

    • Proposed as answer by Syswizard Saturday, December 15, 2012 2:24 AM
    • Marked as answer by ryguy72 Thursday, December 20, 2012 1:32 PM
    Thursday, December 13, 2012 9:57 PM
  • 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

    Thursday, December 20, 2012 1:33 PM