Hello good VBA folks,
I've searched/reviewed this forum and many other pages on the web. I find lots of people who have had the same issue, but every fix I've seen that takes care of someone's problem like this does not seem to work for me. My code does what I need for it to do (make the top line of each spreadsheet be the field names) and I can use it as-is, but I just can't make Excel close in Task Manager. I think I've tried about every web suggestion I've found, to no avail. If anyone has time to review my code and offer suggestions for me to try, I'm very interested to figure this out.
Thanks!
Notes:
[ul]
[li]Because this Access app is going between users of Access2010 and Access2013, based on a web find, I change to "late binding" (i.e., Dimming as Object rather than Excel.Application) to eliminate a Library Reference issue between the 2 versions.[/li]
[li]On thing I have determined is that if I run the code to Set xlApp = New Excel.Application and then jump immediately to xlApp.Quit, when it runs past Set xlApp = Nothing, this instance of Excel closes in Task Manager. As suggested by another web article, this prompted me to research and insert code to test to see if one of the Workbooks was still open (note that code in yellow below); but this code indicated no open Workbook(s).[/li]
[li]Based on another web article, I also discovered that, if I don't run xlApp.Quit, Excel (with no Workbook displaying) remains visible. When I then manually close Excel, *this* instance of Excel disappears from Task Manager (the desired effect except that I want to handle it with VBA).[/li]
[li]I also found a web article saying Excel would disappear from Task Manager when MSAccess was closed, but I did *not* find that to be the case.[/li]
[/ul]
Key:
I have added and moved things around and around trying to get Excel to close via VBA. To help distinguish code from comments below for your review:
[ul]
[li]Currently running code is in black.[/li]
[li]Comments are in [highlight #8AE234]green[/highlight] or [highlight #FCE94F]yellow[/highlight] highlight.[/li]
[li]Code that has been tried and commented out is in brown.[/li]
[/ul]
[pre]
Public Sub sRemovePSQCountRow()
[highlight #8AE234]'To bypass Excel 14.0 vs 15.0 Ref Lib between 2010 and 2013 vers of Access...
''LATE BINDING: [/highlight]
Dim strExcelFilePath As String
'Dim xlApp As Excel.Application
'Dim xlWB As Excel.Workbook
'Dim xlSheet As Excel.Worksheet
Dim xlApp As Object [highlight #8AE234]'To bypass Excel 14.0 vs 15.0 Ref Lib between 2010 and 2013 vers of Access...[/highlight]
Dim xlWB As Object [highlight #8AE234]'Ditto[/highlight]
Dim xlSheet As Object [highlight #8AE234]'Ditto[/highlight]
Dim intPass As Integer
Dim sKill As String
Dim intWBCnt As Integer
[highlight #8AE234]'We need to delete the count row in all of the fresh PSQ data Excel files.
'We'll do that by looping through the row-delete process for each file.[/highlight]
[highlight #8AE234]'First we open Excel
'Either of the two Sets seems to open Excel. What I can't do is get it closed at the end.[/highlight]
'Set xlApp = CreateObject("Excel.Application")
Set xlApp = New Excel.Application
'GoTo CompleteProcess:
xlApp.Visible = True
[highlight #8AE234]'Loop through the process for all files (chg 4 to however many files you want to loop through)[/highlight]
For intPass = 1 To 4
If intPass = 1 Then
strExcelFilePath = "I:\GIA\FromAthletics\NC_FA_GIA_ACCESSAPP_CLSLVL_AY.xls"
ElseIf intPass = 2 Then
strExcelFilePath = "I:\GIA\FromAthletics\NC_FA_GIA_ACCESSAPP_CLSLVL_SS.xls"
ElseIf intPass = 3 Then
strExcelFilePath = "I:\GIA\FromAthletics\NC_FA_GIA_ACCESSAPP_AWARDS.xls"
ElseIf intPass = 4 Then
strExcelFilePath = "I:\GIA\FromAthletics\NC_FA_GIA_ACCESSAPP_UNAPPLIED.xls"
End If
[highlight #8AE234]'We first need to make sure something didn't go wrong with the Cybermation process to[/highlight]
[highlight #8AE234]' run each PSQ so that the PSQ Excel doesn't exist. If it did go wrong, we'll[/highlight]
[highlight #8AE234]' present a message to investigate and then get out of this sub.[/highlight]
If Len(Dir(strExcelFilePath)) = 0 Then
MsgBox strExcelFilePath & " is missing! Note the missing file name and " & _
"contact Mary E. to investigate.", vbOKOnly, ">>>Problem!<<<"
GoTo CompleteProcess
Else
[highlight #8AE234]'If the Excel file exists, we'll move on with deleting the count row.[/highlight]
'Set xlWB = xlApp.Workbooks.Open(strExcelFilePath, True, False)
Set xlWB = xlApp.Workbooks.Open(strExcelFilePath, , False)
[highlight #8AE234]'Appears xlSheet is unnecessary if just looking at Sheet 1. Leave this so I will know how to do other than Sheet 1.[/highlight]
Set xlSheet = xlWB.Worksheets(1)
[highlight #8AE234]'Test to see if 1st row looks like the count row before deleting it[/highlight]
If Not IsEmpty(xlSheet.Range("A1")) And (Not IsEmpty(xlSheet.Range("B1")) And xlSheet.Range("B1").Value > -1) And IsEmpty(xlSheet.Range("C1")) And Not IsEmpty(xlSheet.Range("A2")) Then
xlSheet.Rows(1).EntireRow.Delete
End If
[highlight #8AE234]'Close and save the .xls file regardless of whether or not we made changes[/highlight]
xlWB.Close True
Set xlSheet = Nothing
Set xlWB = Nothing
[highlight #8AE234]'If we've handled the last PSQ Excel file, close Excel and we're done. If not,
' increment the Pass number and do the process for the next Excel file.[/highlight]
End If
Next intPass
CompleteProcess:
[highlight #FCE94F]'Attempting to figure out why Task Mgr won't let go of Excel.
'This indicates xlWB has no Fullname (i.e., no workbooks are still open. Then why won't Excel end??)[/highlight]
intWBCnt = 0
For Each xlWB In xlApp.Workbooks
intWBCnt = intWBCnt + 1
'MsgBox xlWB.FullName
xlWB.Save
xlWB.Close
Next xlWB
'MsgBox intWBCnt & " workbooks are open."
[highlight #FCE94F]'Quit Excel <-- This doesn't seem to work. Task Mgr still shows Excel running.[/highlight]
xlApp.Quit
Set xlApp = Nothing
[highlight #FCE94F]'Kill all instances of Excel still running. It kills Excel, but can't use this as it might kill unrelated spreadsheets that the user wants left open.[/highlight]
'sKill = "TASKKILL /F /IM excel.exe"
'Shell sKill, vbHide
End Sub
[/pre]
I've searched/reviewed this forum and many other pages on the web. I find lots of people who have had the same issue, but every fix I've seen that takes care of someone's problem like this does not seem to work for me. My code does what I need for it to do (make the top line of each spreadsheet be the field names) and I can use it as-is, but I just can't make Excel close in Task Manager. I think I've tried about every web suggestion I've found, to no avail. If anyone has time to review my code and offer suggestions for me to try, I'm very interested to figure this out.
Thanks!
Notes:
[ul]
[li]Because this Access app is going between users of Access2010 and Access2013, based on a web find, I change to "late binding" (i.e., Dimming as Object rather than Excel.Application) to eliminate a Library Reference issue between the 2 versions.[/li]
[li]On thing I have determined is that if I run the code to Set xlApp = New Excel.Application and then jump immediately to xlApp.Quit, when it runs past Set xlApp = Nothing, this instance of Excel closes in Task Manager. As suggested by another web article, this prompted me to research and insert code to test to see if one of the Workbooks was still open (note that code in yellow below); but this code indicated no open Workbook(s).[/li]
[li]Based on another web article, I also discovered that, if I don't run xlApp.Quit, Excel (with no Workbook displaying) remains visible. When I then manually close Excel, *this* instance of Excel disappears from Task Manager (the desired effect except that I want to handle it with VBA).[/li]
[li]I also found a web article saying Excel would disappear from Task Manager when MSAccess was closed, but I did *not* find that to be the case.[/li]
[/ul]
Key:
I have added and moved things around and around trying to get Excel to close via VBA. To help distinguish code from comments below for your review:
[ul]
[li]Currently running code is in black.[/li]
[li]Comments are in [highlight #8AE234]green[/highlight] or [highlight #FCE94F]yellow[/highlight] highlight.[/li]
[li]Code that has been tried and commented out is in brown.[/li]
[/ul]
[pre]
Public Sub sRemovePSQCountRow()
[highlight #8AE234]'To bypass Excel 14.0 vs 15.0 Ref Lib between 2010 and 2013 vers of Access...
''LATE BINDING: [/highlight]
Dim strExcelFilePath As String
'Dim xlApp As Excel.Application
'Dim xlWB As Excel.Workbook
'Dim xlSheet As Excel.Worksheet
Dim xlApp As Object [highlight #8AE234]'To bypass Excel 14.0 vs 15.0 Ref Lib between 2010 and 2013 vers of Access...[/highlight]
Dim xlWB As Object [highlight #8AE234]'Ditto[/highlight]
Dim xlSheet As Object [highlight #8AE234]'Ditto[/highlight]
Dim intPass As Integer
Dim sKill As String
Dim intWBCnt As Integer
[highlight #8AE234]'We need to delete the count row in all of the fresh PSQ data Excel files.
'We'll do that by looping through the row-delete process for each file.[/highlight]
[highlight #8AE234]'First we open Excel
'Either of the two Sets seems to open Excel. What I can't do is get it closed at the end.[/highlight]
'Set xlApp = CreateObject("Excel.Application")
Set xlApp = New Excel.Application
'GoTo CompleteProcess:
xlApp.Visible = True
[highlight #8AE234]'Loop through the process for all files (chg 4 to however many files you want to loop through)[/highlight]
For intPass = 1 To 4
If intPass = 1 Then
strExcelFilePath = "I:\GIA\FromAthletics\NC_FA_GIA_ACCESSAPP_CLSLVL_AY.xls"
ElseIf intPass = 2 Then
strExcelFilePath = "I:\GIA\FromAthletics\NC_FA_GIA_ACCESSAPP_CLSLVL_SS.xls"
ElseIf intPass = 3 Then
strExcelFilePath = "I:\GIA\FromAthletics\NC_FA_GIA_ACCESSAPP_AWARDS.xls"
ElseIf intPass = 4 Then
strExcelFilePath = "I:\GIA\FromAthletics\NC_FA_GIA_ACCESSAPP_UNAPPLIED.xls"
End If
[highlight #8AE234]'We first need to make sure something didn't go wrong with the Cybermation process to[/highlight]
[highlight #8AE234]' run each PSQ so that the PSQ Excel doesn't exist. If it did go wrong, we'll[/highlight]
[highlight #8AE234]' present a message to investigate and then get out of this sub.[/highlight]
If Len(Dir(strExcelFilePath)) = 0 Then
MsgBox strExcelFilePath & " is missing! Note the missing file name and " & _
"contact Mary E. to investigate.", vbOKOnly, ">>>Problem!<<<"
GoTo CompleteProcess
Else
[highlight #8AE234]'If the Excel file exists, we'll move on with deleting the count row.[/highlight]
'Set xlWB = xlApp.Workbooks.Open(strExcelFilePath, True, False)
Set xlWB = xlApp.Workbooks.Open(strExcelFilePath, , False)
[highlight #8AE234]'Appears xlSheet is unnecessary if just looking at Sheet 1. Leave this so I will know how to do other than Sheet 1.[/highlight]
Set xlSheet = xlWB.Worksheets(1)
[highlight #8AE234]'Test to see if 1st row looks like the count row before deleting it[/highlight]
If Not IsEmpty(xlSheet.Range("A1")) And (Not IsEmpty(xlSheet.Range("B1")) And xlSheet.Range("B1").Value > -1) And IsEmpty(xlSheet.Range("C1")) And Not IsEmpty(xlSheet.Range("A2")) Then
xlSheet.Rows(1).EntireRow.Delete
End If
[highlight #8AE234]'Close and save the .xls file regardless of whether or not we made changes[/highlight]
xlWB.Close True
Set xlSheet = Nothing
Set xlWB = Nothing
[highlight #8AE234]'If we've handled the last PSQ Excel file, close Excel and we're done. If not,
' increment the Pass number and do the process for the next Excel file.[/highlight]
End If
Next intPass
CompleteProcess:
[highlight #FCE94F]'Attempting to figure out why Task Mgr won't let go of Excel.
'This indicates xlWB has no Fullname (i.e., no workbooks are still open. Then why won't Excel end??)[/highlight]
intWBCnt = 0
For Each xlWB In xlApp.Workbooks
intWBCnt = intWBCnt + 1
'MsgBox xlWB.FullName
xlWB.Save
xlWB.Close
Next xlWB
'MsgBox intWBCnt & " workbooks are open."
[highlight #FCE94F]'Quit Excel <-- This doesn't seem to work. Task Mgr still shows Excel running.[/highlight]
xlApp.Quit
Set xlApp = Nothing
[highlight #FCE94F]'Kill all instances of Excel still running. It kills Excel, but can't use this as it might kill unrelated spreadsheets that the user wants left open.[/highlight]
'sKill = "TASKKILL /F /IM excel.exe"
'Shell sKill, vbHide
End Sub
[/pre]