excel documents not closing

cjcant · June 28th, 2011, 10:17 AM

I am working in Word 2007 and during the application I use a global function to open an excel document (so that I can transfer data over to my word document).

However, the code I am using doesn't come up with any errors but it doesn't appear to work as it still says the excel workbook is locked by me (so I think it must still be running on my computer in the background).

I have the following code:

Declare Source:

Code:

Function RetrieveExcelData()
    'Parts document (ScopeDeliverablesExclusionsExcelData)
    Set appParts = Excel.Application
    Set wkParts = appParts.Workbooks.Open("H:\proposalDocumentDevelopment\proposalTemplates\excelData.xlsm")

    'Other document (ScopeDeliverablesExclusionsExcelData)
    Set appOther = Excel.Application
    Set wkOther = appOther.Workbooks.Open("H:\proposalDocumentDevelopment\proposalTemplates\testExclusionsDeviationsClarifications.xlsx")

I will not put the transferring data code unless you need it. but it is basically just call function and insert text.

Exit button within all forms :

Code:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    'have a dialogue box asking whether the user is sure they want to close the application
    Dim msgPrompt As String
    Dim msgButtons As Integer
    Dim msgTitle As String
    Dim msgResult As Integer
    
    msgPrompt = "Are you sure you are finished?"
    msgButtons = vbYesNo + vbQuestion + vbDefaultButton2
    msgTitle = "Exit"
    msgResult = MsgBox(msgPrompt, msgButtons, msgTitle)
    
    If CloseMode = vbFormControlMenu Then
        If msgResult = 7 Then
            Cancel = True
        End If
        If msgResult = 6 Then
            Call CloseAll
        End If
    End If
    
End Sub

Close the excel documents (global variable):

Code:

Function CloseAll()
    wkOther.Close xlDoNotSaveChanges
    Unload UserForm1
    wkParts.Close xlDoNotSaveChanges
End Function

Could somebody please help me?
Thanks

Shasur · June 28th, 2011, 01:17 PM

There might be a memory leak as the program still holds the object instance. You can dispose it using

Set wkParts = Nothing

etc

Cheers
Shasur

cjcant · June 29th, 2011, 03:58 AM

Thanks.

It is amazing how the answer can be something so simple.

cjcant · July 19th, 2011, 04:13 AM

Hi,

I thought it had been working but it isn't. I think I maybe tested it with the wrong document.

Do you have any other ideas? The program just does not seem to let it go. Is there maybe some code for closing a document rather than cutting the connection?

Thanks