VLookup command accessing external spreadsheet

[lampac34]

I am trying to link data between two spreadsheets. The data from one is accessed by two seperate spreadsheets files using the VLOOKUP command in excel.

The infuriating thing is this worked in testing and now doesn't work so it seems very flakey.

I have set the 3 spreadsheets to open from the "Excel" and "Log Multiple Sheets" option. I click on all the tabs that say keep the links but it just does not work.

I know what I am doing regarding creating VLOOKUP commands to grab data from external spreadsheets but it does not work here ???

Any ideas ? I am using Excel 2002, but this VLOOKUP is only a basic command and it did work in testing.

[osknows]

links to other workbooks aren't live if opened using another instance of Excel, i.e if data changes in one you have to refresh the link before the data updates. You have several options

1. write code to refresh links
2. write code to read data manually
3. Ensure both workbooks are open in same instanceof Excel
3. Migrate all worksheets into 1 workbook

If you're still stuck PM me a link to the workbooks and I'll see if I can help

[lampac34]

Just to add to my last post, I have checked and the VLOOKUP commands works seemlessly across the 2 Spreadsheets when the Beting Assistant software is not running. (I check this by manually adding data to the main spreadsheet and then the other 2 spreadsheets updated fine every time).

As soon as I put activate the spreadsheets using the "Log Multiple Sheets" command and say yes to updating the links the links then no longer work !

Another question is do you need to keep the "log Multiple Sheets" window open all the time ? or if when you save and close are the sheets then logged and ready to be used by Betting Assistant ? it makes no difference to the above problem but you cannot reaccess the main Betting Assistant interface with the Log Multiples Sheets page opened.

Look forward to response as other than this frustrating issue with the linked sheets not working it works great, but I wont be able to use this without multiple sheets.

Cheers

[lampac34]

Hi Osknows,

thank you for the reply.

It does seem to be related to refreshing the links which works to update the data the first time when the spreadsheets are first opened up when you click the "start logging" option in the "Log Multiple Sheets" section.

Is it quite simple to code a refresh that takes place say once every half a second ?

[osknows]

the syntax for updating links is

ThisWorkbook.UpdateLink _
"C:\Documents and Settings\Example.xls", xlExcelLinks

Non code solution:

WorkSheet Menubar, Edit, Links, Update Values

However, links can be very sssllllooooww to update.

[lampac34]

I have now put all the formulas into one spreadsheet, that seems to be working but cant test on live mkt till tomorrow, so will see then.

Thanks for your advice I think this may have done it.

Cheers