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

[R_S_6]

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

[royUK]

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

[R_S_6]

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

[royUK]

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

[R_S_6]

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

[R_S_6]

Appologies for the odd change in text size

Cheers!

[royUK]

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

Please Login or Register  to view this content.

[R_S_6]

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

[royUK]

Amended the code

Please Login or Register  to view this content.

[R_S_6]

Wonderful! Thanks again Roy!

Well chuffed!

Cheers

[royUK]

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.

[R_S_6]

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

[royUK]

I forgot to attach an example

[R_S_6]

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

[royUK]

Are you using the example workbook that I uploaded?

[R_S_6]

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

Cheers

[royUK]

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

[R_S_6]

Cheers Roy!

[royUK]

This should fix it

P

Please Login or Register  to view this content.

[R_S_6]

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

[colin_reith]

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