Excel hangs when using Automation

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Has anyone experienced Excel 97 hanging on them when they
release the object from VBA code? I am using Access 97 to
automate a few things. When my code executes, I try to
open Excel (manually) and it hangs. When I go into the
Task Manager, I find that there is a instance of Excel.
Has anyone had a similar issue? If so, how did you handle
it? Code is below:

Private Sub btn10WeekChart_Click()
On Error GoTo btn10WeekChart_Click_Error

Dim objExcel As Excel.Application, fname As String

Set objExcel = Excel.Application

'Creates a chart based upon the frmCriteria box and
exports the query to a temp table
DoCmd.OpenForm "frmCriteria", , , , , acDialog, "ByPlant"
'DoCmd.DeleteObject acTable, "tmpGloss10Week2"
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrytmp10Week2"
DoCmd.SetWarnings True
fname = GetFileName
DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel97, "tmpGloss10Week2", fname, True

With objExcel
.Workbooks.Open fname
.Cells.Select
.Cells.EntireColumn.AutoFit
.ActiveSheet.Range("D2:D11").Select
.Selection.NumberFormat = "0.00"
.ActiveSheet.Range("B2:B11").Select
With .Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
.ActiveSheet.Range("A1:E11").Select
.Selection.Sort Key1:=Range("E2"),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
.Sheets("tmpGloss10Week2").Select
.Charts.Add
With .ActiveChart
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets
("tmpGloss10Week2").Range("E18")
.SeriesCollection.NewSeries
.SeriesCollection(1).XValues = "=tmpGloss10Week2!
R2C5:R11C5"
.SeriesCollection(1).Values = "=tmpGloss10Week2!
R2C4:R11C4"
.SeriesCollection(1).Name = "=tmpGloss10Week2!R1C4"
.Location Where:=xlLocationAsNewSheet
.HasTitle = True
.ChartTitle.Characters.Text = _
"10-Week " & .Application.Sheets
("tmpGloss10Week2").Range("A2").Value & " % Acceptable
Chart"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory,
xlPrimary).AxisTitle.Characters.Text = "Week End Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue,
xlPrimary).AxisTitle.Characters.Text = _
"Percentage Acceptable"
End With
.ActiveChart.HasLegend = False
.ActiveChart.ApplyDataLabels
Type:=xlDataLabelsShowValue, LegendKey:=False
.ActiveChart.Axes(xlCategory).Select
With .ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = 7
.MajorUnitScale = xlDays
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = True
.ReversePlotOrder = False
End With
.ActiveChart.PlotArea.Select
.ActiveChart.SeriesCollection(1).Select
With .ActiveChart.ChartGroups(1)
.Overlap = -100
.GapWidth = 0
.HasSeriesLines = False
.VaryByCategories = False
End With
.ActiveChart.Axes(xlValue).Select
With .ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScale = 100
.MinorUnitIsAuto = True
.MajorUnit = 10
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
End With
.ActiveChart.SeriesCollection(1).Trendlines.Add
(Type:=xlMovingAvg, Period:=2 _
, Forward:=0, Backward:=0, DisplayEquation:=False,
DisplayRSquared:= _
False).Select
.Sheets("tmpGloss10Week2").Move Before:=Sheets(1)
.ActiveWorkbook.Save
.Workbooks.Close
End With
MsgBox "Your file and chart is saved to " & fname,
vbOKOnly, "File Save Successful"

btn10WeekChart_Click_Exit:
Set objExcel = Nothing
Exit Sub

btn10WeekChart_Click_Error:
MsgBox Err.Number & Chr(13) & Chr(13) & Err.Description
Resume btn10WeekChart_Click_Exit

End Sub
 
Try to make Excel visible using:
objExcel.Visible=true
and then look what there really happens
 
Why should that matter? My problem is that Excel doesn't
close out completely when in the Task Manager it states
that Excel still resides there...
 
Why should that matter? My problem is that Excel doesn't
close out completely when in the Task Manager it states
that Excel still resides there...
Click to expand...

do it and step throught the code, then you will find out why

BTW: it's easy to see in the code, because the code does exactly what
you told to do. If it's not what you want then add a small line to it,
to do what you want that excel is doing after your "end With"
 
Back
Top