Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Bug! Error Bars Inherited from Another Workbook - Any Fix?
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? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Bug! Error Bars Inherited from Another Workbook - Any 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Spiggy Topes" wrote in message oups.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? |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Bug! Error Bars Inherited from Another Workbook - Any Fix?
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" wrote: 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" wrote in message roups.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? |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Bug! Error Bars Inherited from Another Workbook - Any Fix?
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" wrote in message ... 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" wrote: 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" wrote in message groups.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? |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Bug! Error Bars Inherited from Another Workbook - Any Fix?
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" wrote in message ... 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" wrote in message ... 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" wrote: 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" wrote in message egroups.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? |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
Bug! Error Bars Inherited from Another Workbook - Any Fix?
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 Peltier wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote: 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" wrote in message egroups.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? |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
Bug! Error Bars Inherited from Another Workbook - Any Fix?
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" wrote in message ps.com... 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 Peltier wrote: 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" wrote in message ... 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" wrote in message ... 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" wrote: 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" wrote in message egroups.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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to merge monthly workbooks into a quarterly workbook?? | Excel Worksheet Functions | |||
excel exits unexpectedly or hangs the second time I open workbook | Excel Discussion (Misc queries) | |||
Repost:Automatically inserting a row in external workbook | Excel Discussion (Misc queries) | |||
keeping a toolbar attached to a workbook | Excel Discussion (Misc queries) | |||
How to hyperlink from a workbook to sheets in another workbook? | Excel Worksheet Functions |