Solved

Access 2007 Object Variable or With Block Not Set

Posted on 2011-02-13
10
832 Views
Last Modified: 2012-05-11
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!
 Debug line highlighted in yellow where error occurs
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

0
Comment
Question by:jrogersok
  • 5
  • 4
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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

0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
Something like this:
   Dim appXL As Excel.Application
   Dim wbk As Excel.Workbook
   Dim wksSource As Excel.Worksheet, wksNew As Excel.Worksheet
   Dim rng As Excel.Range
   Dim pt As Excel.PivotTable
   Dim pc As Excel.PivotCache
   Dim cht As Excel.Chart

   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")

   Set wbk = appXL.Workbooks.Open(filePath & "\JamUpsExport.xlsx")
   Set wksSource = wbk.ActiveSheet
   appXL.Visible = True

   'Format Date column as mmm-yy
   wksSource.Columns("C:C").NumberFormat = "[$-409]mmm-yy;@"

   'Set range of data
   Set rng = wksSource.Range("a1").CurrentRegion

   'Create Pivot Table
   Set wksNew = wbk.Sheets.Add

   Set pc = wbk.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng, Version:=xlPivotTableVersion12)
   Set pt = pc.CreatePivotTable(TableDestination:=wksNew.Range("A3"), TableName:="PivotTable1", _
                                DefaultVersion:=xlPivotTableVersion12)

   With pt
      .AddDataField .PivotFields("SumOfWaste Qty"), "Sum of SumOfWaste Qty", xlSum
      .PivotFields("Date").Orientation = xlRowField
      .PivotFields("Date").Position = 1
      .PivotFields("Machine").Orientation = xlColumnField
      .PivotFields("Machine").Position = 1
   End With
   
   'Create Chart
   Set cht = wksNew.Shapes.AddChart.Chart
   With cht
      .SetSourceData Source:=pt.TableRange2
      wbk.ShowPivotChartActiveFields = True
      .ChartType = xlLineMarkers
      .ApplyLayout 5
      .Axes(xlValue).AxisTitle.Delete
      .ChartTitle.Caption = "Jam Ups Per Month"
   End With

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

   wbk.Close savechanges:=True, Filename:=filePath & "\01-" & strMonth & strYear & "-JamUps Per Month.xlsx"

   appXL.Quit
   Set appXL = Nothing

   MsgBox "Jam Ups has been printed and saved as an Excel file."

End Sub

Open in new window

0
 
LVL 2

Author Comment

by:jrogersok
Comment Utility
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!
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Author Comment

by:jrogersok
Comment Utility
Thanks for the explanation.
0
 
LVL 2

Author Comment

by:jrogersok
Comment Utility
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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.
0
 
LVL 2

Author Comment

by:jrogersok
Comment Utility
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!
0
 
LVL 2

Author Closing Comment

by:jrogersok
Comment Utility
Thanks to you!  It worked great!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now