• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Break external link without breaking formula

A

Atul kumar

New Member
How do I break external link without breaking formula?

"=(link to some other workbook) + c1"

when I break link, instead of it becoming "=constant + c1" it becomes a constant.

Any solution to this ?

Thanks
 
Hi Atul,

Please find attached sample files with excel VBA code to replace all Old links with New link in formulas. Extract all the files in a folder and Open "Master Workbook.xlsm" file to run ReplaceLinks Macro.

Code: 
Sub ReplaceLinks()
Dim Link As Variant
Dim NewWorkBook As String
Dim strNewWorkBook As String
' Run the Input Box method to collect New Workbook path
strNewWorkBook = Application.InputBox("Please Enter new workbook path.", _
   "New WorkBook", , , , "", , 2)
' Check to see if Input Box is empty. If Yes, procedure will end
If strNewWorkBook <> "" Then
For Each Link In ActiveWorkbook.LinkSources
'Replacing old links
ActiveWorkbook.ChangeLink Name:=Link, NewName:=strNewWorkBook, Type:=xlExcelLinks
Next Link
End If
MsgBox "All Links have been replaced!", vbInformation, "Completed"
End Sub

Note: while running VBA procedure, in the message box input new file name as “C:\Users\Mahesh\Desktop\New workbook.xlsx” (Please replace given folder path with your folder path)

Let me know if you have any question or need any further help in this regard.

Thanks,
Mahesh
 

Attachments

  • ReplaceLinks - Sample Files.zip
    26.5 KB · Views: 9
Last edited by a moderator:
Hi Mahesh

I asked about breaking link and not replacing link. Breaking link while maintaining internal formula

Mr. Mahesh said:
Hi Atul,

Please find attached sample files with excel VBA code to replace all Old links with New link in formulas. Extract all the files in a folder and Open "Master Workbook.xlsm" file to run ReplaceLinks Macro.

Code Procedure:
Sub ReplaceLinks()
Dim Link As Variant
Dim NewWorkBook As String
Dim strNewWorkBook As String
' Run the Input Box method to collect New Workbook path
strNewWorkBook = Application.InputBox("Please Enter new workbook path.", _
"New WorkBook", , , , "", , 2)
' Check to see if Input Box is empty. If Yes, procedure will end
If strNewWorkBook <> "" Then
For Each Link In ActiveWorkbook.LinkSources
'Replacing old links
ActiveWorkbook.ChangeLink Name:=Link, NewName:=strNewWorkBook, Type:=xlExcelLinks
Next Link
End If
MsgBox "All Links have been replaced!", vbInformation, "Completed"
End Sub

Note: while running VBA procedure, in the message box input new file name as “C:\Users\Mahesh\Desktop\New workbook.xlsx” (Please replace given folder path with your folder path)

Let me know if you have any question or need any further help in this regard.

Thanks,
Mahesh
Click to expand...
 
Hi Atul,

Please replace link text in formula using excel default Find and Replace option as below.
Find what :'C:\Users\Mahesh\Desktop\[Old workbook.xlsx]Sheet1'!
Replace : Leave it empty

Replace Link.PNG

Thanks
Mahesh
 
Atul Kumar,

Place it in a module and run it from the sheet from where you want to break the links.

Sub break_external_link()
ActiveSheet.Hyperlinks.Delete.EntireRow.Delete
End sub
 
Hi Mahesh,

Need a VBA code for excel which could replace the linked formula to it calculated value + the formula something like =IF(TRUE,25425.00,'[My Workbook.xls]Sheet1'!A2)
where A2=25425

OR

All formula in cell to get it calculated value and keeping the formula intact

And then get back to original state.

Please advise if this is possible

Shadab
 
You must log in or register to reply here.
Back
Top