[AccessD] Access to Excel Automation works first time

Jürgen,

    If the problem is still persisting, it might be desirable for you to go through each line of code and make sure that there is no term native to excel that has been used without an explicit object qualifier.

    For example:
    1 - Excel constant xlLineMarkers in following statement should be replaced by its literal value.
    oxlCht.ChartType = xlLineMarkers

    2 - In the statement starting with  oxlCht.SetSourceData,  the portion Sheets("Data") should be replaced by oxlApp.Sheets("Data")

    3 - In the same statement, xlColumns should be replaced by its actual value.

    You might like to check all other statements on similar lines.

    In addition, modifications to exit code, as suggested by Paul in his post of 13-Jul-2009, should be incorporated so as to ensure proper closing (where relevant) and setting to nothing of all excel related objects followed by quitting of excel application and setting it to nothing.

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: Jurgen Welz 
  To: accessd at databaseadvisors.com 
  Sent: Monday, July 13, 2009 11:25
  Subject: [AccessD] Access to Excel Automation works first time

  I've run into an Excel automation problem.  It seems like one of those nagging failure to release an object reference problems.

  What happens is, the first time I run the code, it works perfectly.  If I call the procedure a second time, it errors.  If I then close the Access application, reopen it and then try the code again, it triggers the error.  If I close the access application by running End and then Quit from the immediate window, the code again works the first time I try it after reopening the Access application.

  The code worked perfectly until I added some cleaned up Excel macro recorder code to the Access code that inserted a chart.  If I comment out the chart code, the automation code runs correctly every time.  The code uses a preformatted Excel template and writes a table of information to sheet 2 and then diplays a table based on that data on the first sheet.

  Every object that uses a set statement is set to nothing in the ExitRoutine portion of the error handler in the code.  I can only assume there is some implicit instantiation of an Excel object that I'm not seeing.  Excerpted below, skipping the part that writes the data, is the part of the code that blows up on the 4 th line below where the Source of chart data is set by calling the SetSourceData method of the Chart object variable.  I tried it with a workbook chart object as well as the application chart object and it errors at the same point.  I also replaces all the oxlApp.ActiveChart with:  With oxlCht and with:  With oxlapp.ActiveChart versions of the code.  Same story every way I try it.  The code works the first time and then there is an error message:  Method 'SetSourceData' of object'_Chart' failed.  By the way, I needed to decrease the margins in code because if I don't, a portion of the x axis labeling is cut off.  If I insert the chart with the wider margins and decrease them in the code, the axis numbers are correctly visible.  What am I missing to get this to run without forcing a code end/quit?
   
  Private Sub cmdCashFlowSummary_Click()
      On Error GoTo ErrorHandler
   
      Dim oxlApp As Excel.Application
      Dim oxlWkb As Excel.Workbook
      Dim oxlCht As Excel.Chart

      oxlApp.Sheets("Forecast").Activate
      Set oxlCht = oxlApp.Charts.Add
      oxlCht.ChartType = xlLineMarkers
        'Need C3 to C28 but will delete SeriesCollection(1)
      oxlCht.SetSourceData Source:=Sheets("Data").Range("B3:C28"), PlotBy:= _
      xlColumns
        'get rid of the first series that sets the scale
      oxlCht.SeriesCollection(1).Delete
      oxlCht.SeriesCollection(1).XValues = "={""Weeks""}"
      oxlCht.SeriesCollection(1).Name = _
        "=""Historical Percent Paid by Week After Invoice"""
      oxlCht.Location Where:=xlLocationAsObject, Name:="Forecast"
      oxlApp.ActiveChart.HasTitle = True
      oxlApp.ActiveChart.ChartTitle.Characters.Text = "Percent Paid by Week After Invoice"
      oxlApp.ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = False
      oxlApp.ActiveChart.Axes(xlValue, xlPrimary).HasTitle = False
      oxlApp.ActiveChart.Legend.Select
      oxlApp.Selection.Delete
        'Shape object is the chart.  Shapes(1) is logo
      oxlApp.ActiveSheet.Shapes(2).Top = 250 
      oxlApp.ActiveSheet.Shapes(2).Left = 0
      oxlApp.ActiveSheet.Shapes(2).Width = 516
      oxlApp.ActiveChart.SeriesCollection(1).XValues = "=Data!R3C2:R28C2"
      oxlApp.Range("A1").Select
      oxlApp.ActiveSheet.PageSetup.TopMargin = oxlApp.InchesToPoints(0.5)
      oxlApp.ActiveSheet.PageSetup.BottomMargin = oxlApp.InchesToPoints(0.5)
  ExitRoutine:
      On Error Resume Next
      r.Close
      Set r = Nothing
      oxlApp.Visible = True
      Set oxlCht = Nothing
      Set oxlWkb = Nothing
      Set oxlApp = Nothing
      Exit Sub
  ErrorHandler:
      With Err
          Select Case .Number
              Case Else
                  MsgBox .Number & vbCrLf & .Description, vbInformation, "Error - " & _
                    "frmCashFlow.cmdCashFlowSummary_Click"
          End Select
      End With
      Resume 0
      Resume ExitRoutine
  End Sub

  Ciao 
  Jürgen Welz 
  Edmonton, Alberta 
  jwelz at hotmail.com