none
Object Variable or With block variable not set : MS Access RRS feed

  • Question

  • I have a code that I'm running in Access. I take the queries from access and export the data to excel then i begin to create a pivot table. The code will run through four queries then stop on the fifth one. I'm not sure how to fix the error "Object Variable not set or with block variable not set" as i have tried with statements and the code runs through four queries before it debugs. 

    Please assist. 

    Debugs here in Iowa:

    'ITM Price Var
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("ITM_Price_Var"), "Sum of ITM_Price_Var", xlSum

    Function pivotXLall()

    Application.Echo False
    '''''''''''''''''''''''''''''''''''''''
    'AC
    '''''''''''''''''''''''''''''''''''''''
    myDate = Format(Date, "mm-dd-yyyy")

    Dim acPath As String
    acPath = "Z:\Reports_&_Audits\Variance\Saved Files\AC\"
    acName = myDate & "_AC Variance.xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_2_AC", acPath & acName, True

    Set ObjExcelAppl = CreateObject("Excel.Application")

    ObjExcelAppl.DisplayAlerts = False
    Dim Objworkbook As Object
    Set Objworkbook = ObjExcelAppl.Workbooks
    Objworkbook.Open acPath & acName
    ObjExcelAppl.Visible = True
    Dim Objworksheet As Object
    Set Objworksheet = ObjExcelAppl.Worksheets("qry_2_AC")


    Dim endrow As Long
    endrow = 1
    Do While Objworksheet.Cells(endrow, 1).Value <> ""
        endrow = endrow + 1
    Loop
    endrow = endrow - 1
    If endrow <= 1 Then
    ObjExcelAppl.Quit
    MsgBox "Atlantic City Has No Data", vbOK, "AC Not Exported"

    Else



    ObjExcelAppl.Sheets.Add

            
        ObjExcelAppl.Workbooks(acName).PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "qry_2_AC!R1C1:R" & endrow & "C21", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14


        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").Position = 1
      
        
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Company").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Company").Position = 2
       
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Description").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Description").Position = 3

       
        'ITM Price Var
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("ITM_Price_Var"), "Sum of ITM_Price_Var", xlSum

        'Max of ITM_Price_Var change from sum to max
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ITM_Price_Var").Caption = "Max of ITM_Price_Var"
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Max of ITM_Price_Var").Function = xlMax
      


        'Sum of Line_Var_Total
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Line_Var_Total"), "Sum of Line_Var_Total", xlSum


        
        'Sum of VI_Gross_Item_Price
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("VI_Gross_Item_Price"), "Sum of VI_Gross_Item_Price" _
            , xlSum


        'Sum of VI_Item_Qty
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("VI_Item_Qty"), "Sum of VI_Item_Qty", xlSum
        
        'Sum of RCV_Gross_Item_Price
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("RCV_Gross_Item_Price"), _
            "Sum of RCV_Gross_Item_Price", xlSum

        'Sum of RCV_Item_Qty
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("RCV_Item_Qty"), "Sum of RCV_Item_Qty", xlSum
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").ShowDetail = False
        
        'change to ouline
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlOutlineRow
        
        ' autfit columns
            ObjExcelAppl.ActiveSheet.Columns("A:A").EntireColumn.AutoFit
        ObjExcelAppl.ActiveSheet.Columns("B:B").EntireColumn.AutoFit
        ObjExcelAppl.ActiveSheet.Columns("C:C").EntireColumn.AutoFit
    'End With

    ObjExcelAppl.Save

    ObjExcelAppl.Quit

    MsgBox "AC Exported"
    End If

    ''''\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    '
    ''''\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
    'Balitmore
    Application.Echo False
    Dim baPath As String
    baPath = "Z:\Reports_&_Audits\Variance\Saved Files\Baltimore\"
    baName = myDate & "_Baltimore Variance.xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_2_Ba", baPath & baName, True


    ObjExcelAppl.DisplayAlerts = False
    Set Objworkbook = ObjExcelAppl.Workbooks
    Objworkbook.Open baPath & baName
    ObjExcelAppl.Visible = True
    Set Objworksheet = ObjExcelAppl.Worksheets("qry_2_Ba")

    Dim baendrow As Long
    baendrow = 1
    Do While Objworksheet.Cells(baendrow, 1).Value <> ""
        baendrow = baendrow + 1
    Loop
    baendrow = endrow - 1
    If baendrow <= 1 Then
    ObjExcelAppl.Quit
    MsgBox "Balitmore Has No Data", vbOK, "Balitmore Not Exported"


    Else



    ObjExcelAppl.Sheets.Add
    'Dim Pt As PivotTable

            
        ObjExcelAppl.Workbooks(baName).PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "qry_2_Ba!R1C1:R" & baendrow & "C21", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14


        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").Position = 1
      
        
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Company").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Company").Position = 2
       
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Description").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Description").Position = 3

       
        'ITM Price Var
    '
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("ITM_Price_Var"), "Sum of ITM_Price_Var", xlSum

        'Max of ITM_Price_Var change from sum to max
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ITM_Price_Var").Caption = "Max of ITM_Price_Var"
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Max of ITM_Price_Var").Function = xlMax
      
    '    End With

        'Sum of Line_Var_Total
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Line_Var_Total"), "Sum of Line_Var_Total", xlSum
     
        
        'Sum of VI_Gross_Item_Price
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("VI_Gross_Item_Price"), "Sum of VI_Gross_Item_Price" _
            , xlSum


        'Sum of VI_Item_Qty
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("VI_Item_Qty"), "Sum of VI_Item_Qty", xlSum
        
        'Sum of RCV_Gross_Item_Price
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("RCV_Gross_Item_Price"), _
            "Sum of RCV_Gross_Item_Price", xlSum

        'Sum of RCV_Item_Qty
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("RCV_Item_Qty"), "Sum of RCV_Item_Qty", xlSum
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").ShowDetail = False
        
        'change to ouline
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlOutlineRow
        
        ' autfit columns
            ObjExcelAppl.ActiveSheet.Columns("A:A").EntireColumn.AutoFit
        ObjExcelAppl.ActiveSheet.Columns("B:B").EntireColumn.AutoFit
        ObjExcelAppl.ActiveSheet.Columns("C:C").EntireColumn.AutoFit
    'End With

    ObjExcelAppl.Save

    ObjExcelAppl.Quit

    MsgBox "Baltimore Exported"
    End If


    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    'Cincinnati
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    Application.Echo False

    Dim cinPath As String
    cinPath = "Z:\Reports_&_Audits\Variance\Saved Files\Cincinnati\"

    cinName = myDate & "_Cincinnati Variance.xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_2_Cin", cinPath & cinName, True

    Set ObjExcelAppl = CreateObject("Excel.Application")

    ObjExcelAppl.DisplayAlerts = False
    Set Objworkbook = ObjExcelAppl.Workbooks
    Objworkbook.Open cinPath & cinName
    ObjExcelAppl.Visible = True
    Set Objworksheet = ObjExcelAppl.Worksheets("qry_2_Cin")


    Dim cinendrow As Long
    cinendrow = 1
    Do While Objworksheet.Cells(cinendrow, 1).Value <> ""
        cinendrow = cinendrow + 1
    Loop
    cinendrow = cinendrow - 1
    If cinendrow <= 1 Then
    ObjExcelAppl.Quit
    MsgBox "Cincinnati Has No Data", vbOK, "Cincinnati Not Exported"

    Else



    ObjExcelAppl.Sheets.Add


        ObjExcelAppl.Workbooks(cinName).PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "qry_2_Cin!R1C1:R" & endrow & "C21", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14


        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").Position = 1
      
        
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Company").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Company").Position = 2
       
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Description").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Description").Position = 3

       
        'ITM Price Var
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("ITM_Price_Var"), "Sum of ITM_Price_Var", xlSum

        'Max of ITM_Price_Var change from sum to max
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ITM_Price_Var").Caption = "Max of ITM_Price_Var"
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Max of ITM_Price_Var").Function = xlMax
      


        'Sum of Line_Var_Total
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Line_Var_Total"), "Sum of Line_Var_Total", xlSum

        
        'Sum of VI_Gross_Item_Price
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("VI_Gross_Item_Price"), "Sum of VI_Gross_Item_Price" _
            , xlSum


        'Sum of VI_Item_Qty
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("VI_Item_Qty"), "Sum of VI_Item_Qty", xlSum
        
        'Sum of RCV_Gross_Item_Price
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("RCV_Gross_Item_Price"), _
            "Sum of RCV_Gross_Item_Price", xlSum

        'Sum of RCV_Item_Qty
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("RCV_Item_Qty"), "Sum of RCV_Item_Qty", xlSum
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").ShowDetail = False
        
        'change to ouline
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlOutlineRow
        
        ' autfit columns
            ObjExcelAppl.ActiveSheet.Columns("A:A").EntireColumn.AutoFit
        ObjExcelAppl.ActiveSheet.Columns("B:B").EntireColumn.AutoFit
        ObjExcelAppl.ActiveSheet.Columns("C:C").EntireColumn.AutoFit


    ObjExcelAppl.Save

    ObjExcelAppl.Quit

    MsgBox "Cincinnati Exported"
    End If
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    'Clevand Thistle Down
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    Application.Echo False

    Dim ctPath As String
    ctPath = "Z:\Reports_&_Audits\Variance\Saved Files\Cleve-ThistleDown\"

    ctName = myDate & "_Cleve-ThistleDown Variance.xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_2_CleThis", ctPath & ctName, True

    Set ObjExcelAppl = CreateObject("Excel.Application")
    ObjExcelAppl.DisplayAlerts = False
    Set Objworkbook = ObjExcelAppl.Workbooks
    Objworkbook.Open ctPath & ctName
    ObjExcelAppl.Visible = True
    Set Objworksheet = ObjExcelAppl.Worksheets("qry_2_CleThis")


    Dim ctendrow As Long
    ctendrow = 1
    Do While Objworksheet.Cells(ctendrow, 1).Value <> ""
        ctendrow = ctendrow + 1
    Loop
    ctendrow = citendrow - 1
    If citendrow <= 1 Then
    ObjExcelAppl.Quit
    MsgBox "Cle-This Has No Data", vbOK, "Cle-This Not Exported"


    Else


    ObjExcelAppl.Sheets.Add

            
        ObjExcelAppl.Workbooks(ctName).PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "qry_2_CleThis!R1C1:R" & ctendrow & "C21", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14


        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").Position = 1
      
        
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Company").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Company").Position = 2
       
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Description").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Description").Position = 3

       
        'ITM Price Var
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("ITM_Price_Var"), "Sum of ITM_Price_Var", xlSum

        'Max of ITM_Price_Var change from sum to max
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ITM_Price_Var").Caption = "Max of ITM_Price_Var"
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Max of ITM_Price_Var").Function = xlMax
      


        'Sum of Line_Var_Total
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Line_Var_Total"), "Sum of Line_Var_Total", xlSum
        
        'Sum of VI_Gross_Item_Price
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("VI_Gross_Item_Price"), "Sum of VI_Gross_Item_Price" _
            , xlSum


        'Sum of VI_Item_Qty
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("VI_Item_Qty"), "Sum of VI_Item_Qty", xlSum
        
        'Sum of RCV_Gross_Item_Price
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("RCV_Gross_Item_Price"), _
            "Sum of RCV_Gross_Item_Price", xlSum

        'Sum of RCV_Item_Qty
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("RCV_Item_Qty"), "Sum of RCV_Item_Qty", xlSum
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").ShowDetail = False
        
        'change to ouline
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlOutlineRow
        
        ' autfit columns
            ObjExcelAppl.ActiveSheet.Columns("A:A").EntireColumn.AutoFit
        ObjExcelAppl.ActiveSheet.Columns("B:B").EntireColumn.AutoFit
        ObjExcelAppl.ActiveSheet.Columns("C:C").EntireColumn.AutoFit
    'End With

    ObjExcelAppl.Save

    ObjExcelAppl.Quit

    MsgBox "CleThis Exported"
    End If

    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    'Iowa Region
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    '''''''''''''''''''''''''''''''''''''''
    Application.Echo False

    Dim iaPath As String
    iaPath = "Z:\Reports_&_Audits\Variance\Saved Files\Iowa Region\"

    iaName = myDate & "_Iowa Variance.xlsx"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qry_2_Ia", iaPath & iaName, True

    Set ObjExcelAppl = CreateObject("Excel.Application")

    ObjExcelAppl.DisplayAlerts = False
    Set Objworkbook = ObjExcelAppl.Workbooks
    Objworkbook.Open iaPath & iaName
    ObjExcelAppl.Visible = True
    Set Objworksheet = ObjExcelAppl.Worksheets("qry_2_Ia")


    Dim iaendrow As Long
    iaendrow = 1
    Do While Objworksheet.Cells(iaendrow, 1).Value <> ""
        iaendrow = iaendrow + 1
    Loop
    iaendrow = iaendrow - 1
    If iaendrow <= 1 Then
    ObjExcelAppl.Quit
    MsgBox "Iowa Has No Data", vbOK, "Iowa Not Exported"

    Else



    ObjExcelAppl.Sheets.Add

            Debug.Print ("qry_2_Ia")
            
        ObjExcelAppl.Workbooks(iaName).PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "qry_2_Ia!R1C1:R" & iaendrow & "C21", Version:=xlPivotTableVersion14).CreatePivotTable _
        TableDestination:="Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion _
        :=xlPivotTableVersion14

        
         ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").Position = 1
      
        
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Company").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Company").Position = 2
       
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Description").Orientation = xlRowField
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Description").Position = 3

       
        'ITM Price Var
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("ITM_Price_Var"), "Sum of ITM_Price_Var", xlSum

        'Max of ITM_Price_Var change from sum to max
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of ITM_Price_Var").Caption = "Max of ITM_Price_Var"
             ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Max of ITM_Price_Var").Function = xlMax
      


        'Sum of Line_Var_Total
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("Line_Var_Total"), "Sum of Line_Var_Total", xlSum

        
        'Sum of VI_Gross_Item_Price
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("VI_Gross_Item_Price"), "Sum of VI_Gross_Item_Price" _
            , xlSum


        'Sum of VI_Item_Qty
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("VI_Item_Qty"), "Sum of VI_Item_Qty", xlSum
        
        'Sum of RCV_Gross_Item_Price
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("RCV_Gross_Item_Price"), _
            "Sum of RCV_Gross_Item_Price", xlSum

        'Sum of RCV_Item_Qty
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
            "PivotTable1").PivotFields("RCV_Item_Qty"), "Sum of RCV_Item_Qty", xlSum
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Item_Number").ShowDetail = False
        
        
       
             
             
        'change to ouline
        ObjExcelAppl.ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlOutlineRow
        
        ' autfit columns
            ObjExcelAppl.ActiveSheet.Columns("A:A").EntireColumn.AutoFit
        ObjExcelAppl.ActiveSheet.Columns("B:B").EntireColumn.AutoFit
        ObjExcelAppl.ActiveSheet.Columns("C:C").EntireColumn.AutoFit


    ObjExcelAppl.Save

    ObjExcelAppl.Quit

    MsgBox "Iowa Exported"
    End If



    • Edited by Legzen34 Thursday, January 19, 2017 5:58 PM
    Thursday, January 19, 2017 5:55 PM

All replies

  • Wow. Lotsa code.

    Hypothesis:

    The base query, "qry_2_Ia", does not contain a field named exactly "ITM_Price_Var", since that is the first time that field is used from that query. My money is on a leading/trailing space, Space instead of an underscore, or doubled up character.

    You can verify by looking at the headings transferred and shown on worksheet qry_2_Ia

    You could debug further by (temporarily) adding the "ITM_Price_Var"  field as another row & see if it blows up there instead of the summation field.


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com


    • Edited by MainSleuth Thursday, January 19, 2017 7:21 PM
    Thursday, January 19, 2017 7:18 PM
  • MainSleuth...How right you are. I completely missed that! "ITM_Price_Var" is "NOT" in the field summary. Thanks again!
    Thursday, January 19, 2017 11:25 PM
  • Still debugs....debugs on the next pivot table adddatafield sigh...
    Thursday, January 19, 2017 11:44 PM
  • When i run each one individually, the code works. When i put it all together. It doesn't.

    Thursday, January 19, 2017 11:50 PM
  • Yes - it looks to my eye that you are doing a lot of the same stuff over & over. It's hard to be efficient that way, but you have to start somewhere. Sometimes breaking things up helps you and your machine to keep things in the right box.

    The next step is probably to put each variance in it's own sub & then call them one by one. You will see the pattern from one variance to the next and can generalize from there. You can also convert some of the repetitive lines (like finding the last row) into a function.
    Here's what I mean for both breaking it up and using a function for repetitive stuff. This is clearly incomplete, but it should give you the idea:

    Option Explicit
    
    Function pivotXLall()
      Dim ObjExcelAppl  As Excel.Application
      Application.Echo False
      Set ObjExcelAppl = CreateObject("Excel.Application")
      If Not ObjExcelAppl Is Nothing Then
        ObjExcelAppl.DisplayAlerts = False
        GetTheACData ObjExcelAppl
        ' ... Break your large sub into smaller ones - one for each export.
        GetTheIowaData ObjExcelAppl
      End If
      ObjExcelAppl.Quit
      Set ObjExcelApp = Nothing
    End Function
    
    Public Function FindTheLastRow(wksThisOne As Excel.Worksheet) As Long
    ' A generic function that returns the last row used from the export
      Dim FindTheLastRow As Long
      On Error Resume Next
      FindTheLastRow = wksThisOne.UsedRange.Rows.Count - 5    ' maybe this is a better guess than 1
      If FindTheLastRow < 1 Then FindTheLastRow = 1
      Do While wksThisOne.CellsFindTheLastRow1.Value <> ""
         FindTheLastRow = FindTheLastRow + 1
      Loop
      FindTheLastRow = FindTheLastRow - 1
    End Function
    
    Private Sub GetTheACData(oXlApp As Excel.Application)
        Const acPath As String = "Z:\Reports_&_Audits\Variance\Saved Files\AC\"
        Const strExportQueryName As String = "qry_2_AC"
        Dim myDate As String
        Dim acName As String
        Dim Objworkbook As Excel.Workbook
        Dim Objworksheet As Excel.Worksheet
        Dim endrow  As Long
    
        On Error Resume Next        ' I suggest usung your favorite error handling setup instead of this
       
         myDate = Format(Date, "mm-dd-yyyy")
         acName = myDate & "_AC Variance.xlsx"
         DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strExportQueryName, acPath & acName, True
         Set Objworkbook = oXlApp.Workbooks.Open(acPath & acName)
         If Not Objworkbook Is Nothing Then
            Set Objworksheet = Objworkbook.Sheets(strExportQueryName)
            If Not Objworksheet Is Nothing Then
                endrow = FindTheLastRow(Objworksheet)
                If endrow <= 1 Then
                   MsgBox "Atlantic City Has No Data", vbOK, "AC Not Exported"
                Else
                   '... Build your pivot table for Atlantic City Here...
                End If
                Objworkbook.Save
                MsgBox "AC Exported"
            'Else
            ' Code in here allows you to do a little error trapping if the worksheet isn't found
                MsgBox "Sheet " & strExportQueryName & " was not found in workbook """ & acName & """ for Atlantic City."
            End If
            Objworkbook.Close
         'Else
            ' Code in here  allows you to do a little error trapping if the Workbook isn't found
            MsgBox "Workbook """ & acName & """ for Atlantic City could not be opened."
        End If
         Set Objworksheet = Nothing
         Set Objworkbook = Nothing
    End Sub
    
    Private Sub GetTheIowaData(oXlApp As Excel.Application)
        Const acPath As String = "Z:\Reports_&_Audits\Variance\Saved Files\Iowa Region\"
        Const strExportQueryName As String = "qry_2_Ia"
        Dim myDate As String
        Dim acName As String
        Dim Objworkbook As Excel.Workbook
        Dim Objworksheet As Excel.Worksheet
        
        '...
    
         Set Objworksheet = Nothing
         Set Objworkbook = Nothing
    End Sub
    


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com

    Friday, January 20, 2017 1:28 AM
  • MainSleuth, that does make sense, however I gave up this morning and I just now checked for a response. I redid everything in excel this morning. I'm still learning so your advice did not go without strain. Thank you for your advanced knowledge. 
    Friday, January 20, 2017 11:00 PM
  • Hi,

    Thanks for posting here.

    If you have any new issues, please feel free to post and many community members are willing to help.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 23, 2017 7:02 AM
    Moderator