Downloading multiple files from excel hyperlinks

Are the workbooks behind login walls that require authentication?

download.bat

curl -O http://www.example.com/index.xlsx

https://curl.se/docs/tutorial.html

Get a webpage and store in a local file, make the local file get the name of the remote document (if no filename part is specified in the URL, this fails):

curl -O http://www.example.com/index.html

How about this if VBA considered:

Sub DownloadFiles()
    Dim ws As Worksheet
    Dim cell As Range
    Dim url As String
    Dim http As Object
    Dim fileName As String
    Dim folderPath As String
    
    ' Set your folder path here
    folderPath = "C:\Path\"
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to your sheet name
    
    For Each cell In ws.Range("A1:A100") ' Change the range to your hyperlink range
        If cell.Hyperlinks.Count > 0 Then
            url = cell.Hyperlinks(1).Address
            Set http = CreateObject("MSXML2.XMLHTTP")
            http.Open "GET", url, False
            http.send
            
            fileName = folderPath & Mid(url, InStrRev(url, "/") + 1)
            If http.Status = 200 Then
                With CreateObject("ADODB.Stream")
                    .Type = 1 ' adTypeBinary
                    .Open
                    .Write http.responseBody
                    .SaveToFile fileName, 2 ' adSaveCreateOverWrite
                    .Close
                End With
            End If
        End If
    Next cell
End Sub

What kind of files would be downloaded?

Kindly mark the solution as Resolved if you find the below information useful :)

Unfortunately, Excel does not have a built-in feature or formula to directly download files from hyperlinks. However, you can automate this task using PowerShell Script or VBA (Visual Basic for Applications) in Excel.

Here is a PowerShell way of getting this done.

Disclaimer: Please test this script in a test environment first and use it at your own risk. 

1. Prepare Your Excel File

• Ensure the hyperlinks are in a single column (e.g., Column A).
• Save the Excel file as .xlsx.

2. PowerShell Script

# Load the Excel file
$excelFile = "C:\Path\To\Your\File.xlsx"  # Change to your file path
$sheetName = "Sheet1"                    # Change to your sheet name
$saveFolder = "C:\Path\To\Save\Files"    # Change to your desired download folder

# Create the save folder if it doesn't exist
if (!(Test-Path -Path $saveFolder)) {
    New-Item -ItemType Directory -Path $saveFolder
}

# Load the Excel application
$excel = New-Object -ComObject Excel.Application
$workbook = $excel.Workbooks.Open($excelFile)
$sheet = $workbook.Sheets.Item($sheetName)

# Get the used range of the sheet
$usedRange = $sheet.UsedRange
$rowCount = $usedRange.Rows.Count

# Iterate through each row
for ($row = 1; $row -le $rowCount; $row++) {
    $cell = $sheet.Cells.Item($row, 1)  # Assuming hyperlinks are in Column A
    $hyperlink = $cell.Hyperlinks.Item(1).Address

    if ($hyperlink) {
        try {
            # Extract file name from URL
            $fileName = Split-Path -Leaf $hyperlink
            $filePath = Join-Path -Path $saveFolder -ChildPath $fileName

            # Download the file
            Invoke-WebRequest -Uri $hyperlink -OutFile $filePath
            Write-Host "Downloaded: $fileName"
        } catch {
            Write-Host "Failed to download: $hyperlink" -ForegroundColor Red
        }
    }
}

# Clean up
$workbook.Close($false)
$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)

Write-Host "Download completed!" -ForegroundColor Green

3. How It Works

• Excel Interaction: Uses PowerShell’s COM object to read Excel files.
• URL Extraction: Reads the hyperlinks from the specified column.
• File Download: Uses Invoke-WebRequest to download each file.
• File Naming: Automatically names the file based on the URL.

4. How to Run the Script

1. Save the script as a .ps1 file (e.g., DownloadFiles.ps1).
2. Open PowerShell as Administrator.
3. Run the script:

.\DownloadFiles.ps1

5. Additional Notes

• Ensure PowerShell execution policies allow the script to run:

  Set-ExecutionPolicy -Scope Process -ExecutionPolicy Bypass

• For hyperlinks requiring authentication, use Invoke-WebRequest with credentials:

  Invoke-WebRequest -Uri $hyperlink -OutFile $filePath -Credential (Get-Credential)

• Make sure you have the Excel application installed for COM object interaction.