Johniny

Link manipulation

Mar 8th, 2020
585
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Dim myLinks As Variant
  2. Dim targetLinks As Variant
  3.  
  4. Sub getLinks()
  5.  
  6. Dim i As Integer
  7.  
  8. myLinks = ThisWorkbook.LinkSources(xlExcelLinks) 'https://docs.microsoft.com/en-us/office/vba/api/excel.xllink
  9. 'ThisWorkbook.UpdateLink( null , xlLinkTypeOLELinks) 'https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.updatelink
  10. 'https://www.mrexcel.com/board/threads/vba-open-source-links.345232/
  11. Debug.Print "Link list:" & vbNewLine & Join(myLinks, vbNewLine) & vbNewLine & "End List" 'https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/join-function
  12.  
  13. Call filterLinks
  14. Call updateSpecificLinks
  15.  
  16. MsgBox "Done!"
  17.  
  18. End Sub
  19.  
  20. Sub filterLinks()
  21.  
  22. Dim criterion As String
  23. criterion = "Documents"
  24.  
  25. targetLinks = Filter(myLinks, criterion, True) 'https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filter-function
  26. Debug.Print "Target link list:" & vbNewLine & Join(targetLinks, vbNewLine) & vbNewLine & "End List"
  27.  
  28. End Sub
  29.  
  30. Sub updateSpecificLinks()
  31.  
  32. ThisWorkbook.UpdateLink (targetLinks) 'https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.updatelink
  33.  
  34. End Sub
RAW Paste Data