Migrating from 2003 to 2007: Unexpected link errors appear

[Bernd Wechner]

I have an application which creates an excel workbook. It works fine using
the Excel 2003 object model to create the workbook. Now if I use the Excel
2007 object model (a simple upgrade) suddenly it throws a link error when its
opened.

I am then frustrated by the still painful lack of link diagnostic tools.
They have improved in 2007 to be sure, marginally. 2003 just points out that
I have links to other data sources and I can choose to update or not update
them. But 2007 actually throws up an editor with various options to fix or
remove the links but still for some reason beyond my comprehension does not
let me know where that link is.

The problem is, I don't ever create a link, not knowingly. I've never had
one, made one or been informed of one after loads of using my app. Now I
upgrade and there magically is one?

It relates suspiciously to one of he worksheets in a temporary workbook I
create. A rough outline of my codeed approach is as follows:

Create Workbook 1 and add some sheets of data and such to it.
Create workbook 2 and add some sheets of data and such to it.
Use Worksheets.Move to move the sheets from Workbook 2 to Workbook 1
Close Workbook 2
Save Workbook 1

Now this has been running fine using Excel 2003 to create the workbooks,
worksheets, moving them and all. And I end up with one workbook with all my
sheets in it and all is well.

If I use Excel 2007 to create the workbooks and worksheets, move them and
all, then whether I open the result .xls file in Excel 2007 or Excel 2003 I
get an error regarding a link. Excel 2007 in its link editor says it
apparently comes from one of the sheets that I moved from workbook 2 to
workbook 1.

Worksheet.Move has no options I can see re: holus bolus clean move or weird
leave links around moves. So what's up? Why does Excel 2007 create the
impression, in the .xls file that there are links. Links I've not created,
was never aware of, don't want, and can't find?

Is this a bug in Excel 2007? Can I share an .xls file that exhibits this
problem with you for diagnosis?

The sheet in question contains some data and a chart. That is all.

Moreover, when Excel 2003 creates the .xls file it is about 300KB in size.
When Excel 2007 creates the .xls file it is about 11MB in size. Yep, that's
an order of magnitude difference and most surprizing.

Yes, I can try and use Excel 2007 to create a .xlsx or .xlsm file instead,
and may do that next, but it doesn't aid in comprehension of the problem. It
is the Workbook.SaveAs method I use to save the .xls file, with parameters
specified appropriately.

This is a little puzzling.

 

[Jan Karel Pieterse]

Hi Bernd,

Worksheet.Move has no options I can see re: holus bolus clean move or weird
leave links around moves. So what's up? Why does Excel 2007 create the
impression, in the .xls file that there are links. Links I've not created,
was never aware of, don't want, and can't find?

Download Bill Manville's Findlink from www.oaltd.co.uk/mvp. That will find your
link.

I bet it is in the chart somewhere.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Bernd Wechner]

Thanks Jan. Very helpful, I googled around for such a tool and failed, which
only goes to show a human being can still outperform google in the
information mining field.

This helps me to confirm only one thing, that I already knew. That there are
no external links. Well more or less. Let see what anyone can make of this.

I have a chart on a sheet named "All Defects" and it references data on
another sheet named "DR - All Defects". The Edit Links dialog suggests the
problem it finds is as follows:

Source: DR - All Defects
Type: Worksheet
Update: A
Status: Error: Source not found

which I take it to mean that the sheet "DR - All Defects" cannot be found.

