[Solved] Delete Link in Files and update Data

M

mfaisal.ce

Member
Dear,

I have 03 files. Each file has same format.
1. A1 cell has date with formula "=Now()"
2. Each file is linked to get the data and file is auto genertaed daily.

Requirement.
1. A1 cell date to change from "=Now()" formula to date
2. Link to be disable or deleted because when the file is opened and it doesnot find the link, it gives error.

Purpose.

I need to get each file in a loop and get some selected data and store to another file. Problem, is whenever i run my loop to get the data; the file data is changed due to unavailable of link (because the file is relocated from server to local computer) and moreover, the date is also changed because it is "Now()" formula.

Kindly guide

Files are attached.
 

Attachments

  • DCS READING_01Nov2023000205.xlsx
    161.1 KB · Views: 1
  • DCS READING_02Nov2023000202.xlsx
    161 KB · Views: 1
  • DCS READING_03Nov2023000205.xlsx
    160.2 KB · Views: 1
mfaisal.ce said:
Dear,

I have 03 files. Each file has same format.
1. A1 cell has date with formula "=Now()"
2. Each file is linked to get the data and file is auto genertaed daily.

Requirement.
1. A1 cell date to change from "=Now()" formula to date
2. Link to be disable or deleted because when the file is opened and it doesnot find the link, it gives error.

Purpose.

I need to get each file in a loop and get some selected data and store to another file. Problem, is whenever i run my loop to get the data; the file data is changed due to unavailable of link (because the file is relocated from server to local computer) and moreover, the date is also changed because it is "Now()" formula.

Kindly guide

Files are attached.
Click to expand...
Dear Mfaisal.ce

Thanks for reaching out and posting your problem. You want to open several files with the same data format. You want to apply a date format within all sheets of a workbook. And you also wanted to remove or disable all links from a workbook.

I am presenting two Excel VBA sub-procedures to let you choose several Excel files using the File Dialog. Later, it will loop through each workbook. For each workbook, it will break or disable links, and later, it will loop through all sheets and apply a date format within cell A1.

Excel VBA Sub-procedure (Break Links):
Code: 
Sub ProcessWorkbooks()
  
    Dim fileDialog As fileDialog
    Dim selectedFiles As FileDialogSelectedItems
    Dim currentWb As Workbook
    Dim ws As Worksheet
    Dim targetRange As Range

    Set fileDialog = Application.fileDialog(msoFileDialogOpen)
    fileDialog.AllowMultiSelect = True
    fileDialog.Title = "Select Excel Files"
  
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
    If fileDialog.Show = -1 Then

        Set selectedFiles = fileDialog.SelectedItems
      
        For Each SelectedFile In selectedFiles

            Set currentWb = Workbooks.Open(SelectedFile)
          
            For Each ws In currentWb.Sheets
                ws.Range("A1").NumberFormat = "dd-mm-yyyy"
            Next ws
            currentWb.Save
          
            Dim link As Variant
            On Error Resume Next
          
            For Each link In currentWb.LinkSources
                currentWb.BreakLink Name:=link, Type:=xlLinkTypeExcelLinks
            Next link
          
            currentWb.Save
            currentWb.Close
        Next SelectedFile
      
    End If
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True

End Sub

Excel VBA Sub-procedure (Disable Links):
Code: 
Sub ProcessWorkbooks2()
  
    Dim fileDialog As fileDialog
    Dim selectedFiles As FileDialogSelectedItems
    Dim currentWb As Workbook
    Dim ws As Worksheet

    Set fileDialog = Application.fileDialog(msoFileDialogOpen)
    fileDialog.AllowMultiSelect = True
    fileDialog.Title = "Select Excel Files"
  
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
  
    If fileDialog.Show = -1 Then
        Set selectedFiles = fileDialog.SelectedItems
      
        For Each SelectedFile In selectedFiles
            Set currentWb = Workbooks.Open(SelectedFile)
          
            For Each ws In currentWb.Sheets
                ws.Range("A1").NumberFormat = "dd-mm-yyyy"
            Next ws
          
            currentWb.UpdateLinks = xlUpdateLinksNever
          
            currentWb.Save
            currentWb.Close
        Next SelectedFile
    End If
  
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True

End Sub

Important Note: We may lose data if we break or disable links.

Hopefully, the idea will help you. Good luck.

Regards
Lutfor Rahman Shimanto
 
Lutfor Rahman Shimanto said:
Dear Mfaisal.ce

Thanks for reaching out and posting your problem. You want to open several files with the same data format. You want to apply a date format within all sheets of a workbook. And you also wanted to remove or disable all links from a workbook.

