macro to open all links entered in cells to another files

[Neomatrix]

Hi,
I need to open links to another files with one button using macro. Links to another files are in D column, they will be changed after few months to another files. Sometimes is 3 links, sometimes 6. But all links entered in cells should be opened with one button. I have managed to record macro, but if I change links to another files, it opens old link, not new ones.
Thanks

 

[Jeffrey Mahoney]

Please try this code

Sub OpenExternalLinks()
  Dim aLinks As Variant
  Dim i As Long
  
  aLinks = ThisWorkbook.LinkSources(xlExcelLinks)
  If Not IsEmpty(aLinks) Then
      For i = 1 To UBound(aLinks)
        Workbooks.Open Filename:=aLinks(i)
      Next i
  End If
  
End Sub

 

[Neomatrix]

Please try this code

Sub OpenExternalLinks()
  Dim aLinks As Variant
  Dim i As Long
 
  aLinks = ThisWorkbook.LinkSources(xlExcelLinks)
  If Not IsEmpty(aLinks) Then
      For i = 1 To UBound(aLinks)
        Workbooks.Open Filename:=aLinks(i)
      Next i
  End If
 
End Sub

Morning, it is working, only thing is that last file on my screen should be primary workbook. Can you do this? Thank you sooooooooooooo much!

 

[Neomatrix]

Morning, it is working, only thing is that last file on my screen should be primary workbook. Can you do this? Thank you sooooooooooooo much!

Now I have checked by adding another sheet inside file and it is not working. It opens all links inside workbook, if I add another sheet with links on this sheet. I want only links to be opened from column D from sheet "Instructions+links". Last file on my screen should be primary workbook

 

[Jeffrey Mahoney]

I added the last line so that your main WB has focus.

Sub OpenExternalLinks()
  Dim aLinks As Variant
  Dim i As Long
  
  aLinks = ThisWorkbook.LinkSources(xlExcelLinks)
  If Not IsEmpty(aLinks) Then
      For i = 1 To UBound(aLinks)
        Workbooks.Open Filename:=aLinks(i)
      Next i
  End If
  
  ThisWorkbook.Activate
  
End Sub

 

[Neomatrix]

I added the last line so that your main WB has focus.

Sub OpenExternalLinks()
  Dim aLinks As Variant
  Dim i As Long
 
  aLinks = ThisWorkbook.LinkSources(xlExcelLinks)
  If Not IsEmpty(aLinks) Then
      For i = 1 To UBound(aLinks)
        Workbooks.Open Filename:=aLinks(i)
      Next i
  End If
 
  ThisWorkbook.Activate
 
End Sub

Hi Jeffrey, Thank you, this code opens all links from the workbook, even if you add another sheet with new link, it will open and this one. My task is to open links only those entered on this sheet. You can rename this sheet to: "Master+links". Currently it is named "Instructions+links". Last part of code works perfectly. Thank you

 

[Jeffrey Mahoney]

Here is some code to open all the links you have in formulas on the Master+Links sheet. I created a named range called LinkList that holds all the links on that sheet and used it in the macro.

I replaced some of the strings below to hide personal info and to not create a hyperlink

For me, I have files in my documents folders that are stored on One Drive. When the files are not open the path looks something like this:
='httpx://xx365-my.sharepoint.com/personal/jeffrey_mahoney_xx_com/Documents/Desktop/[Junk.xlsm]Sheet1'!$B$1
But the actual folder when you use File explorer is:
C:\Users\xxxxxx\OneDrive - xx\Desktop\

In the code below I stored the one drive folder in a variable because it is different for you: OneDriveFolder = "OneDrive - xx\"

