Excel 2007 Error: Link source not found

Another hard to reproduce one – but I think we may have actually solved this one. (after a fair amount of pain it has to be said)

Open a new Excel 2007

Open 2 wbs and save them as .xlsms.

In one put some text in one cell

in the other link to that cell.

Save and close both. (perhaps in a trusted location, and or set all external connection security to the minimum)

Open the source.

Then open the one with the link in, bear in mind this is now a link to an open workbook. That should just silently update. But no… Instead on several machines for several users we get this:

Click Edit links to get to here:

Source not found? SOURCE NOT FOUND?? Its behind you you ejit!

Click close on here or continue on the previous one and it just updates the link. Next time they open the file the same problem occurs. Its like at the moment of open Excel just cannot understand the link, and gives up. I think its the same across all file formats, but we didn’t test fully. It occured for every file with an external link in.

I tried all the usual Excel based things to try and sort it but nothing worked. One of the sys support guys suggested it might be corrupt profiles. And sure enough, if those affected create a new profile by logging into a new machine then the problem goes away.

Dunno how or why, but if it happens to you hopefully you won’t waste as much time as we have before trying this.

If you have seen this issue then plese let us know how you fixed it.

Have a good weekend.

cheers

simon

Magical merged cells

I don’t think merged cells get much love, nor do I think they deserve any. I much prefer to use centre across selection, but for some reason way back when, MS decided to put that at the bottom of the alignment drop down to ensure people pick the gross spreadsheet breaking Merge mess instead of the simple centre across selection. Oh well..

Anyway I just had a wb with external links that shouldn’t have. Find didn’t find any, no names, charts or objects – all the usual suspects for hard to remove names.

In this case Cells X10:AI10 were merged showing the year once across all 12 cells.

If I unmerge those 12 cells I get the year in X10 and the other 11 cells are links to cells in an external wb.

If I try and remerge them it deletes the links and leaves only the year.

How can you have a merged cell range that has multiple values/formulas?

Anyone else seen that? Anyone know how to do it?

This was an old spredasheet, so its not a 2007 issues

I wondered if it might have been some broken array formula? Any other ideas?

cheers

Simon

New Excel zero day exploit

Be careful what you are opening!

Seems there is a new vuln being actively exploited – across all recent versions of Excel, including the viewers.

cheers

Simon

End User Computing Czar

One of my firmly held spreadsheet quality views is that companies could benefit from a single point of focus for their End user computing.

That could be a person or a department, but lets start with a person. Their role would be to educate and support and where necessary force compliance with quality/control standards.

I think this role kind of exists in many small to medium companies in the shape of the Office expert – to whom everyone turns when they have a problem.

Larger orgs may have information protection czars, and or network czars, database czars, desktop build czars. In short they have a person or a department specifically responsible for every significant part of the IT infrastructure except the most important – the End User Computing jungle.

Does anyone know of any companies that do have a head of EUC or something similar on an equivalent level to the boss of networking, or client apps or whatever? Ie a senior role at or just below board level. No need to mention the co name, just a yes I have seen it or a no never.

How was it structured? did they report through IT/IS or finance or another user department or what?

I think organisations are realising what a mess their EUC resources are, but I don’t see much sign of the most obvious way to manage them – assign a manager!

do you see it?

Is it just too politically hard to work across deportments in the way this would need to succeed?

cheers

simon

No Office 14 in 2009

From big Steve himself – courtesy of Silicon Alley Insider.

I’m not saying I don’t believe the CEO or owt, buuut I wouldn’t be surprised to see something (Office 14 capable vouchers ??? ;-)) in time for the Christmas shopathon.

Judging by the rest of that call they could do with a bit of a holiday season revenue boost.

Do you think the release date will have a noticeable impact on your business?

cheers

Simon

Excel 2007 lost names

The last post was about Excel 2007 losing certain formulas when saved in compatibility mode.

This post is across all file formats – from titme to time, for no apparent reason Excel 2007 will lose name ranges (this is SP1).

Its not file or user specific, not even name specific – just sometimes the odd one or two names disappear.

One workaround we have been doing is to define them in the workbook_open event – but this of course destroys the main benefit of names – that they adapt to edits. If you hardcode the address in VBA, you might as well just use the hardcoded ref and accept to risks and problems.

One approach I have used in the past is to put a ref in a cell as in =A1:D100. that will probably return some sort of error, but it will adpat with inserts and deletes within that range. Then on open define the name to refer to the formula of that cell.

Anyone else seen this disappearing name issue and got a reproducable case?

Or a solution?

cheers

Simon

Excel 2007 =#N/A error

Anyone else seen this:

Open a happily working .xls that uses some analysis toolpack functions in Excel 2007 (SP1). Do a bit of work, click save, close.

Next time you need the file open it and find that some or all of the ATP functions (EOMONTH, EDATE, NETWORKDAYS, etc) and any cells that depend on them have been helpfully replaced by ‘=#N/A’.

It seems another intermittent problem like the freezing I mentioned the other week. This one though is a proper data loss. If that file is saved (possible as the underlying formula is changed to =#N/A but if its not calculated it still reports the correct answer). As far as I have seen and read this is a compatibility  mode only issue – it has a better memory in native mode. Unless you know different?

From what I have read it appears to be a known issue specifically with the ATP functions that have been ‘incorporated’ into the Excel core. I say ‘incorporated’ rather than incorporated because losing them from time to time does not seem exactly incorporated to me.

Apparently the solution is to enable the ATP in the Excel 2007 Addins list. Which is a bit odd. Or to use a native file format. I havent been able to check that as I am struggling to repro the problem.

It looks a bit like the internal codes have moved. Except that its inconsistent. The fact it flows through the calc chain is a bit of a scare too (not sure how far).

Anyone seen this? Any other theories? or fixes/workarounds?

I think it is often partnered by a ‘File Error: data may have been lost’ warning on open.

cheers

Simon

Competitiveness

Interesting story here the author is discussing another post proposing that MS cut the MacBU to bolster their O/S marketshare, and save a few quid. The idea being that if MS stop writing software for the Mac then Macs marketshare would decline as everyone rushed back to Windows.

I don’t think Mac marketshare would decrease if MS Office was suddenly not available on Macs – do you?

Personally I think that is the best possible thing Microsoft could do for OpenOffice. OOo is already the best cross platform option, but it has only recently got a native look and feel on Macs.

If MS did bin Mac Office, then I think OOo would quickly become the standard on none MS o/s which is just over 10% – thats enough to start a trend. And I think it would, and that trend would be away from MS Office and in turn away from MS Windows.

So I agree with the post I linked to, and I reckon the guy he linked to is well wrong.

Whats your take?

cheers

Simon

Codematic Excel Add-in Downloads

Heres the latest situation from the Codematic free Excel add-ins and tools downloads.

The anonimser hasn’t been up that long so that may not be a fair representation of its popularity.

Its good to see that XLAnalyst is still a popular download – sometimes I wonder if the fad of caring about spreadsheet quality passed faster than skateboarding. (I am saving up for a new skateboard, having realised the kids have better ones than mine).

Watch out for a new commercial version of XLAnalyst… one day ;-). Its all written, I’m using it on my own projects, I just want to do some more structured testing and package it up for release – both of which are much less fun than writing software.

The most interesting thing to me is the continued popularity of the base xll project. I have always thought there is a lot of pent up interest in this ‘fast worksheet funciton’ space. but other than doing the odd training course, and offering to convert VBA UDFs to xll ones I havent really come up with a worthwhile commercial offering. Any ideas?

The popularity of fast worksheet unprotection has never surprised me, the pitiful download to purchase ratio is a little on the disappointing side though (not a surprise though).

Cheers

Simon

Are they serious?

I havent been keeping up with my internet reading for a while so only the big buzzes have been getting through.

I note the noise about Win 7 and the general relief that its appears so far to be less crap than Vista. So far.

I just noticed a comment today saying the cheap, netbook targeted version of Win 7 (starter) will only let you have 3 apps open simultaneously. At first I just thought it was a joke, a miss timed april fools perhaps.

Anyway it appears they are serious! I’m stunned.

What apps do they expect netbook users to be running?

Web browser? – Firefox is at least as good as any IE

Email? – Thunderbird is at least as good as Outlook Express, or its next incarnation.

Skype? – does MS have a product in this space? I think so – is it something something something live?

Office? -puh-leeese! that big daft ribbon takes far too much screen to be usable on a netbook. OOo make much more sense,( or Gnumeric).

The thing is, the most likely 3 apps are all areas where Microsoft has some serious competition. And all the competitors works at least as well on Linux.

Why would anyone buy a crippled Windows netbook, when they can have the same or better apps for free on Linux?? It makes no sense to me – unless they are expecting netbook manufacturers to stop supplying Linux machines? (HP – I’m looking at you)

So if the netbook manufacturers want to shift Windows netbooks its looks like they are stuck with the next version up of windows, which is rumoured to cost significantly more than the starter version.

So you can have a crippled Windows netbook for just a bit more than a Linux one, or you can have a normally functioning Windows one for significantly more money. But you will probably be using the same or similar apps whichever way you go.

I must be missing something about how this is going to boost windows uptake?

Please let us know in the comments

Cheers

Simon