Excel not ending after launching through Access VB code

[ritco]

I have a datasheet form that exports as an Excel file (through an embedded macro on a button click event). I then call a function (in that same macro) that opens the Excel and the file and applies conditional formatting and subtotaling to it.
This works fine except that after I close the Excel file, an Excel process is left stuck in my task manager, so when I try to run the click event again for another customer, Excel won't open but the system thinks I have it open and it thinks the file I closed out of is still open.
I have tried a couple of statements in my code to close or clear the Excel program but nothing is working. Very frustrating since the rest of the code works fine. Anyone know how to fix this?
Here's my code:
Function FormatDataSht()
Dim xlApp As Object
Dim strPath As String
Dim myMsg As String
strPath = "\\SYSPRO\Exports\SalesHistByItemPc.xlsx"
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

With xlApp
Workbooks.Open FileName:=strPath

Columns("I:I").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Columns("M:M").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Columns("A:M").Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8, 10 _
, 11, 12), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With

'Set xlApp = Nothing
xlApp.Application.ActiveWorkbook.Save
xlApp.Quit

End Function

 

[pbaldy]

http://blogs.office.com/b/microsoft...-objects-when-referring-to-excel-in-code.aspx

 

[ritco]

Thanks for the link. I changed my code as suggested in the blog and am still having the same problem. It runs fine the first time but errors out the second time around.
I get a “Run-Time error ‘91’: Object variable or With block variable not set”
The blog link says this will happen if the objects are not explicitly referenced so I did this as shown below.
My worksheet file is closing but an instance of the Excel application is staying open which I can see in task manager.
Am I still doing something wrong here or is this a problem for Access?

Function test()
Dim objXL As Excel.Application
Dim xlWB As Excel.Workbook
Set objXL = New Excel.Application
Set xlWB = objXL.Workbooks.Open("\\SYSPRO\Exports\SalesHistByItemPc.xlsx")


With xlWB
xlWB.ActiveSheet.Columns("I:I").Select

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

xlWB.ActiveSheet.Columns("M:M").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
xlWB.ActiveSheet.Columns("A:M").Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8, 10 _
, 11, 12), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

End With
'objXL.Visible = True
objXL.Application.ActiveWorkbook.Save
objXL.Application.ActiveWorkbook.Close ("\\SYSPRO\Exports\SalesHistByItemPc.xlsx")
objXL.Application.Quit


End Function

 

[pbaldy]

If you weren't using the With block, wouldn't your code look like this?

xlWB.Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

You have no "." before Selection, which I suspect is the source of your problem. It means you're not using xlWB.

 

[Rx_]

Been there done that before. If you check your process threads, there is a orphaned Excel still open.
The Selection(s) needs a dot in front.
Here are a couple of random code segments examples.
Lets say on line 2040 that I forgot a dot in front of Selection
That will spawn a new instance of Excel. If it were just the one single instance, the code might actually run to the end the first time.
Then when the user runs the code the 2nd time, Error 91 would appear.

All of my reports use a Access 2010 front-end to draw data from SQL Server 2008 and create very custom Excel reports with custom formulas and other features. It just takes one missing dot for an object qualifier. The debugger won't find it. Destroying the orginial Excel object variable leaves the orphan out there. And we don't need to read the Tale of Two Cities to know how much trouble an unattended orphan can create.

770     ObjXL.Range("A" & intRowPos - 1 & ":D" & intRowPos - 1).Select
780     ObjXL.Selection.Font.Bold = True
790     With ObjXL.Selection.Font
800         .Name = "Arial"
810         .Size = 12
820         .ThemeColor = xlThemeColorLight1
830      End With
 
840      With ObjXL.Selection.Borders(xlEdgeBottom)
850         .LineStyle = xlContinuous
860           .ColorIndex = 0
870           .TintAndShade = 0
880         .Weight = xlMedium
890     End With

1930  With ObjXL
1940      .Range("I5").Select
1950      .ActiveCell.FormulaR1C1 = "State Type"
1960      .Range("J5").Select
2030      .Columns("J:J").ColumnWidth = 13.29
2040      With .Selection
2050          .HorizontalAlignment = xlGeneral
2060          .VerticalAlignment = xlBottom
2070          .WrapText = True
2080          .MergeCells = False
2090      End With
2100      .Columns("I:L").Select
2110      .Range("I2").Activate
2120      .Selection.ColumnWidth = 11.29
          '.Application.Left = -36.5   ' WARNING <- this will fail!
          '.Application.Top = 0.25
