Automated Nightly Update

Discussion in 'General Questions' started by David Kanof, Nov 13, 2017.

Automated Nightly Update

  1. David Kanof

    David Kanof New Member

    Joined:
    Nov 2, 2017
    Messages:
    14
    Likes Received:
    5
    I have a workbook that I want to update with price information (Last and Change) on a nightly basis. At 11:30 PM I have a scheduled task that copies the workbook to a staging directory and opens the workbook. In the workbook I have VBA code that runs on Open and, if the workbook is in my specially-named directory, does an Application.CalculateFullRebuild, Application.Save, and Application.Quit. This should cause updating with market close data, since we are many hours after the market close.

    It did not seem to be getting proper values last week. The following morning, before the market opened, if I did a Settings->Clear Cache and then the CalculateFullRebuild if seemed to get the correct data.

    Is there a problem with the caching? It would seem that this should work.

    How would you suggest I accomplish this so that it works reliably in an automated fashion?
     
  2. David Kanof

    David Kanof New Member

    Joined:
    Nov 2, 2017
    Messages:
    14
    Likes Received:
    5
    And related to this ... just now (10:26 AM) I opened the sheet and did a CTRL-ALT-SHIFT-F9 (CalculateAllRebuild) and nothing changed. I then went to Settings->Clear Cache and then repeated the re-calculate and things changed. I don't think this is the way it should work.

    (And, is there a way to do a Clear Cache from VBA?)
     

Share This Page