Linking cells to files in a folder
 

Hello,
I'd like to link cells in a column to files in a folder. The cell value is
the file name within the folder.

Is there an easy way to do this without linking one cell at a time?

Thank you.

Try to cut and paste this macro code to a new excel file and run.

Sub AutoHyperlinkFileNametoExcel()
Dim fnam As Variant

' fnam is an array of files returned from GetOpenFileName
' note that fnam is of type boolean if no array is returned.
' That is, if the user clicks
' on cancel in the file open dialog box, fnam is set to FALSE

Dim b As Integer 'counter for filname array
Dim b1 As Integer 'counter for finding \ in filename
Dim Cell As Range
Dim x As Integer
' first open a blank sheet and go to top left ActiveWorkbook.Worksheets.Add

fnam = Application.GetOpenFilename("all files (*.*), *.*", 1, _
"Select Files to Fill Range", "Get Data", True)

If TypeName(fnam) = "Boolean" And Not (IsArray(fnam)) Then Exit
Sub 'if user hits cancel, then end

For b = 1 To UBound(fnam)

' print out the filename (with path) into first column of new
sheet
ActiveSheet.Cells(b, 1) = fnam(b)
b1 = Len(fnam(b))

' add any other columns to s/s here
' this code makes the drawing name from the filename
' by stripping off the directory name and the extension

Do While Mid(fnam(b), b1, 1) < "\"
b1 = b1 - 1
Loop
Next
'resize the columns

ActiveSheet.Range("a:zz").Columns.AutoFit
x = ActiveSheet.UsedRange.Rows.Count
Range("B1").Select
ActiveCell.FormulaR1C1 = "=HYPERLINK(RC[-1])"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B" & x), Type:=xlFillDefault
End Sub



"beginnerExceluser" wrote:

Hello,
I'd like to link cells in a column to files in a folder. The cell value is
the file name within the folder.

Is there an easy way to do this without linking one cell at a time?

Thank you.