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.
Display MoreCode- Option 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!!
-
Display MoreCode
- 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
-
Sorry about that.
Does it work now?
-
yes perfectly working!!
-
That's good. I didn't have time to test it.