No announcement yet.

Open, refresh, save As and close Excel every half an hour

  • Filter
  • Time
  • Show
Clear All
new posts

  • Open, refresh, save As and close Excel every half an hour

    We run a number of sales sites and I am trying to automate stock control between them. I can export from the websites a CSV file with new orders (three separate csv files)

    Now, I have created a workbook that grabs these three CSV files, collates the data. I then have to manually do a save as CSV file to be able to export the collated data (actually into a database to do some more jiggery pokery)

    As we are getting busier, i really need this to be happening automatically every half an hour, or perhaps every hour

    excel version is 2007 on Windows PC

    So to the question, is it possible, if so how?

    Every half an hour
    Open Excel with a specific named workbook
    The workbook would then automatically refresh from the CSV data sources
    Then after say 30 secs to allow the updates to happen
    Do Save As to save as a CSV file (overwriting without prompts the existing file)
    Then close excel again without prompts

    This loop would then be left running 24/7

  • #2
    Use Task Scheduler to run a VBScript (.vbs) file every 30 minutes. The .vbs file opens the workbook, which automatically refreshes the CSV data (I assume you have a text import query which refreshes the data when opening the file). Then write a macro which calls Application.OnTime 30 seconds into the future which 'allows the updates to happen' (I'm not sure what you mean by this) and Saves As a CSV file and closes the workbook and the Excel application. The Save As and closing a workbook code can be generated by recording a macro and editing the generated code. There should be examples of all this on this forum.


    • #3
      Thanks for the reply

      Task Schulder, of course. Should have thought of that. Is there a reason why I should use a vbs file rather than just opening the file with scheduler?

      The reason for the delay is because whenever I open the workbook manually it seems to take a good few seconds before the linked data updates


      • #4

        VBScript is more flexible because it can run specific VBA macros, or run its own 'macros'. Without it you would need to use the Workbook_Open event handler to call macros.