get path of file with reference cell value - Excel VBA

  • 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 ?

  • 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

    1. In the VB Editor, click on Tools.Tools in Excel VB Editor Toolbar
    2. Click on References.References Option in Excel VB Editor Toolbar to Enable VBA FileSystemObject FSO
    3. 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.

  • hi Roy, please ignore. I debugged it myself. there was a "." missing before "xlsx".. thanks mate... your are awesome!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!