Bug! Error Bars Inherited from Another Workbook - Any Fix?

  • Thread starter Thread starter Spiggy Topes
  • Start date Start date
S

Spiggy Topes

Got an odd one here.

I have suite of macros that create a workbook with a number of sheets,
containing charts from a number of separate workbooks. The charts show
a number of series, including a mean with custom error bars, taken
directly from two columns in the source data, spec for the + bars
='C:\Temp\[Some_File_Or_Other.xls]Data_Sheet'!$CK$2:$CK$366, and the
following column for the - bars.

I run the macro once, and save the resultant chart-filled workbook as
DirectoryA/FileA.xls.

I change the data in the source workbooks, rerun the macro and save the
results as DirectoryB/FileB.xls

I close Excel.

I open each file, and everything's fine.

I close Excel.

I move the file known as DirectoryB/FileB.xls to DirectoryA, so now I
have FileA and FileB both in the same directory.

I open one of the two files. It looks fine.

I open the other file. It too looks fine.

I alt-tab back to file one, and it's now showing the error bars from
file two. Other series are okay, but the error bars are from the most
recent file. I close the second file, no problem. I close the first
file, Excel asks if I want to save changes.

Normally, it's not not a problem. But I'm mailing these files out, and
if they're opened directly from Outlook, they're in the same directory,
and the recipients get very, very confused.

Any ideas for a simple fix?
 
Your macro shouldn't be going all the way to the root to get the error bar
links. I would worry that the proper file was referenced in the first place.
If you want to post the code we could do a little triage.

- Jon
 
There's an awful lot of source code, around 2,000 lines including
comments. I wouldn't subject you to it, and I couldn't post data to
match, so it would be a little hard to debug.

But it's easy enough to recreate.

First, create a data workbook. A few rows of two columns, column 1 for
the series, column 2 for the error bars.

Next, a chart workbook. Build a chart based on the series in workbook
1, and add a custom error bar - +ive or -ive, matters not - using
column 2. Save this as Chart_A.xls.

Now return to the data workbook and change the values in the error bar
column.

Return to the chart workbook, which should reflect the changed values.
Save it again, this time as Chart_B.xls.You must save to the same
directory as Chart_A.

Close all open files, don't bother to save the data sheet, or, if you
do, rename it or something so that it's not found when you reopen the
charts.

Open Chart_A.xls. You'll get the automatic links notification, to
which respond "No". Chart looks fine.

Repeat with Chart_B.xls. This looks fine too.

Now navigate back to Chart_A. Hey presto! It's got chart_B's error
bars!

So it's not a macro problem. I haven't fully identified the scope of
the problem, but it's independent of chart file names and sheet names,
dependent on both having automatic links to "the same place" and being
in the same directory. Move Chart_D.xls to a different directory and
you'll see the problem goes away.

Neat, huh?



Your macro shouldn't be going all the way to the root to get the error bar
links. I would worry that the proper file was referenced in the first place.
If you want to post the code we could do a little triage.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



Spiggy Topes said:
Got an odd one here.

I have suite of macros that create a workbook with a number of sheets,
containing charts from a number of separate workbooks. The charts show
a number of series, including a mean with custom error bars, taken
directly from two columns in the source data, spec for the + bars
='C:\Temp\[Some_File_Or_Other.xls]Data_Sheet'!$CK$2:$CK$366, and the
following column for the - bars.

I run the macro once, and save the resultant chart-filled workbook as
DirectoryA/FileA.xls.

I change the data in the source workbooks, rerun the macro and save the
results as DirectoryB/FileB.xls

I close Excel.

I open each file, and everything's fine.

I close Excel.

I move the file known as DirectoryB/FileB.xls to DirectoryA, so now I
have FileA and FileB both in the same directory.

I open one of the two files. It looks fine.

I open the other file. It too looks fine.

I alt-tab back to file one, and it's now showing the error bars from
file two. Other series are okay, but the error bars are from the most
recent file. I close the second file, no problem. I close the first
file, Excel asks if I want to save changes.

Normally, it's not not a problem. But I'm mailing these files out, and
if they're opened directly from Outlook, they're in the same directory,
and the recipients get very, very confused.

Any ideas for a simple fix?
Click to expand...
Click to expand...
 
I reproduced this with the error bars, then I found the same problem with
the chart data itself. Sick.

If & when I get a chance I'll forward it to the Excel guys.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Spiggy Topes said:
There's an awful lot of source code, around 2,000 lines including
comments. I wouldn't subject you to it, and I couldn't post data to
match, so it would be a little hard to debug.

But it's easy enough to recreate.

