Jan 27
Link Multiple Files in Excel
The purpose of this article is to solve a problem with linking multiple files into a spreadsheet for data and financial analysis.
Imagine reviewing a large dataset with multiple contracts, invoices, purchase orders and you need to go through those documents to audit the work done on a specific project. In my case, creating hyperlinks inside my worksheet is an easy way to match the data with the document involved. But what if you come across hundreds of thousands of lines and documents to review?
The VBA code below (Add hyperlinks) is the solution for creating automatic hyperlinks in bulk for all your documents listed in a worksheet:
Sub AddHyperlinks()
Dim Cell As Range
Dim FSO As New FileSystemObject
Dim filePath As String
Dim fileName As String
Dim actualFile As String
Dim Length As Integer
Dim myFile As File
Dim myFolder As FolderfilePath = ThisWorkbook.Path & Application.PathSeparator
For Each Cell In selection
Set myFolder = FSO.GetFolder(filePath)
For Each myFile In myFolder.Files
Length = Len(Cell.Value)
actualFile = Left(myFile.Name, Length)
If actualFile = Cell.Value Then
ActiveSheet.Hyperlinks.Add Anchor:=Cell, Address:=myFile
End If
Next myFile
Next Cell
End Sub
How to use this code:
1. Copy and paste this code into Developer>Visual Basic>New Module
2. Save this VBA code in a spreadsheet as an “Excel Macro-Enabled Workbook(.xlsm).”
3. On your dataset, select each cell you need to create hyperlinks. Each cell name should be the same name as the filename. I.e., “Invoice 1234”, your filename should be “Invoice 1234”, with the same characters and space.
4. Documents linked to the spreadsheet must be saved in the same folder as the worksheet you are working on.
5. When using a separate excel file for all your macros and VBA, make sure you have this excel file in the same folder as the original file you need the VBA code to run.
6. When ready go to Developer>Macros>Choose AddHyperlinks>Run
In the example below, I’m working on analyzing a Crypto dataset. My code is saved in a separate spreadsheet called Macros. I open both, select all cells that needs the links, and run the code. In a matter of seconds all my documents are linked into each cell and now I can review each document.
I hope this helps you and your team save hours of work and simplify the analysis process.
