locked
VBA help required Microsoft excel RRS feed

  • Question

  • I have a excel sheet in below format. This sheet is updated every second by a plugin provided by a data provider. I need to:
    1. copy values into next column every minute
    2. Start copying at fixed time say Monday to Friday and at 9 AM
    3. Stop copying at fixed time say Monday to Friday and at 4:30 PM

    This is basically stock data and want to generate 1 Min. data out of it.

    The sample of data is as under:

    Stock LTP
    ABC
    12.30​
    XYZ
    345.00​
    Tuesday, July 21, 2020 6:02 AM

All replies

  • Trying this in VBA is pretty hard, cause VBA itself is not very stable for such timer-based actions.

    An approach which may work, hook into the worksheets Change event.

    Tuesday, July 21, 2020 11:46 AM
  • I did something like this a few years ago.  The addin would search for temp job assignments on the web every few minutes (random interval).  I had it run from 5am to 5pm.  I used Application.OnTime.  In another addin I used Task Scheduler to run the addin every 10 minutes. Both of these seemed to work fine if I used a dedicated PC.

    In one app I had to check the Outlook email inbox for a specific email and perform an action.  I was using Azure SQL Db.  For this app I used an Azure Function App (C#) and ran it every 2 minutes using a built in timer function.

    I don't know your requirements but you might want consider alternate solutions like using a database to do updates and having Excel read database on demand.

    Tuesday, July 21, 2020 1:16 PM