VBA - to change all the source links of linked objects at once

[cheongmarcus]

Hi everyone,

I have copied and pasted as link (worksheet object) from excel to powerpoint, but now that i shifted the excel to another location (e.g. from Documents to Desktop), the file source path has changed.

Is there any way to change all the links at once rather than clicking each link > 'change source' > select the excel?

e.g. i have these few source path:
1. C:\Users\cheong\Documents\data.xlsx!Sheet1!A1:B3
2. C:\Users\cheong\Documents\data.xlsx!Sheet1!A1:A5
3. C:\Users\cheong\Documents\data.xlsx!Sheet2!A1:E8

and now i want to change all these at once to:
1. C:\Users\cheong\Desktop\data.xlsx!Sheet1!A1:B3
2. C:\Users\cheong\Desktop\data.xlsx!Sheet1!A1:A5
3. C:\Users\cheong\Desktop\data.xlsx!Sheet2!A1:E8

As you can see, the only difference is "Documents" to "Desktop".

I tried searching online but cant find any reproducible solutions. One of which changes but it removes part of the reference i.e. the "Sheet1!A1:B3" from the link. So it becomes C:\Users\cheong\Desktop\data.xlsx instead, which is not what I am looking for.

As such, would like to pick some brains here to help me. Thank you!

 

[Nguyen Anh Dung]

you can use code as below: repleace "Documents" to "Desktop".

Option Explicit

Sub change_character()
    Dim wb_new As Workbook
    Dim lr As Long
    Dim i As Long, j As Long
    Dim path As String, fpath As String
    Dim arr As Variant
    Dim char_old As String, char_new As String
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
    path = ThisWorkbook.path
    arr = GetFileNames(path)
    char_old = Application.InputBox("Nh" & ChrW(7853) & "p Chu" & ChrW(7895) & "i C" & ChrW(7847) & "n Thay Th" & ChrW(7871) & ":")
    char_new = Application.InputBox("Nh" & ChrW(7853) & "p chu" & ChrW(7895) & "i mu" & ChrW(7889) & "n thay th" & ChrW(7871) & ":")
    For i = 1 To UBound(arr) - 1
        For Each wb_new In Workbooks
            If wb_new.Name = arr(i) Then
                lr = Workbooks(wb_new.Name).Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
                lr = Workbooks(wb_new.Name).Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
                For j = 2 To lr
                    Range("N" & j) = Replace(Range("A" & j), char_old, char_new)
                    Range("O" & j) = Replace(Range("B" & j), char_old, char_new)
                Next j
            Else
                fpath = path & "\" & arr(i)
                Set wb_new = Workbooks.Open(fpath)
                lr = Workbooks(wb_new.Name).Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
                lr = Workbooks(wb_new.Name).Sheets(1).Range("B" & Rows.Count).End(xlUp).Row
                For j = 2 To lr
                    Range("A" & j) = Replace(Range("A" & j), char_old, char_new)
                    Range("B" & j) = Replace(Range("B" & j), char_old, char_new)
                Next j
                wb_new.Close True
            End If
        Next wb_new
    Next i
    MsgBox "Ok r" & ChrW(7891) & "i nha!!!"
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
End Sub

Function GetFileNames(ByVal FolderPath As String) As Variant
Dim Result As Variant
Dim i As Integer
Dim MyFile As Object
Dim MyFSO As Object
Dim MyFolder As Object
Dim MyFiles As Object
Set MyFSO = CreateObject("Scripting.FileSystemObject")
Set MyFolder = MyFSO.GetFolder(FolderPath)
Set MyFiles = MyFolder.Files
ReDim Result(1 To MyFiles.Count)
i = 1
For Each MyFile In MyFiles
Result(i) = MyFile.Name
i = i + 1
Next MyFile
GetFileNames = Result
End Function

 

[cheongmarcus]

thank you @Nguyen Anh Dung , but when i run the macro, it says "Compile Error: User-defined type not defined. You have any idea what it means?

 

[Nguyen Anh Dung]

thank you @Nguyen Anh Dung , but when i run the macro, it says "Compile Error: User-defined type not defined. You have any idea what it means?

you edit code:
Range("N" & j) = Replace(Range("A" & j), char_old, char_new)->Range("A" & j) = Replace(Range("A" & j), char_old, char_new)
Range("O" & j) = Replace(Range("B" & j), char_old, char_new)->Range("B" & j) = Replace(Range("B" & j), char_old, char_new)
you copy file macro from folder have file need edit after run

 

[BuJay]

I have this exact same question but I only need to update the file name reference (and I can't understand that above solutions)

Can anyone help?

Current PowerPoint has hundreds of visualizations linked to an excel source file named
\\corp\sites\RIB1001\HLSCreditRisk\0_Property_Secured_Credit_MIS\0_Master_Reports\heloc\aqr\Perf_Prep_Work\043023\051623\excel_test_v1.0.1.xlsx

Want to have a VBA script that changes the linked objet references from the above to the below
\\corp\sites\RIB1001\HLSCreditRisk\0_Property_Secured_Credit_MIS\0_Master_Reports\heloc\aqr\Perf_Prep_Work\043023\051623\excel_test_v1.0.2.xlsx

The only thing that changes is the v1.0.1 changes to v1.0.2

Can't do this manually as there are too many charts in the powerpoint