Link to home
Start Free TrialLog in
Avatar of jrogersok
jrogersok

asked on

Access 2007 Object Variable or With Block Not Set

Ok, I've fought with this long enough.....

I have an Access 2007 (in .mdb) database where I am exporting data to Excel to create an Excel Pivot Table and Chart.  I am using an object variable and creating an instance of an Excel object and using a button on an unbound form to run the code.  On the form the user selects a month and year from combo boxes to process data to that point.

Everything works fine the first time I click the button.  However, if I return to the form and change the dates, I get an Object Variable not set about 1/2way through the code.  The strange thing is that the object variable is used to perform several other commands previous to it getting the error so it was working to that point.

See the code below -- any suggestions?  I've been fighting this for hours.

Thanks!
 User generated image
Dim appXL As Object

    If IsNull(Me.cboYear) Or IsNull(Me.cboMonth) Then
        MsgBox "A year and month must be selected to run this process.", vbCritical
        Exit Sub
    End If
    
    Call SetDatesAndPath
  
    DoCmd.TransferSpreadsheet acExport, , "EXOqryJamUpsWaste", filePath & "\JamUpsExport.xlsx", True
    
    Set appXL = CreateObject("Excel.Application")
    
    appXL.Workbooks.Open (filePath & "\JamUpsExport.xlsx")
    appXL.Visible = True
    
    'Format Date column as mmm-yy
    appXL.Columns("C:C").Select
    appXL.Selection.NumberFormat = "[$-409]mmm-yy;@"

    'Set range of data
    appXL.ActiveSheet.Range("a1").Select
    appXL.ActiveCell.CurrentRegion.Select
    
    Dim listRange As Object
    Set listRange = appXL.Selection
    
    'Create Pivot Table
    appXL.Sheets.Add
    
    appXL.ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        listRange, Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="Sheet1!R1C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion12
    appXL.Sheets(1).Select
    appXL.Cells(1, 1).Select
    
   ' appXL.ActiveSheet.PivotTable("PivotTable1").AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("sumOfWaste Qty"), "Sum Of SumofWaste Qty", xlSum
    
    appXL.ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("SumOfWaste Qty"), "Sum of SumOfWaste Qty", xlSum
    
    appXL.ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").Orientation = xlRowField
    appXL.ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").Position = 1
    
    appXL.ActiveSheet.PivotTables("PivotTable1").PivotFields("Machine").Orientation = xlColumnField
    appXL.ActiveSheet.PivotTables("PivotTable1").PivotFields("Machine").Position = 1
    
    'Create Chart
    appXL.ActiveSheet.Shapes.AddChart.Select
    appXL.ActiveChart.SetSourceData Source:=Range("'Sheet1'!$A$1:$G$14")
    appXL.ActiveWorkbook.ShowPivotChartActiveFields = True
    appXL.ActiveChart.ChartType = xlColumnClustered

    appXL.ActiveChart.ChartType = xlLineMarkers
    appXL.ActiveChart.ApplyLayout (5)
    appXL.ActiveChart.Axes(xlValue).AxisTitle.Select
    appXL.Selection.Delete
    appXL.ActiveSheet.ChartObjects("Chart 1").Activate
    appXL.ActiveChart.ChartTitle.Select
    appXL.Selection.Caption = "Jam Ups Per Month"
    

    appXL.ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"

    appXL.ActiveWorkbook.Close savechanges:=True, FileName:=filePath & "\01-" & strMonth & strYear & "-JamUps Per Month.xlsx"
    
    appXL.Application.Quit
    Set appXL = Nothing
    
    MsgBox "Jam Ups has been printed and saved as an Excel file."
    
    
End Sub

Open in new window

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

