Jtdylktuy

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?

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