I need some ideas here... I have a column which has file names. I have a folder which has all the excel files. Is it possible to link both these and get the file path in adjacent cell ?
get path of file with reference cell value - Excel VBA
- RAAGA
- Thread is marked as Resolved.
-
-
-
Are the files in different folders?
-
Hi Roy!!
Please review below for better understanding.
for example: I have a value "AAA", "BBB",and so on in column A1,A2 similarly I have a set of excel files in a folder with name AAA.xlsx, BBB.xlsx.
Ill choose folder path to macro manually everytime.
Finally, by comparing the cell value in column A and file name in the folder, the file path should be displayed in column B for matched items.
-
You need to create a reference to the Scripting Runtime Library
- In the VB Editor, click on Tools.Tools in Excel VB Editor Toolbar
- Click on References.References Option in Excel VB Editor Toolbar to Enable VBA FileSystemObject FSO
- In the References dialog box that opens, scroll through the available references and check the ‘Microsoft Scripting Runtime’ option.
Then add this code to a standard module. It assumes the sheet with the list is active.
Code
Display MoreOption Explicit Sub FolderDetails() Dim FSO As New FileSystemObject Set FSO = CreateObject("Scripting.FileSystemObject") Dim rRng As Range, rCl As Range Dim sFolder As String ''// Open the select folder prompt With Application.FileDialog(msoFileDialogFolderPicker) If .Show = -1 Then ' if OK is pressed sFolder = .SelectedItems(1) End If End With Set rRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) For Each rCl In rRng If FSO.FileExists(sFolder & Application.PathSeparator & rCl.Value & "xlsx") Then rCl.Offset(, 1).Value = sFolder Else: rCl.Offset(, 1).Value = "The File Does Not Exist" End If Next rCl End Sub -
hi roy, script runs well. but I get "The File Does Not Exist" even though Value and file name are same
-
-
hi Roy, please ignore. I debugged it myself. there was a "." missing before "xlsx".. thanks mate... your are awesome!!
-
Code
Display MoreSub FolderDetails() Dim FSO As New FileSystemObject Set FSO = CreateObject("Scripting.FileSystemObject") Dim rRng As Range, rCl As Range Dim sFolder As String ''// Open the select folder prompt With Application.FileDialog(msoFileDialogFolderPicker) If .Show = -1 Then ' if OK is pressed sFolder = .SelectedItems(1) End If End With Set rRng = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) For Each rCl In rRng If FSO.FileExists(sFolder & Application.PathSeparator & rCl.Value & ".xlsx") Then rCl.Offset(, 1).Value = sFolder Else: rCl.Offset(, 1).Value = "The File Does Not Exist" End If Next rCl End Sub -
Sorry about that.
Does it work now?
-
yes perfectly working!!
-
That's good. I didn't have time to test it.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!