please try this:
Dim oPT As PivotTable
Dim appXL As Object

    If IsNull(Me.cboYear) Or IsNull(Me.cboMonth) Then
        MsgBox "A year and month must be selected to run this process.", vbCritical
        Exit Sub
    End If
    
    Call SetDatesAndPath
  
    DoCmd.TransferSpreadsheet acExport, , "EXOqryJamUpsWaste", filePath & "\JamUpsExport.xlsx", True
    
    Set appXL = CreateObject("Excel.Application")
    
    appXL.Workbooks.Open (filePath & "\JamUpsExport.xlsx")
    appXL.Visible = True
    
    'Format Date column as mmm-yy
    appXL.Columns("C:C").Select
    appXL.Selection.NumberFormat = "[$-409]mmm-yy;@"

    'Set range of data
    appXL.ActiveSheet.Range("a1").Select
    appXL.ActiveCell.CurrentRegion.Select
    
    Dim listRange As Object
    Set listRange = appXL.Selection
    
    'Create Pivot Table
    appXL.Sheets.Add
    
    set oPT = appXL.ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        listRange, Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="Sheet1!R1C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion12
    appXL.Sheets(1).Select
    appXL.Cells(1, 1).Select
    
   ' oPT.AddDataField ActiveSheet.PivotTables("PivotTable1").PivotFields("sumOfWaste Qty"), "Sum Of SumofWaste Qty", xlSum
    
    oPT.AddDataField ActiveSheet.PivotTables( oPT.PivotFields("SumOfWaste Qty"), "Sum of SumOfWaste Qty", xlSum
    
    oPT.PivotFields("Date").Orientation = xlRowField
    oPT.PivotFields("Date").Position = 1
    
    oPT.PivotFields("Machine").Orientation = xlColumnField
    oPT.PivotFields("Machine").Position = 1
    
    set oPT = nothing

    'Create Chart
    appXL.ActiveSheet.Shapes.AddChart.Select
    appXL.ActiveChart.SetSourceData Source:=Range("'Sheet1'!$A$1:$G$14")
    appXL.ActiveWorkbook.ShowPivotChartActiveFields = True
    appXL.ActiveChart.ChartType = xlColumnClustered

    appXL.ActiveChart.ChartType = xlLineMarkers
    appXL.ActiveChart.ApplyLayout (5)
    appXL.ActiveChart.Axes(xlValue).AxisTitle.Select
    appXL.Selection.Delete
    appXL.ActiveSheet.ChartObjects("Chart 1").Activate
    appXL.ActiveChart.ChartTitle.Select
    appXL.Selection.Caption = "Jam Ups Per Month"
    

    appXL.ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"

    appXL.ActiveWorkbook.Close savechanges:=True, FileName:=filePath & "\01-" & strMonth & strYear & "-JamUps Per Month.xlsx"
    
    appXL.Application.Quit
    Set appXL = Nothing
    
    MsgBox "Jam Ups has been printed and saved as an Excel file."
    
    
End Sub

Open in new window

You have unqualified references to activesheet and range (and probably others) in the code, hence your error. All that selecting is also unnecessary.
Also, I assume you have a reference set to Excel sin e you are using excel constants in the code? If so, then you should declare xlapp as Excel.application rather than Object.
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jrogersok
jrogersok

ASKER

Thanks for all your suggestions.  I had to switch gears this week but am back to looking at this so will test the solutions.

One question -- Is there a reason that with undeclared objects to the pivot table and sheet why it work the first time through flawlessly, but not the second?   I'm pretty new to working with object variables so am trying to learn as much as possible.

thanks for your comments and help!
You create implicit references to the objects that you can't control, which means that the next time you run the code you have no way of knowing which object you are actually manipulating. You also end up with orphaned instances of the other application running invisibly.
Thanks for the explanation.
rorya - yours worked perfectly the first time. Thanks!

Now for another question related to this -- I will be using the same process pretty much for about 20 other pivot tables and chart in 20 other files.    Would it be ok to move the variables to the top of the module so they can be reused by each of the 20 procedures?

Thanks,
Joan
Yes - as long as you properly qualify all the Excel objects you use, and make sure you destroy them when done, it doesn't really matter where they are declared.
Thanks.  Another question:

On another export I have a page field and I have written some code to loop through it but it's not refreshing the chart's data.   I'm not that familiar with manipulating pivot tables/charts with vba so a little assistance is needed.    Here's my code:

         pt.PivotFields("Work Center").ClearAllFilters
       
       For Each ptf In pt.PageFields
         
          For Each pti In pt.PivotFields(ptf.Name).PivotItems
           
            pt.PivotFields("Work Center").CurrentPage = pti
            pt.GetPivotData
            pt.RefreshTable
            appXL.ActiveSheet.ChartObjects("Chart 1").Activate
            appXL.ActiveChart.ChartTitle.Caption = pti & " Make Ready Per Month"
            appXL.ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
          Next pti
       Next ptf

I'm getting an application defined or object defined error on the line:
pt.pivotfields("Work Center").CurrentPage = pti

The title caption is changing for each of the items in the page filter, however, the table and chart are not refreshing.

Thanks again!
Thanks to you!  It worked great!