Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access 2007 Object Variable or With Block Not Set

Posted on 2011-02-13
10
Medium Priority
?
866 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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…

604 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