Curse of the werechart (Excel 2003)

[mrtr]

I hope someone can help me with this. I havent found any positive research, but it doesnt make sense. I have these great charts, but when I open the workbook in the wee hours of the night, something terrible happens...the formatting of the data is lost.

Some few nights ago (I think it was a full moon) I decided to take a very large workbook and divide it up into separate workbooks. This resulted in five "sub" workbooks of considerable size with vast amounts of data particular to each unique workbook within. I also wound up with one unique workbook- a book to hold all the charts for the others unique data books. This book was linked to the data.

Now, these charts have graphics to be proud of, but some have table data as well. That was when I first noticed something was wrong. The charts themselves seem to bring over the formatting fine, at least along the y axis, but the table data becomes a number/serial version of any data that is a percentage or date- not the original defined data format. They have "changed" somehow.

The next day I reopened the main chart workbook and the associated "sub" workbooks and alas! It was fine again! The formatting was back to normal. It was the chart I knew and loved.

This must be some kind of a werechart, because every night it resumes its grotesque form, hideous and vile, while in the light of day they are the charts I love and adore again.

Can someone help me restore my charts to their rightful form? Why do they keep changing formats in this manner? Why is the y axis always fine? How can I take a considerable amount of charts associated with many linked workbooks and present them in an intelligent, sensible manner?

Help!

[Rudi]

It looks like this post "scared" off most of the possible replies.... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

[mrtr]

Ugh. Maybe I wasnt clear enough? Or maybe everyone knows something I dont? I ran some more tests. I can go into chart options and check "Category X Axis" and the x axis will then show the correct date above the data table, but the table still defaults to General format, unless I open the linked sheet. This doesnt do much good to external users. The only option seems to be to get rid of the table, but it really is needed.

[Rudi]

I really wish I could help, but I got lost half way through your original post! I myself was hoping for some more informed reply to try and catch the context of your problem.

It could help to create a smaller scaled sample and see if the problem persists. If you find similar issues, you could then post the file and get some resposes that way!

[Jezza]

<img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

Will you be distributing these workbooks together, if not you will have to consolidate them. If not you could set instructions that they have to be put in the same folder and use a macro to update the links, something similar to :

Sub OpenUpdate()
Workbooks.Open FileName:= _
"C:My DocumentsLinkedBook.xls", UpdateLinks:=xlUpdateLinksAlways
End Sub

[mrtr]

That was the gist- I only wanted to distribute the one workbook with the charts. The others together are huge and sending them all, or opening them all for each user would just be a limitation.

[Jezza]

oooh, I see your problem. So you are stuck between a rock and a hard place. <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

Maybe a re-think in the way you have set up the wb's I would think. If your data is held internally to the wb holding the charts, I suppose you could copy the range of data that is holding the links and then Paste Special and select Values. This would break the links and just put the core data in the wb that holds the charts.

Withoutout know the complexity of your solution that is a "quick and dirty" way to get the job done. (you can always have a back up copy and do this as a "Save As")

[mrtr]

Thats where I was heading to. Excel just cannot remember table data formats without the original source data present. That wont help me, but its a realization.

[sdckapr]

If I understand the problem: You have created a chart which is linked to several workbooks, and when you open that workbook with the other workbooks closed (or not present) the formatting of the datatable is "general" and not the formatting in the external workbooks.

The problem seems that excel can link numbers with an external closed database, but not formatting.

The solution is to open the other workbooks at the same time, or if they will not be available to the other users, it becomes even simpler: don't use a chart linked to external data they don't have access to. You can just send to them a sheet with a chart picture linked to the chart instead of a copy of the linked chart. You can copy the chart, then edit - pastespecial, select "link" and choose picture

If it does not need to be linked, you can paste-special picture without the link...

Steve

[mrtr]

That was another thought I was toying around with. I have a great number of charts though. Any help with a piece of VBA code that can take all the charts in the sheet and copy and paste them as images into another sheet?

[HansV]

Try this as starting point. You can expand it as needed.

Sub CopyCharts()
Dim wshSource As Worksheet
Dim wbkTarget As Workbook
Dim wshTarget As Worksheet
Dim cht As ChartObject
Dim strAddr As String
Set wshSource = ActiveSheet
Set wbkTarget = Workbooks.Add(xlWBATWorksheet)
Set wshTarget = wbkTarget.Worksheets(1)
For Each cht In wshSource.ChartObjects
strAddr = cht.TopLeftCell.Address
cht.CopyPicture
wshTarget.Paste Destination:=wshTarget.Range(strAddr)
Next cht
Set cht = Nothing
Set wshTarget = Nothing
Set wbkTarget = Nothing
Set wshTarget = Nothing
End Sub

[mrtr]

Thanks Hans, can you also help me with this idea? I want to see how it works if I do open the associated workbooks (minimized) when the user open the main one. I have this from a previous post, but how do I maximize the original workbook opened? The first workbook will be called "Charts". Once I open "Charts" I run this code:

Sub Workbook_Open()

Sub Workbook_Open()

Workbooks.Open Filename:= _
"C:FirstFileExcel.xls", UpdateLinks:=xlUpdateLinksAlways
ActiveWindow.WindowState = xlMinimized

Workbooks.Open Filename:= _
"C:SecondFileExcel.xls", UpdateLinks:=xlUpdateLinksAlways
ActiveWindow.WindowState = xlMinimized

Workbooks.Open Filename:= _
"C:ThirdFileExcel.xls", UpdateLinks:=xlUpdateLinksAlways
ActiveWindow.WindowState = xlMinimized

End Sub


Then I want to make sure "Charts" is finally maximized for the user.

Thanks!

[mrtr]

I got it- and your code worked great. Now I just have to work out the placement and formatting.

All in One:

Sub Workbook_Open()

Workbooks.Open Filename:= _
"C:FileOneExcel1.xls", UpdateLinks:=xlUpdateLinksAlways
ActiveWindow.WindowState = xlMinimized

Workbooks.Open Filename:= _
"C:FileTwoExcel2.xls", UpdateLinks:=xlUpdateLinksAlways
ActiveWindow.WindowState = xlMinimized

Workbooks.Open Filename:= _
"C:FileThreeExcel3.xls", UpdateLinks:=xlUpdateLinksAlways
ActiveWindow.WindowState = xlMinimized

ActiveWindow.WindowState = xlMaximized


End Sub