workbook links updating strange behaviour – Excel 2010 SP1

Topic

New Reply

Several workbooks each with many worksheets (50+)
but lets just say workbook1 and workbook2

Workbook1 has multitude of links to workbook2
Some of the links used a mapped drive references suchs as H:workbook2.xlsx!$A$3
Other links (to the SAME workbook) have the path as UNc such as \ab-norm-1commonalanworkbook2.xlsx!$B$12

If the suer has workbook1 open and then opens the workbook2, then the first link reduces the drive reference to just the workbook but the second link still has the UNC full path and does not update.

Using edit links reveals that we are looking for 2 separate linked workbook2 instances.

Much strange

When the workbook2 is being worked on, then using update values in workbook1 will only reveal the last saved value of workbook2 (in other workbooks it updates to the current value even if workbook2 has not been saved)

Saving workbook2 and then updating the values in workbook1 does reveal the new workbook2 values.

Has anyone else experienced this and is their any kind of recommended procedure for going about getting the data correct?

We have used replace to correct the unc path but it seems random as to whether the unc path is used or not. (random shouldn’t figure in this..but it feels like it)

Thanks

Reply | Quote

Replies

Hi

Have you checked that each User has the same drive mappings for the particular PC you are running on???

If you have workbooks with 50+ worksheets it sounds like the files could be large. So another thing you can do is use Excel’s binary file format .xlsb rather than .xlsx or .xlsm
The binary file format is much more compact, meaning smaller file sizes therefore faster retrieval and less network traffic.
The binary file .xlsb format also allows macros to be included in the file too.

zeddy

Reply | Quote

Thanks, been a long busy few weeks so just back on this.
Yep, all users have same mappings at logon and having checked their sessions the mappings are remaining in place.
I have tried the files in a controlled area and have found:
File1 – links to file2 using the mapped drive definition, e.g. M:alanfile2.xlsx
File1 also links to file2 using the unc of \manccommonalanfile2.xlsx

With file2 open I changed a couple of values
Did not save file2
changed view to File1
the cells which referred to the mapped drive have updated immediately
the cells which referred to the unc location have not updated at all
I did not select to update links, just switched views to the File1

In File1, I now selected Dat>Edit Links
Both paths are indicated as containing links to external files.
I select the mapped drive link and select update (I know the data has already appeared in my file, but I selected it anyway)

I select the unc link and select update – and although the link status says OK, the data on the worksheet is not updated (I guess until I SAVE the file2)

Strange goings on, and pretty disconcerting if multiple users are using the files.

Anyone else have similar experience?
TIA
Alan

Reply | Quote