There isn't enough memory to complete this action

Question

I have a little issue with excel. Everytime I open up a sheet I get this error:

after like 10-15 minutes. I have tried several things listed on the internet, here is a list on the things that I tried: Adding extra memory changing from 32bit to 64bit and reversed, adding virtual memory, Disabling and enabling DDE, changing office versions, also a few registery settings. Now I was wondering if its maybe the macro that I am using that switches between 2 sheets at an interval. At first I thought it was an issue if I pulled them off an share so I wrote a script that copies them over locally but the error still occurs.

Error handler

Dim RunTime1 As Date

Sub MacroSwitch()

     Application.DisplayFullScreen = True

     On Error GoTo Errhandler

     Application.ScreenUpdating = False

     Workbooks.Open Filename:= _
        ThisWorkbook.Path & "\Monteursplanning.extern.xls", ReadOnly:=True
     ActiveWindow.WindowState = xlMaximized

     Exit Sub

Errhandler:
     MsgBox "An error has occurred. The macro will end."

     Application.DisplayFullScreen = False
End Sub

StartSwitchbutton

Sub MacroAutoRun1() 

     Application.DisplayFullScreen = True



RunTime1 = Now + TimeValue("00:01:00")


    Application.OnTime RunTime1, "MacroAutoRun1"


    If IsWbOpen("Monteursplanning.extern.xls") Then

         Application.ScreenUpdating = False

         Workbooks.Open Filename:= _
            ThisWorkbook.Path & "\Monteursplanning.intern.xls", ReadOnly:=True
         ActiveWindow.WindowState = xlMaximized

         Windows("Monteursplanning.extern.xls").Activate
         ActiveWindow.Close

         Application.ScreenUpdating = True

    Else

         Application.ScreenUpdating = False



         Workbooks.Open Filename:= _
            ThisWorkbook.Path & "\Monteursplanning.extern.xls", ReadOnly:=True
         ActiveWindow.WindowState = xlMaximized

         Windows("Monteursplanning.intern.xls").Activate
         ActiveWindow.Close

         Application.ScreenUpdating = True

    End If
  End Sub

any thoughts?

I am sorry for the english, I used as much google translate as possible. — Dylan Rozendom, Sep 14 '16 at 07:06
Did you use the Windows Task Manager to have a look at the memory consumption and maybe the number of excel processes? — Seth, Sep 14 '16 at 07:49
@seth Yes I did with and without the error message, but the amount of ram usage and cpu usage remains the same. I monitored it for 15 minutes untill the error occurs but no sudden changes. — Dylan Rozendom, Sep 14 '16 at 07:52
How much RAM does the machine have and how big are both works books that are opened every minute if I understand it correctly? Do those books run calculations on their own? — Seth, Sep 14 '16 at 08:04
@seth The 2 sheets are no bigger than 300kb and from what I know, the sheets dont do any form of calculation. Its all static text. — Dylan Rozendom, Sep 14 '16 at 08:08
Are you able to reproduce the error on different machines? You said you did watch excel in the task manager for a bit. How much memory was it using? — Seth, Sep 14 '16 at 08:10
@seth The maximum amount that I have seen it using was about 300-400MB, I will try right now to reproduce the error on a different machine. — Dylan Rozendom, Sep 14 '16 at 08:13
@DylanRz What does `Monteursplanning.extern.xls` do? Look out for any code like `Sheet1.Cells.Copy`. I've seen that work in 2010 only to fail in 2013. Workaround was to use `Sheet1.UsedRange.Copy`. — ThunderFrame, Sep 14 '16 at 11:19
@seth I tried it on multiple machines but they all get the exact same error. — Dylan Rozendom, Sep 15 '16 at 13:40
If you could verify it happens on multiple machines the problem is most likely the document(s) itself/the macros. Verify whenever the planning files contain any macros. If they don't you know the problem is going to be the macro you're using to switch between them. Even if it does look pretty tame. What I don't get is why your ErrorHandler calls the `MacroAutoRun1` and why it doesn't set `ScreenUpdating` to `true` again. — Seth, Sep 16 '16 at 07:10
@seth ow ye you are right, I removed Macroautorun1 it had absolutely no purpose. But what do you mean with screenupdating? — Dylan Rozendom, Sep 16 '16 at 07:44
@seth well even after removing some code here and there the error stills contiunes, The other files do not contain any macro's. — Dylan Rozendom, Sep 16 '16 at 11:07
On the 9th line you `Application.ScreenUpdating = False` in your error handler but you never (in that particular code path) set `Application.ScreenUpdating = True` again. I'm very limited with my knowledge on Excel Macros but just something I noticed. If those two functions are really the only thing running and there is memory available it might be some kind of bug and you might have to get into contact with Microsoft. Maybe make sure all Windows/Office patches are installed beforehand. Not much else I can think of. :/ — Seth, Sep 16 '16 at 11:11
@seth ye I was thinking of maybe use a program or something that switches sheets instead of the use of Visual basic. Do you happen to know any software? Cuz I am starting to think its just the macros that screw up. — Dylan Rozendom, Sep 16 '16 at 11:37
Depends on why you're doing it in the first place. Otherwise I'd probably try to go with some [AutoIt](http://superuser.com/questions/327676/application-to-automatically-switch-between-two-applications-in-windows) or [PowerShell](http://stackoverflow.com/questions/2556872/how-to-set-foreground-window-from-powershell-event-subscriber-action) or maybe consider exporting the sheets as HTML and build something on top of that. If I wouldn't be able to just display the information side by side. — Seth, Sep 16 '16 at 11:44
@seth well let me scetch the situation for you. Currently we have a television at the front of the office and we want it to keep switching information between 2 workbooks (excel), from a share that keeps getting edited alot. This way we will always have the recent information. It pretty much works with this code but the error is ofcourse the issue. any alternative maybe? — Dylan Rozendom, Sep 16 '16 at 11:49

score 0 · Answer 1 · answered Sep 16 '16 at 12:57

You could go for an AutoIt Script that periodically reopens the workbook or you could do it with a DataSource in your "Display" file and some code to switch between the sheets of that file.

Setup a connection for the excel file using Data => Existing Connection/Connections => Other Elements (on the bottom) and selecting your Monteurplannung.intern.xls add the Data to your first sheet. Be sure to check the properties to setup an auto refresh and edit the definition to replace Mode=Share Deny Write with Mode=Read to make sure the file(s) are opened as read only.
Setup a second connection for the other file and display that data on the second sheet. The options let you specify a refresh interval.

All based on the information in this help topic.

After that you'll have to setup an other macro to switch between those sheets. You can recycle part of your code for that.

Sub MacroAutoRun1()
    RunTime1 = Now + TimeValue("00:00:01")

    Application.OnTime RunTime1, "MacroAutoRun1"

    If ActiveSheet.Name = "Tabelle1" Then
        ThisWorkbook.Sheets("Tabelle2").Activate
    Else
        ThisWorkbook.Sheets("Tabelle1").Activate
    End If
End Sub

It works I think. There is a little issue however everytime it closes it pretty much force closes. The thing that happends is It gets the file recovery panel in the left everytime it shuts and opens. Anyway around this? Or disable the recovery panel? I tried disabling autosave but that didnt work. — Dylan Rozendom, Sep 16 '16 at 13:19

There isn't enough memory to complete this action

1 Answers1