Object Variable or With block variable not set : MS Access

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

Answer 1

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

Answer 2

MainSleuth...How right you are. I completely missed that! "ITM_Price_Var" is "NOT" in the field summary. Thanks again!

Answer 3

Still debugs....debugs on the next pivot table adddatafield sigh...

Answer 4

When i run each one individually, the code works. When i put it all together. It doesn't.

Answer 5

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

Answer 6

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. 

Answer 7

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 [e-mail].