First, create a data workbook. A few rows of two columns, column 1 for
the series, column 2 for the error bars.

Next, a chart workbook. Build a chart based on the series in workbook
1, and add a custom error bar - +ive or -ive, matters not - using
column 2. Save this as Chart_A.xls.

Now return to the data workbook and change the values in the error bar
column.

Return to the chart workbook, which should reflect the changed values.
Save it again, this time as Chart_B.xls.You must save to the same
directory as Chart_A.

Close all open files, don't bother to save the data sheet, or, if you
do, rename it or something so that it's not found when you reopen the
charts.

Open Chart_A.xls. You'll get the automatic links notification, to
which respond "No". Chart looks fine.

Repeat with Chart_B.xls. This looks fine too.

Now navigate back to Chart_A. Hey presto! It's got chart_B's error
bars!

So it's not a macro problem. I haven't fully identified the scope of
the problem, but it's independent of chart file names and sheet names,
dependent on both having automatic links to "the same place" and being
in the same directory. Move Chart_D.xls to a different directory and
you'll see the problem goes away.

Neat, huh?



Your macro shouldn't be going all the way to the root to get the error bar
links. I would worry that the proper file was referenced in the first
place.
If you want to post the code we could do a little triage.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



Spiggy Topes said:
Got an odd one here.

I have suite of macros that create a workbook with a number of sheets,
containing charts from a number of separate workbooks. The charts show
a number of series, including a mean with custom error bars, taken
directly from two columns in the source data, spec for the + bars
='C:\Temp\[Some_File_Or_Other.xls]Data_Sheet'!$CK$2:$CK$366, and the
following column for the - bars.

I run the macro once, and save the resultant chart-filled workbook as
DirectoryA/FileA.xls.

I change the data in the source workbooks, rerun the macro and save the
results as DirectoryB/FileB.xls

I close Excel.

I open each file, and everything's fine.

I close Excel.

I move the file known as DirectoryB/FileB.xls to DirectoryA, so now I
have FileA and FileB both in the same directory.

I open one of the two files. It looks fine.

I open the other file. It too looks fine.

I alt-tab back to file one, and it's now showing the error bars from
file two. Other series are okay, but the error bars are from the most
recent file. I close the second file, no problem. I close the first
file, Excel asks if I want to save changes.

Normally, it's not not a problem. But I'm mailing these files out, and
if they're opened directly from Outlook, they're in the same directory,
and the recipients get very, very confused.

Any ideas for a simple fix?
Click to expand...
Click to expand...
Click to expand...
 
As a workaround, why not put the different values into a different range,
like a different column or a different sheet? Then the two saved charts will
not be pointing at the same imaginary range of data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Jon Peltier said:
I reproduced this with the error bars, then I found the same problem with
the chart data itself. Sick.

If & when I get a chance I'll forward it to the Excel guys.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Spiggy Topes said:
There's an awful lot of source code, around 2,000 lines including
comments. I wouldn't subject you to it, and I couldn't post data to
match, so it would be a little hard to debug.

But it's easy enough to recreate.

First, create a data workbook. A few rows of two columns, column 1 for
the series, column 2 for the error bars.

Next, a chart workbook. Build a chart based on the series in workbook
1, and add a custom error bar - +ive or -ive, matters not - using
column 2. Save this as Chart_A.xls.

Now return to the data workbook and change the values in the error bar
column.

Return to the chart workbook, which should reflect the changed values.
Save it again, this time as Chart_B.xls.You must save to the same
directory as Chart_A.

Close all open files, don't bother to save the data sheet, or, if you
do, rename it or something so that it's not found when you reopen the
charts.

Open Chart_A.xls. You'll get the automatic links notification, to
which respond "No". Chart looks fine.

Repeat with Chart_B.xls. This looks fine too.

Now navigate back to Chart_A. Hey presto! It's got chart_B's error
bars!

So it's not a macro problem. I haven't fully identified the scope of
the problem, but it's independent of chart file names and sheet names,
dependent on both having automatic links to "the same place" and being
in the same directory. Move Chart_D.xls to a different directory and
you'll see the problem goes away.

Neat, huh?



Your macro shouldn't be going all the way to the root to get the error
bar
links. I would worry that the proper file was referenced in the first
place.
If you want to post the code we could do a little triage.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



Got an odd one here.

I have suite of macros that create a workbook with a number of sheets,
containing charts from a number of separate workbooks. The charts show
a number of series, including a mean with custom error bars, taken
directly from two columns in the source data, spec for the + bars
='C:\Temp\[Some_File_Or_Other.xls]Data_Sheet'!$CK$2:$CK$366, and the
following column for the - bars.

