Delete Link without Opening

[mfaisal.ce]

Dear,

1. Is it possible to delete the link of excel file without opening. My issue is that I have a file which has a link and I programically open it and take some data. It is giving error when it is open because the file is moved to another computer and link is not present. If I delete the link manually after opening it then it is working fine. (File Attached)

2. I import data from one excel file to another. The file from which data is to imported is Macro-Enabled, the data is not imported even I refresh many times.

Kindly guide,

Regards,

 

[Lutfor Rahman Shimanto]

Dear,

1. Is it possible to delete the link of excel file without opening. My issue is that I have a file which has a link and I programically open it and take some data. It is giving error when it is open because the file is moved to another computer and link is not present. If I delete the link manually after opening it then it is working fine. (File Attached)

2. I import data from one excel file to another. The file from which data is to imported is Macro-Enabled, the data is not imported even I refresh many times.

Kindly guide,

Regards,

Hello Mfaisal

Thanks for reaching out. The second issue you describe seems to be a part of another problem. Well, your mentioned second issue may occur for several reasons.

I am recommended to check both the source and destination file locations. Ensure the files are in locations which can be accessible. Also, check the paths specified in your code are accurate.

As mentioned, you are working with Macro-Enabled files. So, ensure that your Macro Security settings are not blocking the execution of macros.

I hope this will overcome your second issue. I am working on your first problem currently. When I am done, I will share the idea in this thread. Good luck!

Regards
Lutfor Rahman Shimanto

 

[Lutfor Rahman Shimanto]

1. Is it possible to delete the link of excel file without opening. My issue is that I have a file which has a link and I programically open it and take some data. It is giving error when it is open because the file is moved to another computer and link is not present. If I delete the link manually after opening it then it is working fine. (File Attached)

Dear Mfaisal

Thanks once again for posting your query with such clarity. After investigating the issues you described, I found that you want to remove a link from a workbook without manually opening the workbook. While removing all links, you want to keep previously returned data from those links.

I am delighted to inform you that I am successfully implemented some Excel VBA procedures to reach your goal. All these procedures together work perfectly in achieving your goal. All you need to do is to paste these procedures into a module and run RemoveLinksWithoutOpenning.

Excel VBA Procedures:

Sub DisableExternalLinksWarning()
 
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
    
End Sub

Sub EnableExternalLinksWarning()

    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True

End Sub

Sub RemoveLink()

    ActiveWorkbook.BreakLink Name:="ABBDataDirectSystem800xA.xlam", Type:=xlExcelLinks
    ChDir "C:\Users\Lutfor Rahman\Downloads\Forum"
    
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\Lutfor Rahman\Downloads\Forum\DCS READING_16Aug2023000204.xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        
    ActiveWorkbook.Close

End Sub

Sub RemoveLinksWithoutOpenning()

    Dim filePath As String
    Dim targetWorkbook As Workbook

    Application.ScreenUpdating = False
    Call DisableExternalLinksWarning

    filePath = "C:\Users\Lutfor Rahman\Downloads\Forum\DCS READING_16Aug2023000204.xlsx"

    Set targetWorkbook = Workbooks.Open(filePath, True, False)

    targetWorkbook.Activate

    Call RemoveLink

    Call EnableExternalLinksWarning
    Application.ScreenUpdating = True

End Sub

Things to Keep in Mind:

• Change the file path in provided code when running within your system.
• Ensure the workbook must have one or more links. Otherwise, it will raise an Error.

Regards
Lutfor Rahman Shimanto