The following assumes that all that is in the cells in column B is the
filename itself, not a path or anything, just filename like MyFile.xls or
That Word File.doc
You'll need to change a couple of the Const values in the code to match the
actual reality of your worksheet name and the actual path that you'll have
the other files stored in.
To use this code, open your workbook and right-click on the Excel icon just
to the left of the word "File" in the Excel menu and choose [View Code] from
the popup list. Then copy and paste this code into the module presented to
you - make changes as needed to it. Save the workbook. Close the workbook.
Open the workbook back up and see the created links - no extra cells needed,
the filenames become 'hot' hyperlinks. Hope this helps.
I chose to put it in the Workbook_Open() event processor so that the links
are ready for use when the workbook is opened, and they're always right for
the system that the CD is mounted in - it's not locked to "D" or "E" or "S"
drive.
Private Sub Workbook_Open()
'this constant should hold all of the path
'information from just after the drive ID down
'to the folder containing the file to link to
'we will get the drive ID from the Excel FullName property
'since it'll be on the same CD.
Const SheetToPutLinksOn = "Sheet1" ' change as required.
Const PathToLinkedFiles = "\folder\" ' change as required.
'if you put the files in root folder use this:
' Const PathToLinkedFiles = "\"
Const ColumnWithFileNames = "B"
Const firstFilenameRow = 2
'some working variables
Dim lastRow As Long
Dim rOffset As Long
Dim partialPath As String
Dim linkPath As String
'make sure we're on the proper sheet
ThisWorkbook.Worksheets(SheetToPutLinksOn).Activate
Application.ScreenUpdating = False
'build 'full' path to the files to link to
partialPath = Left(ThisWorkbook.FullName, 2) ' gets C: or D: or X: etc
'add that to the Const path above
partialPath = partialPath & PathToLinkedFiles
'find last filename entry in column B
lastRow = Range(ColumnWithFileNames & Rows.Count).End(xlUp).Row - _
firstFilenameRow
'move to select the first file name entry (at B2)
Range(ColumnWithFileNames & firstFilenameRow).Select
Application.ScreenUpdating = False
'rebuild proper links to the files
For rOffset = 0 To lastRow
If Not IsEmpty(ActiveCell.Offset(rOffset, 0)) Then
linkPath = partialPath & ActiveCell.Offset(rOffset, 0).Text
ActiveSheet.Hyperlinks.Add anchor:=ActiveCell.Offset(rOffset, 0), _
Address:=linkPath
End If
Next
' to prevent prompt to save changes
' since you can't save to CD
ThisWorkbook.Saved = True
Application.ScreenUpdating = True
End Sub
JEM said:
I have a spread sheet with 700 file names and I need to link all the file
names to the files, is there a way hyperlink all of them at once instead of
doing them one at a time? I need to put all the files which some are in sub
directories on a cd and have the hyperlinks open the files on the cd from the
spreadsheet that will be in the root of the disk.
The file names start in B:2 and I would like to either link to the files in
the B column or in a helper column such as C:2
Any help would be greatly appreciated
Thanks!
