CODEWITHSUNDEEP
excelvbams-word
btorres
btorres

Reputation: 3

Excel will not fully close after running Word VBA macro

I'm currently trying to make a macro that opens a user defined excel spreadsheet, extracts some data for use in the word document and then closes it. My problem is that when I run the macro, the spreadsheet that I opened is still technically open as a background process in my task manager. I read on another stack overflow question that the reason is because visual basic will not release the reference object from excel until I close out of Microsoft Word. However, even after closing out of Word, the excel background process is still going and I can only stop it by ending the task in the task manager. To clarify, if I run the macro, close Word and then try to open the excel file, I can get in without telling me it's a read only file. However, if I don't close out of Word and I try to go into the spreadsheet after running the macro, then it tells me that it's a read only file. Below is the code I'm using that is causing this problem for me. Thanks to anyone who can help.

Sub UpdateProposal()

'Declares variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim SpreadsheetPath As String
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim ExcelWasNotRunning As Boolean
Dim ProposalInfoArr(1 To 30) As String

'Skips to ErrorHandler if user cancels out of file dialog
On Error GoTo ErrorHandler

'Display a Dialog Box that allows to select a single file.
'The path for the file picked will be stored in SpreadsheetPath variable
With Application.FileDialog(msoFileDialogFilePicker)

    'Makes sure the user can select only one file
    .AllowMultiSelect = False

    'Filter to just the following types of files to narrow down selection options
    .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1

    'Show the dialog box
    .Show

    'Stores in SpreadsheetPath variable
    SpreadsheetPath = .SelectedItems.Item(1)

End With

'If Excel is running, get a handle on it; otherwise start a new instance of Excel
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")

If Err Then
   ExcelWasNotRunning = True
   Set xlApp = New Excel.Application
End If

'If you want Excel to be visible, you could add the line: xlApp.Visible = True here; but your code will run faster if you don't make it visible

'Open the workbook
Set xlBook = xlApp.Workbooks.Open(FileName:=SpreadsheetPath)

'''Extracts Data

'Quits out of Excel if it was not running previous to running the macro.
If ExcelWasNotRunning Then
    xlApp.DisplayAlerts = False
    xlApp.Quit
End If


'Make sure you release object references.
Set xlRange = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

'Ends the macro before the error handler
Exit Sub

'Ends Macro
ErrorHandler:
    MsgBox "The following error occurred: " & Err.Description

End Sub

Upvotes: 0

Views: 1184

Answers (1)

braX
braX

Reputation: 11735

You are defining the objects correctly:

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range

but you forgot about the implicitly used Workbooks object... as most of the answers you will find do... which means it doesn't get released. So do it like this:

Dim SpreadsheetPath As String
Dim xlApp As Excel.Application
Dim xlBooks As Excel.Workbooks
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlRange As Excel.Range

SpreadsheetPath = "C:\MyPath\MyFile.xlsx"
Set xlApp = New Excel.Application
' Set xlApp = GetObject(, "Excel.Application") ' or attach to an existing one
Set xlBooks = xlApp.Workbooks
Set xlBook = xlBooks.Open(FileName:=SpreadsheetPath) ' you can use .Add instead if someone else may have it open already
Set xlSheet = xlBook.Worksheets(1)
Set xlRange = xlSheet.Range("A1")

' do stuff with the worksheet/range
xlRange.Value = "foo"

' the order matters 
' just like it does 
' when you create the objects
Set xlRange = Nothing
Set xlSheet = Nothing
xlBook.Close False
Set xlBook = Nothing
Set xlBooks = Nothing
xlApp.Quit
Set xlApp = Nothing

However, you may find that it still isn't getting released when you want, but it will get released when you close the program you are using to create it (in your case, MS-Word) as that is (presumably) when Windows does its built-in garbage collection.

Note: I removed the error handling just to keep it a clean example, but you can leave that in

Upvotes: 2

Related Questions