I run the macro once, and save the resultant chart-filled workbook as
DirectoryA/FileA.xls.

I change the data in the source workbooks, rerun the macro and save the
results as DirectoryB/FileB.xls

I close Excel.

I open each file, and everything's fine.

I close Excel.

I move the file known as DirectoryB/FileB.xls to DirectoryA, so now I
have FileA and FileB both in the same directory.

I open one of the two files. It looks fine.

I open the other file. It too looks fine.

I alt-tab back to file one, and it's now showing the error bars from
file two. Other series are okay, but the error bars are from the most
recent file. I close the second file, no problem. I close the first
file, Excel asks if I want to save changes.

Normally, it's not not a problem. But I'm mailing these files out, and
if they're opened directly from Outlook, they're in the same directory,
and the recipients get very, very confused.

Any ideas for a simple fix?
Click to expand...
Click to expand...
Click to expand...
 
It's not a problem in day-to-day running, as I'm normally mailing out a
single worksheet each day; this only arose because we're evaluating 2
SD's vs 95% confidence limits vs 99% confidence limits, and needed two
workbooks open at once to eyeball them. So as long as I know it's
there, I can take steps to avoid.

One effective way would be to extract the data from the chart back into
a sheet within the chart file, and redirect the chart to use that data,
thus avoiding having a linked dataset at all. I have a macro to handle
the first part, and the second part shouldn't be too hard.

But still, it's an interesting bug; if time allows, I'll do some more
excavating, see if I can better define the perimeter of the problem - I
hadn't experienced the same phenomenon on the series themselves, for
instance, and you did, so there's scope for further delving there.

Thanks for taking an interest in this, and if I come up with any
further revelations, I'll post again.

Robert Smith

Jon said:
As a workaround, why not put the different values into a different range,
like a different column or a different sheet? Then the two saved charts will
not be pointing at the same imaginary range of data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Jon Peltier said:
I reproduced this with the error bars, then I found the same problem with
the chart data itself. Sick.

If & when I get a chance I'll forward it to the Excel guys.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Spiggy Topes said:
There's an awful lot of source code, around 2,000 lines including
comments. I wouldn't subject you to it, and I couldn't post data to
match, so it would be a little hard to debug.

But it's easy enough to recreate.

First, create a data workbook. A few rows of two columns, column 1 for
the series, column 2 for the error bars.

Next, a chart workbook. Build a chart based on the series in workbook
1, and add a custom error bar - +ive or -ive, matters not - using
column 2. Save this as Chart_A.xls.

Now return to the data workbook and change the values in the error bar
column.

Return to the chart workbook, which should reflect the changed values.
Save it again, this time as Chart_B.xls.You must save to the same
directory as Chart_A.

Close all open files, don't bother to save the data sheet, or, if you
do, rename it or something so that it's not found when you reopen the
charts.

Open Chart_A.xls. You'll get the automatic links notification, to
which respond "No". Chart looks fine.

Repeat with Chart_B.xls. This looks fine too.

Now navigate back to Chart_A. Hey presto! It's got chart_B's error
bars!

So it's not a macro problem. I haven't fully identified the scope of
the problem, but it's independent of chart file names and sheet names,
dependent on both having automatic links to "the same place" and being
in the same directory. Move Chart_D.xls to a different directory and
you'll see the problem goes away.

Neat, huh?



On Thu, 28 Sep 2006 18:46:04 -0400, "Jon Peltier"

Your macro shouldn't be going all the way to the root to get the error
bar
links. I would worry that the proper file was referenced in the first
place.
If you want to post the code we could do a little triage.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



Got an odd one here.

I have suite of macros that create a workbook with a number of sheets,
containing charts from a number of separate workbooks. The charts show
a number of series, including a mean with custom error bars, taken
directly from two columns in the source data, spec for the + bars
='C:\Temp\[Some_File_Or_Other.xls]Data_Sheet'!$CK$2:$CK$366, and the
following column for the - bars.

I run the macro once, and save the resultant chart-filled workbook as
DirectoryA/FileA.xls.

I change the data in the source workbooks, rerun the macro and save the
results as DirectoryB/FileB.xls

I close Excel.

I open each file, and everything's fine.

I close Excel.

I move the file known as DirectoryB/FileB.xls to DirectoryA, so now I
have FileA and FileB both in the same directory.

I open one of the two files. It looks fine.

I open the other file. It too looks fine.

I alt-tab back to file one, and it's now showing the error bars from
file two. Other series are okay, but the error bars are from the most
recent file. I close the second file, no problem. I close the first
file, Excel asks if I want to save changes.