But that sheet does exist, and the chart referencing it is fine (seemingly
displays the data from the sheet just fine, if I change data on the"DR - All
Defects" sheet the chart updates just fine). So where is the broken link?

Enter Find Links. Here is its report for "DR - All Defects" which is its
default suggestion oddly enough as if it knows what the complaint is about:

Sheet Object Name Property Value Action
Defect Rates Series Testing Effort Formula =SERIES("Testing Effort",'DR -
All Defects'!$A$3:$A$22,'DR - All Defects'!$B$3:$B$22,14)
Defect Rates Series Priority 3+ Defects Formula =SERIES("Priority 3+
Defects",'DR - All Defects'!$A$3:$A$22,'DR - Priority 3+
Defects'!$G$3:$G$22,4)
Defect Rates Series Priority 2 Defects Formula =SERIES("Priority 2
Defects",'DR - All Defects'!$A$3:$A$22,'DR - Priority 2
Defects'!$G$3:$G$22,3)
Defect Rates Series Priority 1 Defects Formula =SERIES("Priority 1
Defects",'DR - All Defects'!$A$3:$A$22,'DR - Priority 1
Defects'!$G$3:$G$22,2)
Defect Rates Series Priority 0 Defects Formula =SERIES("Priority 0
Defects",'DR - All Defects'!$A$3:$A$22,'DR - Priority 0
Defects'!$G$3:$G$22,1)
Defect Rates AxisTitle Secondary Axis Title Formula [unable to check] Please
check manually
Chart Chart 1 on DR - All Defects Series Closed Formula =SERIES('DR - All
Defects'!$E$2,'DR - All Defects'!$A$3:$A$22,'DR - All Defects'!$E$3:$E$22,3)
Chart Chart 1 on DR - All Defects Series Approved Formula =SERIES('DR - All
Defects'!$D$2,'DR - All Defects'!$A$3:$A$22,'DR - All Defects'!$D$3:$D$22,2)
Chart Chart 1 on DR - All Defects Series Created Formula =SERIES('DR - All
Defects'!$C$2,'DR - All Defects'!$A$3:$A$22,'DR - All Defects'!$C$3:$C$22,1)

I hope that's legible in the post, or can be made so by undoing and unwanted
word wrap (it's a copy/paste from FindLinks report which is an Excel sheet of
course).

Only one anomaly is immediately visible:

Defect Rates AxisTitle Secondary Axis Title Formula [unable to check] Please
check manually

What does FindLinks mean "unable to check"?

So I go to the a mentioned chart, delete the Secondary Y Axis label and run
FindLinks again and sure enough its the same report without that anomaly. So
I know which label it's complaining about.

So now I check the label (in Excel 2003) under Chart Options, Titles,
Secondary Value (Y) axis and it is a simple string "Person-Hours". I can
change this any other string like "Test" and the same anomaly is reported. I
can delete the title, add a new one and same deal.

Could be a strange Excel 2007 bug. It remains suspicious that when Excel
2003 writes this file it is 300KB and when Excel 2007 write sit it is 11MB.
Other differences are visible, some other glitches or simply style changes -
chart colors changed and some odd series I don't expect all to diagnose and
no doubt related to differences in the object model calls I'm using but not
as in your face as this apparent reference error that isn't.

Any further input very welcome. I suspect my next experiment will be to have
Excel 2007 write its native .xlsm format to see if I get smoother run on that!

 

[Bernd Wechner]

Frustratingly I am now saving in .xlsm format and the file size has grown to
30MB (sheesh I though .xlsx and .xlsm were introduced because of space
savings in part, remmber 2007 wrote an 11MB .xls file and 2003 wrote a 300KB
..xls file).

When I open this in office 2007 it exhibits the same broken link issue. When
I open it in 2003 it goes through a converter which doe NOT exhibit the
broken link complaint but complains:

---------------------------
Microsoft Office Excel
---------------------------
This file was created in a newer version of Microsoft Excel. The file has
been converted to a format you can work with, but the following issues were
encountered. The file has been opened in read-only mode to protect the
original file.

- This workbook uses more rows and/or columns than are supported in this
version of Excel. Any data outside 256 (IV) columns by 65,536 rows will not
be opened. Any formula references to data in this region will return #REF!
error.

- This version of Excel does not support as many line colors as newer
versions of Excel. All line colors in this workbook will be mapped to the
closest color supported by this version of Excel, and a chart may display
multiple series in the same color.
---------------------------
OK
---------------------------

Which is a lie again. NO sheet in this workbook has more columns or rows
than 2003 supports. I know, I'm migrating it from 2003 where it works hitch
free and data sizes are modest.

I have a gut feel the object model in Excel 2007 or the Microsoft Visual
Studio Tools for the Microsoft Office system (version 3.0 Runtime) with
Service Pack 1 that I am using to access it has some very serious issues with
migration and is perhaps creating monster sheets for no reason at all from
the Range methods I am using to populate (via the Value2 property) rages that
I request using the get_Range method).

This is costly to diagnose and get right alas and bugging me mighty.

Any simply Epiphanous insights are welcome believe me. Sometimes one or two
gems of insight can take away loads and loads of research and diagnosis
effort.

So much so right now that a slow abandonment of Office solutions in their
entirety and find something less painful. I am torn, that is not a cheap way
forward either, not at all, and migration still looks more attractive (just
;-). Perhaps by the time I'm done I'll know enough about 2003 to 2007
migration I can start consulting myself ... ;-).

 

[Jan Karel Pieterse]

Hi Bernd,

What does FindLinks mean "unable to check"?

So I go to the a mentioned chart, delete the Secondary Y Axis label and run
FindLinks again and sure enough its the same report without that anomaly. So
I know which label it's complaining about.

So now I check the label (in Excel 2003) under Chart Options, Titles,
Secondary Value (Y) axis and it is a simple string "Person-Hours". I can
change this any other string like "Test" and the same anomaly is reported. I
can delete the title, add a new one and same deal.

It means there is no VBA way to check if the secondary axis title is perhaps
tied to a cell in an external file. Hence the "please check manually"
suggestion.

Could you send me the offending workbook (one that exhibits the external link
msg)?

Use

infoATjkp-ads.com

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Bernd Wechner]

Some small progress I have made.

Indeed, when saving as .xlsm in the Office OpenXML format diagnostics are
easier than otherwise. I can seethe size blowout is indeed due to the sheets
I move from another workbook into this one. It would appear that in so doing
that Excel 2007 moves all of the empty cells across and they are no longer
empty in the target somehow, and it spans the legal range of cells in Excel
2007 being greater than that in 2003. This explains the size blowout and
suggests some need to explore how and why the worksheet move is doing this
and possibly to work around that move if the move method doesn't supply new
2007 arguments that help.

I can also see that there is one external reference mentioned and
unsurprising perhaps in XML as:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <Relationships
xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1"
Type="http://schemas.microsoft.com/office/2006/relationships/xlExternalLinkPath/xlPathMissing" Target="DR%20-%20All%20Defects" TargetMode="External" />
</Relationships>

So Excel 2007 decided the link was there and missing and worth recording
before it saved to .xlsm format. Tracing where that link is through al the
XML files and relationship files has stumped me so far. It is accompanied by
another XML file containing:

<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <externalLink
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
- <externalBook
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
r:id="rId1">
- <sheetNames>
<sheetName val="DR - All Defects" />
</sheetNames>
- <sheetDataSet>
<sheetData sheetId="0" refreshError="1" />
</sheetDataSet>
</externalBook>
</externalLink>

suggesting the broken link is a sheet with sheetID 0 but alas there is no
such sheet ;-). The sheet ids in the workbook.xl file run for 4 to 31 ...

All rather bizarre. But I have some more solid leads as to where the size
blowout comes from specifically (which methods in my code - i.e. the
Worksheet.Move() method) and a teeny bit of traction on that aberrant and
unexpected external link.

 

[Jan Karel Pieterse]

Hi Bernd,

So Excel 2007 decided the link was there and missing and worth recording
before it saved to .xlsm format. Tracing where that link is through al the
XML files and relationship files has stumped me so far. It is accompanied by
another XML file containing:

I emailed you separately. The problem lies (I suspect) in a problematic series
on one of the charts, where the seriess index in thes eries formula is 14,
where there are just 5 series in the chart. Changing the 14 to 5 in Excel 2003
made the link go away. Will try in Excel 2007 too.

Copying worksheets from one file to another can cause havoc as you've seen,
especially if charts are involved. How are you doing the copy (show us some
code)?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Jan Karel Pieterse]

Hi Jan,

You were mistaken on the # of series on the chart, there were 14 indeed.

But there IS a problem with the 14th series on that chart. If I open the file
in Excel 2007 and change the name of the sheet the series formula SEEMS to
point to, the series formulas is not updated.

I'd suggest rebuilding the chart.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com