none
COMPARE VALUE OF TEXTBOX TO DIRECTORY CONTENTS RRS feed

  • Question

  • Using Excel 2016

    Const strFolder = "C:\Users\JAY\Documents\DOUG\ESTIMATE MASTER SHEETS\DATA\"

    Dim FNAME As String
    FNAME = textbox2.text & " 2016.xlsx"

    How can I check the contents of the above directory to see if FNAME is already used?

    thanks for any help

    Thursday, February 25, 2016 9:23 PM

Answers

  • You can use the Dir function, which returns the filename from a specified path and filename.  Note, however, it returns a null string ("") when the path and filename isn't found.  Then you can use the LEN function to test whether the filename or null string is returned.  So, for example...

    If Len(Dir(strFolder & FNAME, vbNormal)) > 0 Then
        MsgBox "File already exists.", vbInformation
    Else
        MsgBox "File does not already exist.", vbInformation
    End If

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"


    • Edited by Domenic Tamburino Thursday, February 25, 2016 10:15 PM To provide an explanation.
    • Marked as answer by 6da4 Thursday, February 25, 2016 10:18 PM
    Thursday, February 25, 2016 10:09 PM

All replies

  • You can use the Dir function, which returns the filename from a specified path and filename.  Note, however, it returns a null string ("") when the path and filename isn't found.  Then you can use the LEN function to test whether the filename or null string is returned.  So, for example...

    If Len(Dir(strFolder & FNAME, vbNormal)) > 0 Then
        MsgBox "File already exists.", vbInformation
    Else
        MsgBox "File does not already exist.", vbInformation
    End If

    Hope this helps!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"


    • Edited by Domenic Tamburino Thursday, February 25, 2016 10:15 PM To provide an explanation.
    • Marked as answer by 6da4 Thursday, February 25, 2016 10:18 PM
    Thursday, February 25, 2016 10:09 PM
  • Thanks,

    works perfectly

    Thursday, February 25, 2016 10:18 PM
  • You're very welcome!

    Cheers!


    Domenic Tamburino Microsoft MVP - Excel xl-central.com - "For Your Microsoft Excel Solutions"

    Thursday, February 25, 2016 10:21 PM