Normally, it's not not a problem. But I'm mailing these files out, and
if they're opened directly from Outlook, they're in the same directory,
and the recipients get very, very confused.

Any ideas for a simple fix?
Click to expand...
Click to expand...
Click to expand...
 
Robert -

I generally avoid links to external workbooks; I lost a ton of important
data one time when a coworker deleted the data file that a workbook full of
charts was linked to. Before I had a chance to extract the data from the
charts, I'd clicked OK to update links, then saved the file. I'm better at
keeping backups current, too.

Feel free to email me directly, in case I miss any follow-up.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Spiggy Topes said:
It's not a problem in day-to-day running, as I'm normally mailing out a
single worksheet each day; this only arose because we're evaluating 2
SD's vs 95% confidence limits vs 99% confidence limits, and needed two
workbooks open at once to eyeball them. So as long as I know it's
there, I can take steps to avoid.

One effective way would be to extract the data from the chart back into
a sheet within the chart file, and redirect the chart to use that data,
thus avoiding having a linked dataset at all. I have a macro to handle
the first part, and the second part shouldn't be too hard.

But still, it's an interesting bug; if time allows, I'll do some more
excavating, see if I can better define the perimeter of the problem - I
hadn't experienced the same phenomenon on the series themselves, for
instance, and you did, so there's scope for further delving there.

Thanks for taking an interest in this, and if I come up with any
further revelations, I'll post again.

Robert Smith

Jon said:
As a workaround, why not put the different values into a different range,
like a different column or a different sheet? Then the two saved charts
will
not be pointing at the same imaginary range of data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


Jon Peltier said:
I reproduced this with the error bars, then I found the same problem
with
the chart data itself. Sick.

If & when I get a chance I'll forward it to the Excel guys.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


There's an awful lot of source code, around 2,000 lines including
comments. I wouldn't subject you to it, and I couldn't post data to
match, so it would be a little hard to debug.

But it's easy enough to recreate.

First, create a data workbook. A few rows of two columns, column 1 for
the series, column 2 for the error bars.

Next, a chart workbook. Build a chart based on the series in workbook
1, and add a custom error bar - +ive or -ive, matters not - using
column 2. Save this as Chart_A.xls.

Now return to the data workbook and change the values in the error bar
column.

Return to the chart workbook, which should reflect the changed values.
Save it again, this time as Chart_B.xls.You must save to the same
directory as Chart_A.

Close all open files, don't bother to save the data sheet, or, if you
do, rename it or something so that it's not found when you reopen the
charts.

Open Chart_A.xls. You'll get the automatic links notification, to
which respond "No". Chart looks fine.

Repeat with Chart_B.xls. This looks fine too.

Now navigate back to Chart_A. Hey presto! It's got chart_B's error
bars!

So it's not a macro problem. I haven't fully identified the scope of
the problem, but it's independent of chart file names and sheet names,
dependent on both having automatic links to "the same place" and being
in the same directory. Move Chart_D.xls to a different directory and
you'll see the problem goes away.

Neat, huh?



On Thu, 28 Sep 2006 18:46:04 -0400, "Jon Peltier"

Your macro shouldn't be going all the way to the root to get the error
bar
links. I would worry that the proper file was referenced in the first
place.
If you want to post the code we could do a little triage.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______



Got an odd one here.

I have suite of macros that create a workbook with a number of
sheets,
containing charts from a number of separate workbooks. The charts
show
a number of series, including a mean with custom error bars, taken
directly from two columns in the source data, spec for the + bars
='C:\Temp\[Some_File_Or_Other.xls]Data_Sheet'!$CK$2:$CK$366, and the
following column for the - bars.

I run the macro once, and save the resultant chart-filled workbook
as
DirectoryA/FileA.xls.

I change the data in the source workbooks, rerun the macro and save
the
results as DirectoryB/FileB.xls

I close Excel.

I open each file, and everything's fine.

I close Excel.

I move the file known as DirectoryB/FileB.xls to DirectoryA, so now
I
have FileA and FileB both in the same directory.

I open one of the two files. It looks fine.

I open the other file. It too looks fine.

I alt-tab back to file one, and it's now showing the error bars from
file two. Other series are okay, but the error bars are from the
most
recent file. I close the second file, no problem. I close the first
file, Excel asks if I want to save changes.

Normally, it's not not a problem. But I'm mailing these files out,
and
if they're opened directly from Outlook, they're in the same
directory,
and the recipients get very, very confused.

Any ideas for a simple fix?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top