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