Downloading multiple files from excel hyperlinks

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.