I am presenting two Excel VBA sub-procedures to let you choose several Excel files using the File Dialog. Later, it will loop through each workbook. For each workbook, it will break or disable links, and later, it will loop through all sheets and apply a date format within cell A1.

Excel VBA Sub-procedure (Break Links):
Code: 
Sub ProcessWorkbooks()
 
    Dim fileDialog As fileDialog
    Dim selectedFiles As FileDialogSelectedItems
    Dim currentWb As Workbook
    Dim ws As Worksheet
    Dim targetRange As Range

    Set fileDialog = Application.fileDialog(msoFileDialogOpen)
    fileDialog.AllowMultiSelect = True
    fileDialog.Title = "Select Excel Files"
 
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
    If fileDialog.Show = -1 Then

        Set selectedFiles = fileDialog.SelectedItems
     
        For Each SelectedFile In selectedFiles

            Set currentWb = Workbooks.Open(SelectedFile)
         
            For Each ws In currentWb.Sheets
                ws.Range("A1").NumberFormat = "dd-mm-yyyy"
            Next ws
            currentWb.Save
         
            Dim link As Variant
            On Error Resume Next
         
            For Each link In currentWb.LinkSources
                currentWb.BreakLink Name:=link, Type:=xlLinkTypeExcelLinks
            Next link
         
            currentWb.Save
            currentWb.Close
        Next SelectedFile
     
    End If
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True

End Sub

Excel VBA Sub-procedure (Disable Links):
Code: 
Sub ProcessWorkbooks2()
 
    Dim fileDialog As fileDialog
    Dim selectedFiles As FileDialogSelectedItems
    Dim currentWb As Workbook
    Dim ws As Worksheet

    Set fileDialog = Application.fileDialog(msoFileDialogOpen)
    fileDialog.AllowMultiSelect = True
    fileDialog.Title = "Select Excel Files"
 
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
 
    If fileDialog.Show = -1 Then
        Set selectedFiles = fileDialog.SelectedItems
     
        For Each SelectedFile In selectedFiles
            Set currentWb = Workbooks.Open(SelectedFile)
         
            For Each ws In currentWb.Sheets
                ws.Range("A1").NumberFormat = "dd-mm-yyyy"
            Next ws
         
            currentWb.UpdateLinks = xlUpdateLinksNever
         
            currentWb.Save
            currentWb.Close
        Next SelectedFile
    End If
 
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True

End Sub

Important Note: We may lose data if we break or disable links.

Hopefully, the idea will help you. Good luck.

Regards
Lutfor Rahman Shimanto
Click to expand...
Thanks a lot for the help but it is not serving my purpose. Below is the error,

1699786959074.png

I have observed that the original file , if it is opened alone without any other excel being opened, it looks normal while if u open the file while any other excel file is opened, it gives the same error as shown above.

I am not able to understand the issue. Kindly guide,

Regards,
 
mfaisal.ce said:
I have observed that the original file , if it is opened alone without any other excel being opened, it looks normal while if u open the file while any other excel file is opened, it gives the same error as shown above.
Click to expand...
Dear MFaisal.ce

Thank you once again for your patience and for clarifying the intent. Keeping the data will be difficult as we remove all the links.

However, the Information or Observation you have shared brings hope. Currently, I am trying to solve this from another angle. When I am done, I will share the idea within this thread. Meanwhile, stay with ExcelDemy Forum.

Regards
Lutfor Rahman Shimanto
 
mfaisal.ce said:
Thanks a lot for the help but it is not serving my purpose. Below is the error,

View attachment 997

I have observed that the original file , if it is opened alone without any other excel being opened, it looks normal while if u open the file while any other excel file is opened, it gives the same error as shown above.

I am not able to understand the issue. Kindly guide,

Regards,
Click to expand...
Dear MFaisal.ce

I am delighted to inform you that I have successfully overcome the previous issue and fulfilled the intent. To be specific, I solve the problem in a tricky way. Thanks once again for your interesting observation, which is very helpful when solving the problem. This leads to another new observation I have found.

It is true that if the server-generated file is opened alone without any other Excel file being opened, it does not show the #NAME? error. If any other files are already opened while opening the server-generated file, it shows the #NAME? error.

New Observation: I have observed that if we open a copy of a server-generated file and later open the server-generated file (where we need to perform several tasks), the #NAME? error does not appear. Later, within the VBA Editor of the copy file, I ran my previous code and solved the problem.

Follow these steps.

Step 1: Make a copy of any server-generated file.
Make a copy of any file generated from server.gif​

Step 2: Open the copy file.
Open the copy file.gif​

Step 3: Later, open any server-generated file for comparison.

Note: The mentioned error does not appear when opening the server-generated file if the copy file has already been opened.
Step 4: Now, close the server-generated file to start our main task.

