Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink Multiple Files
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink Multiple Files
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).Activat e 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" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink Multiple Files
Thank you so much for the help!
"JLatham" wrote: 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).Activat e 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" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hyperlink to files | Excel Discussion (Misc queries) | |||
hyperlink to files? | Excel Discussion (Misc queries) | |||
hyperlink to files | Excel Worksheet Functions | |||
How can I view files chronologically when opening multiple files | Excel Discussion (Misc queries) | |||
hyperlink to jpg files | Excel Discussion (Misc queries) |