Linking cells from multiple workbooks

[Yorkie]

After taking the advice on here regarding multiple workbooks vs large single workbooks i've split up my large workbook that contained 6 systems into 6 individual workbooks.
My problem is that when it was a single workbook i had a master sheet that showed me the status of each system, wins,loss profit/loss etc
Now i would like to keep this and so i made it into it's own workbook.
So now i have 6 workbooks , each containg a system and another workbook containing the overall position sheet.
I use the excel quick loader within BA to load the system workbooks and open the overal workbook manually.
When i do this the overall workbook only sees 1 of the system workbooks as open and links to the others with the full path as tho they were closed. Only the workbook that is seen as open updates.

How can i overcome this?

Little bit more info, obviously all the workbooks are showing in the task bar but the overall workbook and the workbook that does link correctly just show as "Workbook Name" whereas all the other workbooks are called "Microsoft Excel - Workbook Name".

Hope i've explained good enough what i'm after

Cheers

Yorkie

[Yorkie]

Bit more info.

If I open all the workbooks manually everything is fine so is it the way the excel quick link loader works that means only 1 instance is seen as open?

Yorkie

[osknows]

I'm not sure what the problem is using the 'log multple sheets quick link' as all is working okay here?

If you are trying to create a summary sheet you may have to come up with a workaround. Because you have several workbooks a 'live' link will only work if all workbooks are open in 1 instance of excel (which it won't be in your case). Otherwise each workbook will have to be saved and the summary workbook links refreshed which is not ideal....

One way around this is to set up your summary workbook with a worksheet for each system and use VBA to copy and paste the summary sheets over. Then collate them in your summary workbook

There may be better ways of doing this so suggestions are welcome. If you need some help with the VBA let us know

Os

[osknows]

So BA is linking fine..?

I think you're seeing one of the frustrations with excel. You can't live link across multiple instances of excel

[Yorkie]

Ok more info

Yes the quick link loader works fine as far as linking ba to the workbooks, never not been fine.

IF
I open BA
I open each workbook manually, including the overall workbook
I open each market in BA and manually link it to the relevant worksheet
Doing it this way the overall workbook DOES update from each of the seperate workbooks BUT only 1 instance of excel exists.

By opening each workbook using the quick link loader it opens each in it's own instance of excel which then won't work on the overall workbook.

Do I assume that the benefits of having seperate workbooks is only evident when each has it's own instance of excel?

Yorkie

[osknows]

I think you will need to use a VBA option if you want to summarise across several instances of excel. Is the summary sheet you propose integral to your method or is it a 'nice to have'?

Eg if you just need a summary you could fire from a control button or every few minutes; if you need the summary on every refresh of BA then there could be a performance hit and you would have to compare with performance of running many worksheets/1 Excel vs 1 worksheet/multiple Excel

Let me know if you need some help..
Os

[Yorkie]

No it's purely a nice to have info sheet and has no bearing on the systems operation. I will have a look at how the different set ups seem to work , at the moment i would say that the single instance of excel may have the edge on my particular computer as i've noticed some markets taking over 1 minute to load into excel whereas i can't remember that happening when it was a single workbook.
The computer is an old P4 2.66 Ghz machine dedicated to BA and maybe on a faster multi processor machine it would cope with multiple instances better.

Anyway thanks for the offer of VBA help mate, i'm sure i will make use of it in the future.lol

Yorkie