+ Reply to Thread
Results 1 to 21 of 21

Userform to Control Macro Features. Macro = Search for File Type

  1. #1
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Question Userform to Control Macro Features. Macro = Search for File Type

    Hi all,

    I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.

    The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.

    I have created a basic userform outline, 'UF1' for the user to define:
    • Select File Extension
    • Select Folder to Search
    • Destination Sheet

    I just don't have any idea how to sync the two.

    If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.

    The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.

    'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.

    'CB1_Find_Files' should activate the macro to find any files for the specified criteria.

    There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.

    Hope someone can help with the above sync the above.

    Cheers
    Attached Files Attached Files
    Last edited by R_S_6; 01-26-2009 at 01:05 PM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform to Control Macro Features. Macro = Search for File Type

    You could use a combobox to select the file extensions.

    You cannot use a refedit to find a file, this control can be used to pick a range. Search the forum browse for folder

    You could use a refedit to select the destination
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Userform to Control Macro Features. Macro = Search for File Type

    Hi Roy,

    Not sure a combobox would be that wise as I would have a lot of file different extensions that I may search for - Unless however there is code that lists all the known file extensions on your computer?

    As for the 'refedit' I used that from the userform, UF1 control box as it looked like it had a button to bring up something like a windows explorer, and then once the directory was selected, display it in the text section of the 'refedit'. It shows that I have limited knowledge.

    As for the destination it would be great if the code could be written in such a way that there was a combobox to select the sheet in which to list the filenames, where the combobox would list all the sheets found in the workbook. Would this however not require a new sheet within the workbook to list the options of the sheet names?

    Cheers

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform to Control Macro Features. Macro = Search for File Type

    Try this.

    Select a file extension with first combo - you will need to extend the list
    Select a folder by entering the textbox
    Select the sheet

    Click the button
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Thumbs up Re: Userform to Control Macro Features. Macro = Search for File Type

    First and Foremost - That's Awesome! Thank You!

    A couple of things if I can:
    1. Can it return 'No files of type *xxx found'?
    2. I have found that you can actually modify the file extension manually in the text box and the search still works! (GREAT) Is there a way of getting the code to remember what I have asked for and remember it? E.g. if I typed .hm, then performed the search, it would save the extension '*.hm' in the macro! That would be really useful!
    3. It seems that the destination sheet always seems to be 'Sheet1' even if another sheet is selected? I.e. no matter what sheet you select the file results are listed in sheet 1.

    Once again 'A thousand thanks' - Really Appreciated!

    Cheers

  6. #6
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Userform to Control Macro Features. Macro = Search for File Type

    Appologies for the odd change in text size

    Cheers!

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform to Control Macro Features. Macro = Search for File Type

    Try this amended code, you can manually type into the first combo an extension, remember the .

    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Userform to Control Macro Features. Macro = Search for File Type

    Bingo!

    Working like a charm!

    Would it be hard to get it to say "# *.xxx files found and listed in 'sheetxxx' ", where '#' is the number of files found?

    Thanks Again

  9. #9
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform to Control Macro Features. Macro = Search for File Type

    Amended the code
    Please Login or Register  to view this content.

  10. #10
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Thumbs up Re: Userform to Control Macro Features. Macro = Search for File Type

    Wonderful! Thanks again Roy!

    Well chuffed!

    Cheers

  11. #11
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform to Control Macro Features. Macro = Search for File Type

    Just had time to add the function to add extension types to the list. In this code I use Sheet3 to store a list of extension types in Column A, you can hide the sheet. If you type a new one into the combobox you are offered the option to add it to the list
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Question Re: Userform to Control Macro Features. Macro = Search for File Type

    Just plugged that piece into my spreadsheet (your code ) and it falls over at line:

    Please Login or Register  to view this content.
    Saying 'Run-time error '91': Object variable or With block variable not set'...???

    With this new piece of code do I need to remove/modify:

    Please Login or Register  to view this content.
    ... and list the file types listed in the above code in 'Sheet3'?

    Cheers for the additional help Roy!

    Mark

  13. #13
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform to Control Macro Features. Macro = Search for File Type

    I forgot to attach an example
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Userform to Control Macro Features. Macro = Search for File Type

    Nearly there! Tried searching for '*.hm' (not previously listed in 'Sheet3'), Targeted search folder; set the sheet to 'Sheet1'; It asks me if I want to add the extension, I reply yes, it saves it; tells me it has found four files, then it hits:

    'Run-time error '1004': The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.'

    on line:

    Please Login or Register  to view this content.
    Any ideas?

    Cheers

  15. #15
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform to Control Macro Features. Macro = Search for File Type

    Are you using the example workbook that I uploaded?

  16. #16
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Userform to Control Macro Features. Macro = Search for File Type

    I am indeed! I think its something to do with reordering the extensions by ascending alphabetical order? no?

    Cheers

  17. #17
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform to Control Macro Features. Macro = Search for File Type

    It's working ok for me, I'll look into it for you.

  18. #18
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Re: Userform to Control Macro Features. Macro = Search for File Type

    Cheers Roy!

  19. #19
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform to Control Macro Features. Macro = Search for File Type

    This should fix it

    P
    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    07-28-2008
    Location
    Leamington Spa, UK
    MS-Off Ver
    2010
    Posts
    142

    Smile Re: Userform to Control Macro Features. Macro = Search for File Type

    Bingo! Thanks Roy - Fully up and running now without so much as a glitch!

    (Don't suppose you could shed any light / work your magic on this previous thread I started that no-one replied too? 'Record File Creations within a Folder to a Worksheet Whilst Spreadhseet Remains Open'. Hope that doesn't break forum link rules! Appologies if it does!)

    Thanks again! Really Appreciated!

    Mark

  21. #21
    Registered User
    Join Date
    12-13-2010
    Location
    Wales
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Userform to Control Macro Features. Macro = Search for File Type

    Roy,

    I don't suppose there is a way to amend this macro so that it will search through sub folders is there?

    Regards,

    Colin

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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