Download files to folder from the hyperlink in excel

[sushilkadam]

Hi Experts,

I need your urgent help please.
I am having a list of documents in excel sheet which contains hyperlink to the particular file in the folder (in office server)

There are thousands of drawings, I need as VBA which will download the selected files only in the particular folder (say on Desktop)

I am attaching the sample excel sheet for your reference.
Hyperlinks are in Column B4 to B1512. So I need VBA to download only those files which will be selected in the excel sheet

Basically, I want to download selected hyperlinked files in bulk

Request your urgent help please

Thanks

 

[John_w]

Try this - assuming FileCopy is able to copy files from your server. If it doesn't work then post examples of the hyperlink target addresses.

Change the destinationFolder path in the code to the required folder on your Desktop.

Public Sub Copy_Hyperlink_Target_Files()
    
    Dim cell As Range
    Dim fileName As String, p As Long
    Dim destinationFolder As String
    
    destinationFolder = "C:\path\to\Desktop\folder"
    
    If Right(destinationFolder, 1) <> "\" Then destinationFolder = destinationFolder & "\"
    
    For Each cell In Selection
        If cell.Hyperlinks.Count > 0 Then
            With cell.Hyperlinks(1)
                p = InStrRev(.Address, "\")
                fileName = Mid(.Address, p + 1)
                FileCopy .Address, destinationFolder & fileName
            End With
        End If
    Next
    
End Sub

 

[sushilkadam]

Sir,
Its not working can you please send me the sample file

 

[John_w]

"not working" is too vague. In what way isn't it working? Any errors? If so what is the exact error message?

Remember, the code only looks at the selected cells with hyperlinks, as you requested, so you have to select those cells before running the macro.

There isn't a sample workbook. The best I can provide is this test procedure which creates hyperlinks in A2:A3 (edit the code to specify the exact folder path and file names of each target file), selects those 2 cells and runs the main procedure.

Public Sub Test()

    With ActiveSheet
        .Hyperlinks.Add Anchor:=.Range("A2"), Address:="C:\path\to\folder\file.jpg", TextToDisplay:="Hyperlink 1"       'Change drive, folder path and file name
        .Hyperlinks.Add Anchor:=.Range("A3"), Address:="C:\path\to\folder\another.pdf", TextToDisplay:="Hyperlink 2"   'Change drive, folder path and file name
        .Range("A2:A3").Select
    End With
    
    Copy_Hyperlink_Target_Files
    
End Sub

 

[sushilkadam]

please give me your email id so that I can send you my file

my email is sushil.k.kadam@gmail.com

 

[John_w]

No, I prefer to communicate via this thread.

 

[sushilkadam]

ok no problem

I am getting error File not found

 

[John_w]

Try debugging the code with F8. At the point where the "File not found" error occurs (probably the FileCopy line), what are the values of .Address and destinationFolder & fileName? Are the valid? Are they correct?

What happens if you use those strings with a COPY command in a Command Prompt window (DOS prompt) to copy the file manually? Like this: COPY "source" "destination" (the quotes are needed if the filespec contains spaces).