+ Reply to Thread
Results 1 to 22 of 22

VBA Macro to search for a file in a folder

Hybrid View

  1. #1
    Registered User
    Join Date
    05-29-2025
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 MSO Version 2402
    Posts
    12

    VBA Macro to search for a file in a folder

    Hello.

    I am quite new here in this forum and I hope we can help each other.

    I recently have a hard time in making a code focusing on searching a file in a folder and if possible to open it.

    Please help me on the codings.

    Thanks guys.

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,097

    Re: VBA Macro to search for a file in a folder

    See next code
    Adjust
    .Filters.Add "Classeurs Excel", "*.xlsx"
    to your file extension


    Option Explicit
    
    Sub Search_File_Picker2()
    Dim WkFile As Workbook
    Dim WS As Worksheet
        With Application.FileDialog(msoFileDialogFilePicker)
                .Title = "Choisir le fichier Excel"
                .AllowMultiSelect = False
                .InitialFileName = "Fichier_Par_Defaut.xlsx"
                .Filters.Add "Classeurs Excel", "*.xlsx"
                .FilterIndex = 1
                .InitialFileName = ActiveWorkbook.Path '  Select present folder
            .Show
            If .SelectedItems.Count > 0 Then
                Set WkFile = Workbooks.Open(.SelectedItems(1))
                Set WS = WkFile.Sheets(1)
            Else
                MsgBox "No file selected", , "INFORMATION"
                Exit Sub
            End If
        End With
    End Sub
    - Battle without fear gives no glory - Just try

  3. #3
    Registered User
    Join Date
    05-29-2025
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 MSO Version 2402
    Posts
    12

    Re: VBA Macro to search for a file in a folder

    what if I will add a search in which if something is type there, that words will be search as folder file names

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2507 (Windows 11 Home 24H2 64-bit)
    Posts
    91,828

    Re: VBA Macro to search for a file in a folder

    Administrative Note:

    Welcome to the forum.

    Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you need this to work for?

    Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

    The four most recent Excel products are Excel 2019, Excel 2021, Excel 2024 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

    Thanks.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    05-29-2025
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 MSO Version 2402
    Posts
    12

    Re: VBA Macro to search for a file in a folder

    Thank you. I have already update my infos

  6. #6
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,518

    Re: VBA Macro to search for a file in a folder

    Maybe this will get you started.
    Change directory path in A1. Press button and type keyword in TextBox. Enter or Tab.
    Attached Files Attached Files
    Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
    You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.

  7. #7
    Registered User
    Join Date
    05-29-2025
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 MSO Version 2402
    Posts
    12

    Re: VBA Macro to search for a file in a folder

    The code works but when I try to open the file a run time error occured.

  8. #8
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,518

    Re: VBA Macro to search for a file in a folder

    Just click on a name in the ListBox to open the file because I'm not getting any errors.

  9. #9
    Registered User
    Join Date
    05-29-2025
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 MSO Version 2402
    Posts
    12

    Re: VBA Macro to search for a file in a folder

    FORUM.png
    Please see picture for the error. I'm not well familiar onto what i need todo next

  10. #10
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,097

    Re: VBA Macro to search for a file in a folder

    Which code you were using ?

  11. #11
    Registered User
    Join Date
    05-29-2025
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 MSO Version 2402
    Posts
    12

    Re: VBA Macro to search for a file in a folder

    HI PCI,

    I am currently using Bakermans code.

    I try to use your code, but I have a question regarding what if I will add a search in which if something is type there, that words will be search as folder file names

  12. #12
    Registered User
    Join Date
    05-29-2025
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 MSO Version 2402
    Posts
    12

    Re: VBA Macro to search for a file in a folder

    Please help guys, I'm not familiar on this error and on what to do

  13. #13
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,518

    Re: VBA Macro to search for a file in a folder

    Did you try it on a different directory ?
    Try this also.
    Private Sub ListBox1_Click()
        Dim mybook As Workbook
        With ListBox1
            If .ListIndex > -1 Then
                Set mybook = Workbooks.Open(myDir & .List(.ListIndex, 0))
            End If
        End With
    End Sub

  14. #14
    Registered User
    Join Date
    05-29-2025
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 MSO Version 2402
    Posts
    12

    Re: VBA Macro to search for a file in a folder

    Same error as the first error i have screenshot

  15. #15
    Valued Forum Contributor
    Join Date
    05-02-2013
    Location
    Poland
    MS-Off Ver
    Excel 2013
    Posts
    358

    Re: VBA Macro to search for a file in a folder

    Show image of folder myDir (Sheet1.Range("A1").Value). This is C:\Users\cfederiz\OneDrive - DPDHL\Desktop\?

    Show image of ListBox1 with file names.

  16. #16
    Registered User
    Join Date
    05-29-2025
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 MSO Version 2402
    Posts
    12

    Re: VBA Macro to search for a file in a folder

    I am currently have this code
    Dim myDir As String
    
    Private Sub UserForm_Initialize()
        myDir = Sheet1.Range("A1").Value
    End Sub
    
    Private Sub TextBox1_AfterUpdate()
      textsearch = TextBox1.Text
      sn = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & myDir & "\*" & textsearch & "*.*"" /b").StdOut.ReadAll, vbCrLf)
      ListBox1.List = sn
    End Sub
    
    Private Sub ListBox1_Click()
        Dim mybook As Workbook
        With ListBox1
            If .ListIndex > -1 Then
                Set mybook = Workbooks.Open(myDir & .List(.ListIndex, 0))
            End If
        End With
    End Sub

  17. #17
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,518

    Re: VBA Macro to search for a file in a folder

    OK, but did you try on a different directory because on my part files open without a problem.
    Want to exclude the fact that it might have something to do with the filepath.

  18. #18
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,518

    Re: VBA Macro to search for a file in a folder

    We know which code you use.
    Please answer the questions asked to you.
    Have you tried another directory ? Show screenshots of cell A1 with filepath. Show screenshot of ListBox1 with filenames.
    We ask all of these questions because you seem to be the only one who has problems with getting the code to work properly.

  19. #19
    Registered User
    Join Date
    05-29-2025
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 MSO Version 2402
    Posts
    12

    Re: VBA Macro to search for a file in a folder


  20. #20
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,518

    Re: VBA Macro to search for a file in a folder

    Invalid attachment posted.
    Also this is the 3rd time I ask you if you have tried another directory. Please do and reply.

  21. #21
    Registered User
    Join Date
    05-29-2025
    Location
    Singapore
    MS-Off Ver
    Microsoft 365 MSO Version 2402
    Posts
    12

    Re: VBA Macro to search for a file in a folder

    Oh I know where my mistake is. just like on all what you are pointing it is on my directory.
    I overlooked the Backslash, my mistake. Thank you for all your help.

  22. #22
    Forum Guru bakerman2's Avatar
    Join Date
    10-03-2012
    Location
    Antwerp, Belgium
    MS-Off Ver
    MSO Home and Business 2024
    Posts
    7,518

    Re: VBA Macro to search for a file in a folder

    You're welcome and thanks for rep+.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 07-18-2017, 04:51 PM
  2. Search Folder + Search Excel File and Sum
    By Vanda_a in forum Excel General
    Replies: 1
    Last Post: 03-06-2015, 12:00 PM
  3. search for a file in a folder based on data, copy and paste it in another folder
    By kboy1289 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-05-2013, 11:47 AM
  4. macro script to search and hyperlink image file from folder
    By shamiul in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-15-2013, 12:09 PM
  5. Macro: Search for a file name in a specified folder and link to excel cell
    By jr13 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-08-2013, 03:57 PM
  6. Macro to search folder including subfolders for file and open
    By kiraexiled in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-01-2012, 02:45 PM
  7. Replies: 2
    Last Post: 03-26-2012, 07:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1