Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel not quitting - How to avoid global reference?
I'm using Access 2000 to create charts in Excel. I've read about the
problem Excel not quitting due to a global reference to the automated application at http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/ So I've tried to reference objects explicitly. For example: With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) .[do stuff] End With But Excel still does not quit. Sometimes I need to assign the return value of a method to an object - when creating a chart or adding a series, for example: Dim objChart as Object Set objChart = xlapp.Workbooks(strXlsFile). _ Worksheets(sn(i)).ChartObjects.Add(Left:=100, Top:=24, _ Width:=650, Height:=500).Chart With ObjChart .[do stuff] End with Dim objSeries as Object Set objSeries = objChart.SeriesCollection.NewSeries With objSeries .[do stuff] End With Is this why Excel is not quitting? Do either of the above two examples constitute a global reference? Can this code be written so it does not create a global reference? Could there be some other reason why Excel is not quitting? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel not quitting - How to avoid global reference?
Hello,
You haven't shown a few important details in your post, including: 1) How you have declared (scope-wise) xlApp. Is the variable declared within the procedure or outside of the procedure? 2) Where you attempt to Quit the Application. I.e., xlApp.Quit Yes, you certainly can create an Excel Application variable with scope limited to the procedure, it would be declared similarly as you've declared: Dim objChart as Object Within the procedure. Regards, Nate Oliver |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel not quitting - How to avoid global reference?
You haven't shown a few important details in your post, including:
1) How you have declared (scope-wise) xlApp. Is the variable declared within the procedure or outside of the procedure? 2) Where you attempt to Quit the Application. I.e., Thanks for the reply. Here's an abbreviated version of the function in question. The function is called from a button on an Access form. As you can see, everything is within scope of the function. The two objects I'm wondering about are objChart and objSeries - each are set using the full path "xlapp..." Could either of those objects create a global reference? Should I look elsewhere for the cause of the "Excel not quitting" problem? Public Function CreateWorksheets() As Boolean Dim xlapp As Excel.Application For i = 1 To 50 With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) lc = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row .Cells(1).EntireRow.HorizontalAlignment = xlCenter .Range("D2:D" & lr).Formula = "=STDEV(E2:AH2)" .Columns.AutoFit End With Set objChart = xlapp.Workbooks(strXlsFile).Worksheets _ (sn(i)).ChartObjects.Add _ (Left:=100, Top:=24, Width:=650, Height:=500).Chart With objChart .SetSourceData Source:=xlapp.Workbooks(strXlsFile). _ Worksheets(sn(i)).Range("A1:B" & xlapp.Workbooks _ (strXlsFile).Worksheets(sn(i)).Range("B1").End(xlD own).Row), _ PlotBy:=xlColumns .ChartType = xlLineMarkers .Parent.Name = sn(i) .HasLegend = False .Axes(xlCategory, xlPrimary).HasTitle = False End With j = 3 'data begins in column 4 Do While j < lc - 1 j = j + 1 Set objSeries = xlapp.Workbooks(strXlsFile).Worksheets _ (sn(i)).ChartObjects(1).Chart.SeriesCollection.New Series objSeries.Values = xlapp.Workbooks(strXlsFile).Worksheets _ (sn(i)).Range(xlapp.Workbooks(strXlsFile).Workshee ts _ (sn(i)).Cells(2, j), xlapp.Workbooks _ (strXlsFile).Worksheets(sn(i)).Cells(lr, j)) Loop k = 0 Do While k < objChart.SeriesCollection.Count k = k + 1 With objChart.SeriesCollection(k) .Border.ColorIndex = sm .ErrorBar Direction:=xlY, Include:=xlBoth, _ Type:=xlCustom, Amount:=xlapp.Workbooks(strXlsFile).Worksheets(sn( i)).Range("C2:C" & lr), _ MinusValues:=xlapp.Workbooks(strXlsFile).Worksheet s(sn(i)).Range("C2:C" & lr) End With Loop Next i CreateWorksheets = True xlapp.Workbooks(strXlsFile).Save xlapp.Workbooks(strXlsFile).Close Set objSeries = Nothing Set objChart = Nothing Set xlapp = Nothing xlapp.Quit Exit Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel not quitting - How to avoid global reference?
Forgot to include how I am instantiating the xlapp object...
Public Function CreateWorksheets() As Boolean On Error GoTo HandleErr Dim xlapp As Excel.Application Set xlapp = GetObject(, "Excel.Application") 'gets new instance on error if no current instance exists For i = 1 To 50 With xlapp.Workbooks(strXlsFile).Worksheets(sn(i)) lc = .Range("A1").SpecialCells(xlCellTypeLastCell).Colu mn lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row .Cells(1).EntireRow.HorizontalAlignment = xlCenter .Range("D2:D" & lr).Formula = "=STDEV(E2:AH2)" .Columns.AutoFit End With Set objChart = xlapp.Workbooks(strXlsFile).Worksheets _ (sn(i)).ChartObjects.Add _ (Left:=100, Top:=24, Width:=650, Height:=500).Chart With objChart .SetSourceData Source:=xlapp.Workbooks(strXlsFile). _ Worksheets(sn(i)).Range("A1:B" & xlapp.Workbooks _ (strXlsFile).Worksheets(sn(i)).Range("B1").End(xlD own).Row), _ PlotBy:=xlColumns .ChartType = xlLineMarkers .Parent.Name = sn(i) .HasLegend = False .Axes(xlCategory, xlPrimary).HasTitle = False End With j = 3 'data begins in column 4 Do While j < lc - 1 j = j + 1 Set objSeries = xlapp.Workbooks(strXlsFile).Worksheets _ (sn(i)).ChartObjects(1).Chart.SeriesCollection.New Series objSeries.Values = xlapp.Workbooks(strXlsFile).Worksheets _ (sn(i)).Range(xlapp.Workbooks(strXlsFile).Workshee ts _ (sn(i)).Cells(2, j), xlapp.Workbooks _ (strXlsFile).Worksheets(sn(i)).Cells(lr, j)) Loop k = 0 Do While k < objChart.SeriesCollection.Count k = k + 1 With objChart.SeriesCollection(k) .Border.ColorIndex = sm .ErrorBar Direction:=xlY, Include:=xlBoth, _ Type:=xlCustom, Amount:=xlapp.Workbooks(strXlsFile).Worksheets(sn( i)).Range("C2:C" & lr), _ MinusValues:=xlapp.Workbooks(strXlsFile).Worksheet s(sn(i)).Range("C2:C" & lr) End With Loop Next i CreateWorksheets = True xlapp.Workbooks(strXlsFile).Save xlapp.Workbooks(strXlsFile).Close Set objSeries = Nothing Set objChart = Nothing Set xlapp = Nothing xlapp.Quit HandleErr: Select Case Err.Number Case 429 Set xlapp = CreateObject("Excel.Application") Resume Next [code omitted] Exit Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel not quitting - How to avoid global reference?
Hello again,
Why not use CreateObject in all cases? Versus hijacking and quitting an existing Excel instance. In any case, for starters, try flipping: Set xlapp = Nothing xlapp.Quit To xlapp.Quit Set xlapp = Nothing You're trying to control an object variable which you have terminated from memory. Regards, Nate Oliver |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel not quitting - How to avoid global reference?
Why not use CreateObject in all cases? Versus hijacking and quitting an
existing Excel instance. In any case, for starters, try flipping: Set xlapp = Nothing xlapp.Quit To xlapp.Quit Set xlapp = Nothing You're trying to control an object variable which you have terminated from memory. Great comments. I'll try both suggestions. Thanks for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Global replace a reference to a worksheet | Excel Discussion (Misc queries) | |||
Need to avoid a circular reference... | Excel Worksheet Functions | |||
How to Reference Global Variable for external application. | Excel Programming | |||
Quitting Excel without saving | Excel Programming | |||
Global file reference change | Excel Programming |