We need to take a snapshot of an entire Excel worksheet every 1 minute.
We watch 325 stocks in real time. The dimensions of the matrix are cells B2 to P326. Column 1 is the stock symbols and row 1 is column headings that correspond to the DDE data fields. The data area we need to mine every minute is the cells mentioned above.
The data in the Excel spreadsheet is stock market data being updated by a DDE data feed. The data represents real time stock activity.
The need is to update an Access database with this data. Each row in the spreadsheet represnts a stock and each column represents a field. So, there is a one to one relationship mapped naturally between the fields in Access and the rows/columns in Excel.
Here is the way we do this now:
We have a little MS Access app that runs off of the timer event and executes this command each minute
The problem we have is that both Excel and Access have to be up and running at the same time on the same machine. This is terrebly slow since both Excel & Access are memory/CPU hogs.
We would like to either run a less inefficient process on the PC that is connected via the Excel spreadsheet to the DDE server/internet connection, or we would like to have a process running on a second PC that scrapes the data out of Excel remotely and into an Access DB every minute.
Obviously, once the data updates in the Access database, we do a bunch of custom VBA code between the minute marks to parse the results, but our problem is to scrape the data out and place it into Access. Right now, we just want to have a way to do this in real time that does not make us have both Access & Excel running on the same PC. We can not avoid having Excel up and running, but we are interested in some of your solutions as a way to let us put Access on one PC and Excel on another, thus making the entire process efficient enough to actually work in real-time.
We are not sure if any of your products can enable us to do this.
Thanks
Keener
