Out of memory error when closing workbook opened by vba application

[bdautrich]

Working on my first Excel VBA application. The application tracks inventory and orders for my son's business. We went live with application yesterday and things are working fine. But I am working on an update to the application. When we want to update the application, we need to copy the inventory and order "databases" stored on worksheets in the application excel file to the new application version. When closing the old version file, I get an out of memory error. Here is a snippet of the VBA code

Private Sub cmbCopydb_Click()
Dim Filename As String
Dim msgValue As VbMsgBoxResult
Dim iRow As Integer
Dim OldApp As Workbook
Dim NewApp As Workbook

' user wants to copy databases from old application file

' Create filename
Filename = "C:\Users\" & Application.UserName & "\Desktop\Inventory App Demo3.xlsm"

' Check if files exists
If Dir(Filename) = "" Then
' File does not exists
MsgBox ("Old application file ( " & Filename & " ) does not exist, locate file and try again")
Exit Sub
End If

' Confirm user wants to copy databases
msgValue = MsgBox("Do you want to copy the databases from " & Filename, vbYesNo + vbInformation, "Confirmation")
If msgValue = vbNo Then
Exit Sub
End If

' Store reference to New App workbook
Set NewApp = ActiveWorkbook

' Hide current user form to avoid modal form error when opening old version workbook
Me.Hide

' Open old version workbook and make invisible
Set OldApp = Workbooks.Open(Filename)
ActiveWindow.Visible = False

' Turn off display alerts
Application.DisplayAlerts = False

' Copy inventory by deleting current sheet in new app and then copying from old app
NewApp.Worksheets("Inventory").Delete
OldApp.Worksheets("Inventory").Copy Before:=NewApp.Worksheets("Orders")

ActiveWindow.Visible = True
MsgBox ("Making app Visible")

' Turn alerts back on
Application.DisplayAlerts = True

' Close old application if open
If Len(Application.Workbooks("Inventory App Demo3.xlsm").Name) > 0 Then

'Application gets here error free but next statement causes out of memory error
OldApp.Close SaveChanges:=False
End If

' Now that old app is closed display user form again
Me.Show

' Make the new version the active workbook
NewApp.Activate

End Sub

Any help would be appreciated. Thanks in advance

 

[dmt32]

Hi welcome to forum

try fully releasing your object variable from memory & see if this helps

add the line shown

        'Application gets here error free but next statement causes out of memory error
        OldApp.Close SaveChanges:=False
        
        Set OldApp = Nothing
        
    End If

Dave

 

[bdautrich]

Hi welcome to forum

try fully releasing your object variable from memory & see if this helps

add the line shown

        'Application gets here error free but next statement causes out of memory error
        OldApp.Close SaveChanges:=False
       
        Set OldApp = Nothing
       
    End If

Dave

Thanks that worked. Not sure why but will not argue with success