CODEWITHSUNDEEP
excelvbadiagram
Matthias Pospiech
Matthias Pospiech

Reputation: 3488

remove external links in excel diagramm

I have copied a whole table sheet from one excel document to another. The diagramm in that sheet was copied as well.

However the data in the diagramm refers to the other excel document and not to the current sheet.

That means that the link does look like

'C:\LokaleBilder\[P3-20x]Tabelle1'!$B$3:$B$403

instead of 

'20x-(Kreuz)'!$B$3:$B$403

Note that the sheet name has changed too.

If this was fixable with some vba code I would like to know how.

EDIT:

Note that these are not hyperlinks, its links do documents.

I tried to work on it by removing the document string. that fails however:

Dim currSheet As String
currSheet = ActiveSheet.Name

ActiveSheet.ChartObjects("Diagramm 1").Activate

Dim xSer As Series
Dim xvalueStr As String
Dim valueStr As String
Dim m As Integer
For m = 1 To ActiveChart.SeriesCollection.Count
    xvalueStr = ActiveChart.SeriesCollection(m).XValues

with

data types do not match

in the last line

Edit2: I could find out that xvalues is of datatype Range. I could however not find out how to modify this Range datatype.

Upvotes: 0

Views: 1287

Answers (2)

Matthias Pospiech
Matthias Pospiech

Reputation: 3488

I solved the problem with the value .Formula

Option Explicit

Sub MainRemoveDocumentLinks()

ActiveSheet.ChartObjects("Diagramm 1").Activate

Dim xSer As Series
Dim valueStr As String
Dim m As Integer
For m = 1 To ActiveChart.SeriesCollection.Count
    valueStr = ActiveChart.SeriesCollection(m).Formula
    ActiveChart.SeriesCollection(m).Formula = replaceSeriesLink(valueStr)
    Debug.Print ActiveChart.SeriesCollection(m).Formula
Next

End Sub

Function replaceSeriesLink(inputStr As String) As String

Dim currSheet As String
currSheet = ActiveSheet.Name

Dim pos As Integer
Dim pos_old As Integer

pos = 1
pos_old = 0

Dim pos_start As Integer
Dim pos_end As Integer

pos_start = 0
pos_end = 0

Do While pos > 0
    pos = InStr(pos + 1, inputStr, "'")
    If pos_old = pos Then
        Exit Do
    End If
    If pos_start = 0 Then
        pos_start = pos
    Else
        pos_end = pos
        Dim DatalinkToReplace As String
        DatalinkToReplace = Mid(inputStr, pos_start + 1, pos_end - pos_start - 1)
        inputStr = Replace(inputStr, DatalinkToReplace, currSheet)
        Debug.Print inputStr
        pos_start = 0
    End If

    pos_old = pos
Loop

replaceSeriesLink = inputStr

End Function

Upvotes: 0

NickSlash
NickSlash

Reputation: 5100

I've had a quick go at trying to reproduce what (I think) you're doing.

I think you selected the whole sheet, copied and then pasted the lot into cell A1 of your second workbook. In my test it copied the data and the chart but the chart remained linked to the data in the source workbook.

If you do want to copy the entire worksheet to another workbook and keep any charts linked to the copied data and not the source, I think using the move or copy feature will let you acheive this.

Right click your worksheet's tab and select move or copy. In the dialogue that appears, select your second workbook in the dropdown box, the position you want the sheet to be in using the listbox and then check the "Create a Copy" box.

move or copy

If that does solve your issue, and its a process you need to repeat regularly, you could use the macro recorder to automate it. You might need to modify the macro slightly but it should show you how to programatically acheive your copy.

Upvotes: 0

Related Questions