more about links….

Topic

New Reply

There are two link sources in the attached file. One is easy to find, the other had me stumped for quite a while. My question is not, however, purely link related – although it would be nice to know if anyone has a delinking addin that can detect this type of link.

Has anyone seen this behaviour before? If so how do I duplicate it? How do I detect it in code?

I can’t duplicate the behaviour with e97. Talking of versions, I’ve got no idea how old this file is, so it could easily be a conversion thing. Any takers?

Reply | Quote

Replies

I’m not really clear on what behaviour you are referring to. I can detect 2 links in the file, but can only locate one pn the sheet. However, I am getting a #REF error in row 6 of the table. I cannot know if those cells contained a reference to the 2nd link. Am I missing something ?

Andrew C

Reply | Quote

Sorry, that was a really bad post on my part. Edit/links shows you the two sources. The #refs should be ignored – the sheet came out of a much larger file, and only the relevant part of the sheet survived so that I could post it. The second link source is referenced in cells c5:n5.
Now to my way of thinking, that’s not possible. But it is happening. Unmerge the cell and reveal the link…. remerge the cell and destroy the link…. The workbook knows there’s hidden info there otherwise it wouldn’t show the second link source in edit/links, but debug.print activesheet.cells(5,10).value returns an error, entering the formula =j5 in another cell gives you #ref…. Can I locate in either excel or VBA the fact that there is data behind the merged cell that shouldn’t be there?

Is that any clearer?

Reply | Quote

I suspect that this file was last saved as an Xl95 or previous version. I think 97 was the first version that catered for merged cells. You could in earlier versions apply a formatting to center across selection, which actually made it look like a merged cell. I cannot recall if data in the other cells survived such formatting. When XL97 or 2000 meets this formatting it actually merges the cells, which I would consider to be a bug. It would be interesting to look at the file in XL95 or 5 and see if the formatting allows the invisible data to survive. A very interesting discovery. The cells actually copy and paste both by means of the menu commands and VBA operations.

Andrew

Reply | Quote

Now I’m not sure what you’re saying! CAS exists in 97 and, I thought, 2000. if in 97 I CAS the range under consideration, the links would show up – the centering only works to the next non empty cell. I would agree with you that looking at it in 95 would be interesting, but as it’s been saved as 97 now, would the backwards step pick up the original formatting? Interesting about the copying – I’d worked out copying in excel reproduced the effect, but didn’t get as far as copying in VBA. Still, if they both work, excel must (?) know what’s going on – surely?

Reply | Quote

I wasn’t really saying anything really I suppose, just thinking on the keyboard. I did save the document as a version 95/5 and when I re-opened it it had lost the merged cells (CAS) and each cell revealed the link. Does anybody in the lounge have XL5/95 installed – it would be interesting to see if it could be replicated in an earlier version. Do you have the original, i.e. a copy that was not saved in 97 or 2k ?. I would nearly install XL95 to find out – come to think of it I know where I can get XL 5, but not until tomorrow. I’ll try then.

Andrew

Reply | Quote

Sorry.

>merged cells(CAS)

doesn’t make sense to me. in my opinion the two are different.

I agree with you on the saving as 95 though. Reopening once I’ve done this does as you say reveal the #ref’s of the link under question. CAS, though, would never come up with the behaviour in the file as far as my experience goes(but then neither should merge – I’m also thinking on the keyboard). I’ll try and find out when the original file was created but due to the name in the link I don’t think it was more than two years ago. I’m fairly certain we’ve been 97 that long. I don’t know how far back on the notes database the versions go, but a problem here is that I couldn’t post an original – I’d always have to hack it to some extent. Still, I’ll see what I can come up with.

Reply | Quote

I know ‘merged cells(CAS)’ doesn’t make sense – thats why we are having this discourse, because I think that is the root of the problem. XL does not know what it has and is treating the range as CAS, or merged ,or single cells – take your pick. It is in a state of quantum uncertainty much like Schrodinger’s cat – you don’t know what the position is until you open the box.

I know that if I opened a XL95 file with CAS, XL97 and 2k would, at least in some cases, merge the cells. I have never knowingly tried that with a CAS containing more than one value.

Reply | Quote

Right. I’m with you now. I’ll have a look for the earliest version accesible tomorrow and see whether that suggests anything.

Reply | Quote

Okay, The file in question was created on or around the first week of december last year. The person who created it can’t remember precisely what they did but looking at the previous incarnation it would seem likely that two 97 files were hacked around and combined to create one, using a 97 machine. These two files will not be older than two years as they are reporting on a reporting method that the company introduced two years ago, but I don’t know for sure when we moved to 97. I guess that the rehash of the files included the copying of cells from the date movement file (the links point to a series of dates for consecutive friday’s, the data on offer being weekly figures) and then the subsequent decision not to bother including the dates and just merging the cells with the title – but I really don’t understand how the merge cell command didn’t wipe the links behind it.

This leaves me with three questions:

Has anyone else ever seen this behaviour?
Can anyone figure out a way to access the data in those cells, either via excel or by VBA without destroying the formatting?

What I’m getting at here is a routine that goes along the lines of:

Sub LocateDodgyMerge()
for each MergedCell in ActiveSheet.MergedCells
If MergedCell.HasHiddenData = True Then MergedCell.HiddenData.Trash
Next
End Sub

(or similar)

And finally, does anyone know of any delinking addins that will take care of this?

TIA

Brooke

Reply | Quote

microsoft has a wizard for XL97 to find links:

http://support.microsoft.com/support/kb/ar…s/Q188/4/49.ASP

Reply | Quote

Thanks for the response, I thought I’d posted here that I’ve already tried both the Microsoft and the Bill Manville utilities, but that they don’t work in this case. However, I think that was actually in a response to Geoff elsewhere. Sorry about that. Any others out there?

Reply | Quote