Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Curse of the werechart (Excel 2003)

    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!

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Curse of the werechart (Excel 2003)

    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>
    Regards,
    Rudi

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curse of the werechart (Excel 2003)

    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.

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Curse of the werechart (Excel 2003)

    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!
    Regards,
    Rudi

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Curse of the werechart (Excel 2003)

    <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
    Jerry

  6. #6
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curse of the werechart (Excel 2003)

    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.

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Curse of the werechart (Excel 2003)

    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")
    Jerry

  8. #8
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curse of the werechart (Excel 2003)

    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.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Curse of the werechart (Excel 2003)

    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

  10. #10
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curse of the werechart (Excel 2003)

    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?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Curse of the werechart (Excel 2003)

    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

  12. #12
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curse of the werechart (Excel 2003)

    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!

  13. #13
    Star Lounger
    Join Date
    Apr 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Curse of the werechart (Excel 2003)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •