Transmitting data from a server spreadsheet to a client spreadsheet

Hi all,

I have a spreadsheet (server.xls) that is constantly updated with stock market positions traded information from a trading application (via the trading application’s DDE link). Server.xls then constantly carries out various risk calculations on this data and updates its risk information (consisting of numbers and colour formatting) which is displayed in an area that is 20 rows by 20 columns on the spreadsheet. Excel 2002 is being used.

I have 5 other users that need to see this information, and rather than have 5 spreadsheets calculating risk data, I would like server.xls to feed the risk 20x20 cell range to 5 instances of client.xls, which would purely be a viewer spreadsheet that reads that 20x20 cell range and updates itself, preferably every 10 seconds or so. Note all the computers are networked and have use of a shared drive.

1)The ugliest way to accomplish this which I have working, is using VBA where server.xls, every 10 seconds pastes the 20x20 cell range to a data.xls sheet which is saved to the shared drive. Meanwhile every 10 seconds each client.xls makes a copy of data.xls to its local drive and then reads the data off it. I have this working and not only is it very inelegant, it also causes problems with disk fragmentation as over a day each client locally overwrites 5400 copies.

2)The second way I have found is to use the “Import External Data – Database Query” feature of Excel but it is limited to updating every 1 minute at most, and can’t seem to carry formatting across. I don’t know if the “Import Data” command is better at this.

3)The Microsoft online help recommends that if you want to import data using VBA, you can use ActiveX Data Objects or Data Access Objects. I have a lot of VBA experience but have never used ADO or DAO ( though I am happy to read up on them). Is it difficult to pass a range of cells data through them to a client spreadsheet? Is DDE a possibility?

I am sure there is a nice and simple method of getting a range from one excel sheet to another without resorting to using an intermediary data file which is written to and read from as in 1) above. Any suggestions would be gladly welcomed!

Thanks

Amadeo

Untested suggestion...

On the server side, create an add-in that incorporates the current
code, manages the server.xls file, and has an entry point that provides
the 20x20 matrix.

The clients can use this entry point to get the desired data.

If both pieces of software were running on the same machine, the above
is eminently doable. On different machines, it is conceptually doable.
However, it might need a few more steps to implement.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
>
> Hi all,
>
> I have a spreadsheet (server.xls) that is constantly updated with stock
> market positions traded information from a trading application (via the
> trading application=3Fs DDE link). Server.xls then constantly carries out
> various risk calculations on this data and updates its risk information
> (consisting of numbers and colour formatting) which is displayed in an
> area that is 20 rows by 20 columns on the spreadsheet. Excel 2002 is
> being used.
>
> I have 5 other users that need to see this information, and rather than
> have 5 spreadsheets calculating risk data, I would like server.xls to
> feed the risk 20x20 cell range to 5 instances of client.xls, which
> would purely be a viewer spreadsheet that reads that 20x20 cell range
> and updates itself, preferably every 10 seconds or so. Note all the
> computers are networked and have use of a shared drive.
>
> 1)The ugliest way to accomplish this which I have working, is using VBA
> where server.xls, every 10 seconds pastes the 20x20 cell range to a
> data.xls sheet which is saved to the shared drive. Meanwhile every 10
> seconds each client.xls makes a copy of data.xls to its local drive and
> then reads the data off it. I have this working and not only is it very
> inelegant, it also causes problems with disk fragmentation as over a
> day each client locally overwrites 5400 copies.
>
> 2)The second way I have found is to use the =3FImport External Data =3F
> Database Query=3F feature of Excel but it is limited to updating every 1
> minute at most, and can=3Ft seem to carry formatting across. I don=3Ft know
> if the =3FImport Data=3F command is better at this.
>
> 3)The Microsoft online help recommends that if you want to import data
> using VBA, you can use ActiveX Data Objects or Data Access Objects. I
> have a lot of VBA experience but have never used ADO or DAO ( though I
> am happy to read up on them). Is it difficult to pass a range of cells
> data through them to a client spreadsheet? Is DDE a possibility?
>
> I am sure there is a nice and simple method of getting a range from one
> excel sheet to another without resorting to using an intermediary data
> file which is written to and read from as in 1) above. Any suggestions
> would be gladly welcomed!
>
> Thanks
>
> Amadeo
>
>
> --
> gloryofbach
> ------------------------------------------------------------------------
> gloryofbach's Profile: http://www.excelforum.com/member.php...o&userid=28322
> View this thread: http://www.excelforum.com/showthread...hreadid=479051
>
>

Hi Tushar,

That is a very interesting reply, can you expand on what you mean?

For sure server.xls would reside on one machine and client.xls reside on each of several other machines, but all machines are linkind via a network and the shared drive.

Any other ideas from anyone?