Sub OpenLinksFromList()
  Dim Cel As Range
  Dim LinkList As Range
  Dim UserFolder As String
  Dim OneDriveFolder As String
  Dim aStr As String
  Dim s As Long
  Dim x As Long
  Dim b As Long
  Dim d As Long
  Dim a As Long
  Dim sFolder As String
  Dim FileName As String
  Dim PathFile As String
  Dim Path As String
 
 
  UserFolder = Environ$("USERPROFILE") & "\"
  OneDriveFolder = "OneDrive - xx\"
  Set LinkList = ThisWorkbook.Worksheets("Master+Links").Range("LinkList")
 
  For Each Cel In LinkList
    aStr = Cel.Formula2
    b = InStr(aStr, "[")
    d = InStr(aStr, "]")
    a = InStr(aStr, "'")
    x = InStrRev(aStr, "\")
    FileName = Mid(aStr, b + 1, d - 1 - b)
   
    If b = 2 Or b = 3 Then     'File is open already
      'Do nothing
   
    ElseIf InStr(aStr, "http") > 0 And InStr(aStr, "sharepoint") > 0 Then 'path is for user folder saved on sharepoint
      s = InStr(aStr, "Documents/")
      If s > 0 Then
        s = s + 10
        sFolder = Mid(aStr, s, b - 1 - s) & "\"
        PathFile = UserFolder & OneDriveFolder & sFolder & FileName
        Workbooks.Open FileName:=PathFile
      End If
   
    ElseIf a = 2 Then                                         'path and file name to be opened
      Path = Mid(aStr, a + 1, x - a)
      PathFile = Path & FileName
      Workbooks.Open FileName:=PathFile
    End If
     
  Next Cel
 
  ThisWorkbook.Activate
 
End Sub

 

[Neomatrix]

Here is some code to open all the links you have in formulas on the Master+Links sheet. I created a named range called LinkList that holds all the links on that sheet and used it in the macro.

I replaced some of the strings below to hide personal info and to not create a hyperlink

For me, I have files in my documents folders that are stored on One Drive. When the files are not open the path looks something like this:
='httpx://xx365-my.sharepoint.com/personal/jeffrey_mahoney_xx_com/Documents/Desktop/[Junk.xlsm]Sheet1'!$B$1
But the actual folder when you use File explorer is:
C:\Users\xxxxxx\OneDrive - xx\Desktop\

In the code below I stored the one drive folder in a variable because it is different for you: OneDriveFolder = "OneDrive - xx\"

Sub OpenLinksFromList()
  Dim Cel As Range
  Dim LinkList As Range
  Dim UserFolder As String
  Dim OneDriveFolder As String
  Dim aStr As String
  Dim s As Long
  Dim x As Long
  Dim b As Long
  Dim d As Long
  Dim a As Long
  Dim sFolder As String
  Dim FileName As String
  Dim PathFile As String
  Dim Path As String
 
 
  UserFolder = Environ$("USERPROFILE") & "\"
  OneDriveFolder = "OneDrive - xx\"
  Set LinkList = ThisWorkbook.Worksheets("Master+Links").Range("LinkList")
 
  For Each Cel In LinkList
    aStr = Cel.Formula2
    b = InStr(aStr, "[")
    d = InStr(aStr, "]")
    a = InStr(aStr, "'")
    x = InStrRev(aStr, "\")
    FileName = Mid(aStr, b + 1, d - 1 - b)
  
    If b = 2 Or b = 3 Then     'File is open already
      'Do nothing
  
    ElseIf InStr(aStr, "http") > 0 And InStr(aStr, "sharepoint") > 0 Then 'path is for user folder saved on sharepoint
      s = InStr(aStr, "Documents/")
      If s > 0 Then
        s = s + 10
        sFolder = Mid(aStr, s, b - 1 - s) & "\"
        PathFile = UserFolder & OneDriveFolder & sFolder & FileName
        Workbooks.Open FileName:=PathFile
      End If
  
    ElseIf a = 2 Then                                         'path and file name to be opened
      Path = Mid(aStr, a + 1, x - a)
      PathFile = Path & FileName
      Workbooks.Open FileName:=PathFile
    End If
    
  Next Cel
 
  ThisWorkbook.Activate
 
End Sub

Thank you!

 

[smozgur]

Thank you!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.