Step 5: Activate the copy file => Go to the Developer tab => Click on Insert followed by Module => Paste the following code in the module => You will see the Run button above the module like the following GIF.
Code: 
Sub ProcessWorkbooks()
  
    Dim fileDialog As fileDialog
    Dim selectedFiles As FileDialogSelectedItems
    Dim currentWb As Workbook
    Dim ws As Worksheet
    Dim targetRange As Range

    Set fileDialog = Application.fileDialog(msoFileDialogOpen)
    fileDialog.AllowMultiSelect = True
    fileDialog.Title = "Select Excel Files"
    
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
    If fileDialog.Show = -1 Then

        Set selectedFiles = fileDialog.SelectedItems
      
        For Each SelectedFile In selectedFiles

            Set currentWb = Workbooks.Open(SelectedFile)
          
            For Each ws In currentWb.Sheets
                ws.Range("A1").NumberFormat = "dd-mm-yyyy"
            Next ws
            currentWb.Save
          
            Dim link As Variant
            On Error Resume Next
          
            For Each link In currentWb.LinkSources
                currentWb.BreakLink Name:=link, Type:=xlLinkTypeExcelLinks
            Next link
          
            currentWb.Save
            currentWb.Close
        Next SelectedFile
      
    End If
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True

End Sub

Step 6: Click on Run => Choose the intended files => After executing the code, open the server-generated files and get the desired result.

I have also attached the files as well. Hopefully, the idea has reached your goal. Stay blessed.

Regards
Lutfor Rahman Shimanto
 

Attachments

  • DCS READING_01Nov2023000205.xlsx
    126.3 KB · Views: 0
  • DCS READING_02Nov2023000202.xlsx
    126.2 KB · Views: 0
  • DCS READING_03Nov2023000205.xlsx
    125.6 KB · Views: 0
Lutfor Rahman Shimanto said:
Dear MFaisal.ce

I am delighted to inform you that I have successfully overcome the previous issue and fulfilled the intent. To be specific, I solve the problem in a tricky way. Thanks once again for your interesting observation, which is very helpful when solving the problem. This leads to another new observation I have found.

It is true that if the server-generated file is opened alone without any other Excel file being opened, it does not show the #NAME? error. If any other files are already opened while opening the server-generated file, it shows the #NAME? error.

New Observation: I have observed that if we open a copy of a server-generated file and later open the server-generated file (where we need to perform several tasks), the #NAME? error does not appear. Later, within the VBA Editor of the copy file, I ran my previous code and solved the problem.

Follow these steps.

Step 1: Make a copy of any server-generated file.

Step 2: Open the copy file.

Step 3: Later, open any server-generated file for comparison.

Note: The mentioned error does not appear when opening the server-generated file if the copy file has already been opened.
Step 4: Now, close the server-generated file to start our main task.

Step 5: Activate the copy file => Go to the Developer tab => Click on Insert followed by Module => Paste the following code in the module => You will see the Run button above the module like the following GIF.
Code: 
Sub ProcessWorkbooks()
 
    Dim fileDialog As fileDialog
    Dim selectedFiles As FileDialogSelectedItems
    Dim currentWb As Workbook
    Dim ws As Worksheet
    Dim targetRange As Range

    Set fileDialog = Application.fileDialog(msoFileDialogOpen)
    fileDialog.AllowMultiSelect = True
    fileDialog.Title = "Select Excel Files"
   
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False
    If fileDialog.Show = -1 Then

        Set selectedFiles = fileDialog.SelectedItems
     
        For Each SelectedFile In selectedFiles

            Set currentWb = Workbooks.Open(SelectedFile)
         
            For Each ws In currentWb.Sheets
                ws.Range("A1").NumberFormat = "dd-mm-yyyy"
            Next ws
            currentWb.Save
         
            Dim link As Variant
            On Error Resume Next
         
            For Each link In currentWb.LinkSources
                currentWb.BreakLink Name:=link, Type:=xlLinkTypeExcelLinks
            Next link
         
            currentWb.Save
            currentWb.Close
        Next SelectedFile
     
    End If
    Application.DisplayAlerts = True
    Application.AskToUpdateLinks = True

End Sub

Step 6: Click on Run => Choose the intended files => After executing the code, open the server-generated files and get the desired result.

I have also attached the files as well. Hopefully, the idea has reached your goal. Stay blessed.

Regards
Lutfor Rahman Shimanto
Click to expand...

Thanks a lot ... It worked.

Regards,
 
You must log in or register to reply here.

Online statistics

Members online
0
Guests online
38
Total visitors
38
Totals may include hidden visitors.

Forum statistics

Threads
292
Messages
1,268
Members
531
Latest member
lonkfps

Share this page

Top