Out of memory error when closing workbook opened by vba application

bdautrich

New Member
Joined
Sep 22, 2022
Messages
19
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi welcome to forum

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

add the line shown
Rich (BB code):
        'Application gets here error free but next statement causes out of memory error
        OldApp.Close SaveChanges:=False
        
        Set OldApp = Nothing
        
    End If

Dave
 
Upvote 0
Solution
Hi welcome to forum

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

add the line shown
Rich (BB code):
        '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
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,187
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top