Simple Refresh Macro will not run whenever a second workbook is opened

  • I am looking for some help getting some simple code to force a refresh to have a workbook fetch data from source workbooks. This excel document then graphs the outside data and displays it in a control room setting. This used to be the sole spreadsheet open on this PC, but now I need to run two sheets, the other I do not want refreshed. I modified the code and now it will not refresh both sheets when open, but opening the second sheet will now kill the macro and the sheet will not refresh. I need this code to run only on one workbook, but not on any other workbooks that happen to be open at the same time. I am testing my code on a dummy sheet until I can get it to run properly, names "RefreshTest". Any hep on this is greatly appreciated!


    Code
    Sub Auto_Open()
        Workbooks("RefreshTest.xlsm").RefreshAll
        Application.OnTime Now + TimeValue("00:00:01"), "OpenMe"
        Workbooks("RefreshTest.xlsm").Close False
    End Sub
    
    Sub OpenMe()
        Application.OnTime Now + TimeValue("00:00:30"), "Auto_Open"
    End Sub
  • Hi,


    Could you explain what you actually mean by "another instance of Excel is opened" ...


    1. Have you opened twice the Excel Application ?

    2. Have you opened two workbooks ?

    3. Have you opened a single workbook (an Excel file ) with two worksheets ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thank you so much for the help


    1. I have open the file I would like refreshed, and have a second file open doing unrelated data entry.

    2. Yes I have two separate workbooks open. As i may not be the best with jargon, these are two separately named files.

    3. Both files have multiple sheets

  • Thanks for your clear explanation :)


    For your info, the word "instance" is often restricted to the Application level ... i.e. two Excel applications running simultaneously ;)


    So basically, you are operating with two opened workbooks ...( both of them happen to hold several worksheets...)


    Do we agree on this point ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Yes, we are in agreement there. My apologies for the confusion, I am extremely limited with the macro side of excel, and greatly appreciate your attention on this.

  • My apologies, I need to go inactive for a bit as I have a meeting to attend. I will check in here again as soon as I am available. Again I very appreciate your time and attention on this.

  • Great ... :thumbup:


    Given your environment, you will agree, for sure, that Excel needs to exactly identify

    which worksheet within which workbook does require the action you need to perform


    This does add a constraint to your code ... be extremely specific by using, each time, the full name:

    The Workbook Name AND the Worksheet Name

    e.g.

    Workbooks("RefreshTest.xlsm").Worksheets ("MyData")


    Hope this clarifies

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks again for your help, I made some modifications to the code, but unfortunately I seem to be running into the same problem. I am able to get the code to run as expected when the only Excel file that is open is "RefreshTest.xlsm" but as soon as I open any other excel file such as "Book1.xls" the macro ceases to run and the "RefreshTest.xlsm" sheet no longer closes and opens to force the sheet to update data links. I apologize if I am missing something obvious here.


    I also need the refresh to run on all sheets of the "RefreshTest" file so I am not sure I want to restrict it to a single sheet, but I may just be missing something.


    Code
    Sub Auto_Open()
        Workbooks("RefreshTest.xlsm").Worksheets("Sheet1").Calculate
        Workbooks("RefreshTest.xlsm").Worksheets("Sheet1").Application.OnTime Now + TimeValue("00:00:01"), "OpenMe"
        Workbooks("RefreshTest.xlsm").Close False
    End Sub
    
    Sub OpenMe()
        Workbooks("RefreshTest.xlsm").Worksheets("Sheet1").Application.OnTime Now + TimeValue("00:00:30"), "Auto_Open"
    End Sub
  • Let's tackle your initial objective to refresh the workbook "RefreshTest.xlsm"


    Can you explain what this workbook consists of in terms of data, links, etc ....

    and why there is apparently a need for a full update every second ?


    Are you after updating forex rates or stock prices ? Is there an RTD (real time data) function involved ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Carim

    Changed the title of the thread from “Simple Refresh Macro will not run when another instance of excel is open” to “Simple Refresh Macro will not run whenever a second workbook is opened”.
  • Looking at the code posted in your first message ... would suggest to test the following :


    Code
    Private Sub Workbook_Open()
        Application.Run ("RefreshUpdate")
    End Sub
    
    Sub RefreshUpdate()
        ThisWorkbook.RefreshAll
        Application.OnTime Now + TimeValue("00:00:30"), "RefreshUpdate"
    End Sub

    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • This is a file I am using to pull data from several other separate excel files and then display that data in various graphs (Let's call it "Graph.xlsm" and I am trying to have it refreshed every half hour automatically). The data in these source files is manually input at random times by other staff throughout the day. This data is linked with cell references and equations, but is only updated when the "Graph" file is opened or manually refreshed. The people viewing the "Graph" file are not experienced at all in excel, so asking them to periodically refresh the sheet manually will result in the "Graph" file never being updated and they will not be viewing the most recent data.


    None of this is for stock prices and there is no RTD. I used the 30 second refresh rate as a place holder while I am trying to get the code to do what I want. When the code is correct I plan to have the refresh run every 30 mins, in order to ensure they are viewing the most up to date data from a laboratory.

  • OK. Thanks for the explanation. ;)


    Would suggest to test the two macros posted in message #10

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks, i will give those a shot and see if that will resolve my error. The 1 second timer was a trigger for the other macro but I believe that was what was causing the failure when any other sheet was opened. Ill give this a shot now. Thank you again.

  • You are welcome.


    With your agreement, have modified the initial title of your thread.


    As I understand it now, it is not just a second workbook which prevents your macro from working ....

    but a complete string of various source files ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • I now appear to be getting an "Automation error" which immediately closes the sheet and will not let me debug. I assue I need to put this code in a module? or should this go in the "ThisWorkbook"?

  • Good question !!!


    Sorry for not having added this important indication ... :(


    the first code goes in the module called ThisWorkBook


    Code
    Private Sub Workbook_Open()
        Application.Run ("RefreshUpdate")
    End Sub

    and to be precise and go by the rules, the second macro should go in a standard module


    Code
    Sub RefreshUpdate()
        ThisWorkbook.RefreshAll
        Application.OnTime Now + TimeValue("00:00:30"), "RefreshUpdate"
    End Sub

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • I think I may need to do some more testing but at the moment it does not appear to cause the sheet to pull new data but I may need to work on it a bit more.


    Thank you again

  • You are welcome :)


    Do not hesitate to come back to the Forum ... should the need arises ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!