Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Removing Links

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Everyone,

    I've posted this question on some Excel newsgroups and websites and haven't had that much luck. So, this is my final shot before I totally give up. I have a excel spreadsheet with 7 small charts on it. They all link to a separate spreadsheet that has data on it. I want to place the spreadsheet with the 7 small charts on a server so my co-workers can access it. But, a message appears
    asking if I want to update the links. Does anyone have any ideas on how to
    get rid of these links?

    I've tries this code from a excel website (http://www.geocities.com/jonpeltier/...ChartData.html) but keep on coming up with "run-time error '1004'. Unable to set the XValues property of the Series Class":

    Sub DelinkChartFromData()
    ''' Thanks to Tushar Mehta
    Dim mySeries As Series
    Dim sChtName As String

    ''' Make sure a chart is selected
    On Error Resume Next
    sChtName = ActiveChart.Name
    If Err.Number <> 0 Then
    MsgBox "This functionality is available only for charts " _
    & "or chart objects"
    Exit Sub
    End If
    If TypeName(Selection) = "ChartObject" Then
    ActiveSheet.ChartObjects(Selection.Name).Activate
    End If
    On Error GoTo 0

    ''' Loop through all series in active chart
    For Each mySeries In ActiveChart.SeriesCollection
    '''' Convert X and Y Values to arrays of values
    mySeries.XValues = mySeries.XValues
    mySeries.Values = mySeries.Values
    Next mySeries
    End Sub

    If anyone has any idea on how to do this or any other suggestion, please let me know.
    Thanks
    Lou K

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Minnesota
    Posts
    821
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One: Tools, Options, Edit
    then uncheck the "Ask to update automatic links" box

    Two: Is there any reason why the spreadsheet with the data is not part of the spreadsheet with the charts?

    If you don't want the users to view the actual data, you could hide the spreadsheet with Format, Sheet, Hide

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    363
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I wrote a neat little utility that will remove links. Let me know if you're interested and I'll send it to you
    It's never too late to learn something new.

    Ricky

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Ricky,

    Sure! Let me give that a shot. My e-mail address is ljkelly@asu.edu

    Thanks

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If you sourced your charts from PivotTables that were in turn sourced from each of your 7 worksheets there wouldn't be a link problem.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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