0

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

enter image description here

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?

17
  • I am sorry for the english, I used as much google translate as possible. – Dylan Rozendom Sep 14 '16 at 7: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 7: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 7: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 8: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 8:08
0

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
2
  • 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
  • Hm~ sorry, I'm not sure about that. :/ – Seth Sep 17 '16 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.