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


--
gloryofbach
------------------------------------------------------------------------
gloryofbach's Profile: http://www.excelforum.com/member.php...o&userid=28322
View this thread: http://www.excelforum.com/showthread...hreadid=479051

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 ,
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.


--
gloryofbach
------------------------------------------------------------------------
gloryofbach's Profile: http://www.excelforum.com/member.php...o&userid=28322
View this thread: http://www.excelforum.com/showthread...hreadid=479051

Any other ideas from anyone?


--
gloryofbach
------------------------------------------------------------------------
gloryofbach's Profile: http://www.excelforum.com/member.php...o&userid=28322
View this thread: http://www.excelforum.com/showthread...hreadid=479051