Solved

Access 2007 Object Variable or With Block Not Set

Posted on 2011-02-13
10
849 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34885695
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
ID: 34886068
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
ID: 34886818
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 2

Author Comment

by:jrogersok
ID: 34922571
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
ID: 34923993
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
 
LVL 2

Author Comment

by:jrogersok
ID: 34931109
Thanks for the explanation.
0
 
LVL 2

Author Comment

by:jrogersok
ID: 34945754
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
ID: 34945962
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
ID: 34958020
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
ID: 34992981
Thanks to you!  It worked great!
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 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