Extract Hyperlink Address
In this video, I show how to extract Hyperlink in excel from folder using Macros
VBA Code Below
Sub HyperlinkFileListing()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim i As Integer
Dim filepath As String
'Create an instance of the FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
filepath = InputBox("Enter Link")
objStartFolder = filepath
'Get the folder object
Set objFolder = objFSO.GetFolder(objStartFolder)
i = 1
'loops through each file in the directory and prints their names and path
For Each objFile In objFolder.Files
'select cell
Range(Cells(i + 1, 1), Cells(i + 1, 1)).Select
'create hyperlink in selected cell
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
objFile.Path, _
TextToDisplay:=objFile.Name
i = i + 1
Next objFile
End Sub