2130      .Range("M5:P5").Select
2140      With .Selection
2150          .HorizontalAlignment = xlGeneral
2160          .VerticalAlignment = xlBottom
2170          .WrapText = True
2180          .Orientation = 0
2190          .ReadingOrder = xlContext
2200          .MergeCells = False
2210      End With
2220      .Columns("L:P").Select
2230      .Range("L2").Activate
2240      .Selection.ColumnWidth = 10.86
2250      .Selection.ColumnWidth = 12
2260      .Range("O5").Select
2270      .ActiveCell.FormulaR1C1 = "Completion Report Due"
2280      With .ActiveCell.Characters(Start:=1, Length:=21).Font
2290          .Name = "Arial"
2300          .FontStyle = "Bold"
2310          .Size = 12
2320          .ThemeColor = xlThemeColorLight1
2330          .TintAndShade = 0
2340          .ThemeFont = xlThemeFontNone
2350      End With
2360      .Columns("O:O").Select
2370      .Selection.ColumnWidth = 13.43
2380      .Range("M5").Select
2390      .Columns("M:M").ColumnWidth = 11.14
2400      .Range("I5:J5").Select
2410      With .Selection.Interior
2420    .Pattern = xlSolid
2430    .PatternColorIndex = xlAutomatic
2440    .ThemeColor = xlThemeColorLight2
2450    .TintAndShade = 0.799981688894314
2460    .PatternTintAndShade = 0
2470      End With
2480      .Range("K5:L5").Select
2490      With .Selection.Interior
2500          .Pattern = xlSolid
2510          .PatternColorIndex = xlAutomatic
2520          .Color = 13434879
2530          .TintAndShade = 0
2540          .PatternTintAndShade = 0
2550      End With
2560      .Range("M5").Select
2570      .Range("A5:H5").Select
2580      .Range("H5").Activate
2590      With .Selection
2600          .VerticalAlignment = xlBottom
2610          .WrapText = True
2620          .Orientation = 0
2630          .AddIndent = False
2640          .ShrinkToFit = False
2650          .ReadingOrder = xlContext
2660          .MergeCells = False
2670      End With
2680      .Range("A5").Select
 
2690  End With

 

[ritco]

You guys are great! Thanks so much! Works now!

 

[pbaldy]

Happy to help!

 

[ritco]

Here is my working code for anyone else who might need help:

Function test()
Dim objXL As Excel.Application
Dim xlWB As Excel.Workbook
Set objXL = New Excel.Application
Set xlWB = objXL.Workbooks.Open("\\Path\Filename.xlsx")

With objXL
.Columns("I:I").Select

.Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
.Selection.FormatConditions(.Selection.FormatConditions.Count).SetFirstPriority
With .Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
.Selection.FormatConditions(1).StopIfTrue = False

.Columns("M:M").Select
.Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=0"
.Selection.FormatConditions(.Selection.FormatConditions.Count).SetFirstPriority
With .Selection.FormatConditions(1).Font
.Color = -16776961
.TintAndShade = 0
End With
.Selection.FormatConditions(1).StopIfTrue = False
.Columns("A:M").Select
.Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(6, 7, 8, 10 _
, 11, 12), Replace:=True, PageBreaks:=False, SummaryBelowData:=True

End With
objXL.Visible = True
Set objXL = Nothing
Set xlWB = Nothing

End Function

 

[LadyDi]

I am having this same problem. However, I can't seem to find the "orphan" that is causing the problem. No matter what I do with this code, there is an Excel process showing in my Task Manager when I am finished (and I do not have Excel open on my computer). I am attaching a text file with my code. Any assistance you can provide, I would really appreciate.

 

[Rx_]

Just breaking it down a little different - to point out what might have left the orphan on your doorstep.

Option Explicit 
dim objExcelApp as Excel.Application 
dim wb as Excel.Workbook  
sub Initialize()    
set objExcelApp = new Excel.Application 
end sub  
sub ProcessDataWorkbook()     
dim ws as Worksheet     
set wb = objExcelApp.Workbooks.Open("path to my workbook")     
set ws = wb.Sheets(1)      
ws.Cells(1,1).Value = "TransAct 565" 
 ' record set object here    ![TransactionID] = ws.Cells(1,1).Value    
 
'Close the workbook     
wb.Close     
set wb = Nothing 
end sub  
sub Release()    
set objExcelApp = Nothing 
end sub

Example:
Note: ObjExcelApp as the application
wb --> uses ObjExcelApp
ws --> uses wb

in your reference:
xlsApp as the application
wb --> uses XLsApp
ws --> uses XLsApp

Your variable names are different which is OK. This is laid out to show the difference.
Hope that helps. There is more than one way to create the applicaiton object. Looks like you are very close.