Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linking whole rows of cells | Excel Worksheet Functions | |||
Export html page to Excel - support files folder | Excel Discussion (Misc queries) | |||
Linking Formats Across Cells | Excel Discussion (Misc queries) | |||
Macro to copy range from Excel files in folder | Excel Discussion (Misc queries) | |||
Copy files from spreadsheet into folder | Excel Discussion (Misc queries) |