Automatically Insert file names into a spreadsheet. Files are all in a single folder.

It would be a big time saver to figure out how to get file names entered into a spreadsheet.

I have a folder name KEEPER with an unknown number of text files. I would like to figure out an automated way to get all the file names in KEEPER folder entered into an open spreadsheet named COMPARE. The spreadsheet is in the KEEPER folder location. Be best if the file extension is removed and the file names are entered on tab B beginning at Cell A1. Note the file names are fixed length and one word.

Can this be simply done with minimal VBA code?

Thanks for any direction.

jj

Thanks FoxSeaLady-

Please note the file location:
C:\Desktop\Keeper

The file I where I need the list created is called compare and it is in the folder named keeper. Where I want the list created is on Tab 2 which is also the tab name. The list hopefully would populate column1 beginning at cell A1.

Not sure how to customize the code for my use.

Thanks for any further assistance.

John

Hi

Still getting to a solution. Question how many characters in the file name?

Cheers

Always 10 - thanks.

j

Hi

It has taken time but I think I have your Solution with the following VBA code:-

Please Login or Register  to view this content.

It may not be the tidiest but it works and is not a lot of code.

I have attached a sample file. Note this works from in directory Keeper and will need saving once run.
Cheers

This is fascinating - thanks. It list the txt file names but also the name of the Excel file itself - it put that in the same column. That's a small issue as there is a bunch of different file types and only want the text file names (minus the extension.) It is listing the extension.

Regards,
j

Hi
Can you please confirm the file extension required.

Cheers

Ideally only the file names of the .txt are listed. This folder has various other extensions like PDF which should not appear in the file name list.

Minor issue is it requests where the folder location is each run. The files are in folder C:\Desktop\Keeper - could this location be imbedded in the code and if I ever wanted to change it I could edit the path accordingly?


Thanks for your efforts FoxSeaLady! j

Hi

I don't see why not I will try. Still struggling to solve .txt only files
Trying not to give up
Cheers

Thanks for hanging in there!

Nearly there

Hi

Struggling to make this file default to one directory are not ask each time otherwise seems to meet all your criteria hen run from the default directory Keeper.
I have essentially modified code from another project which has caused some issues.

Hope this works despite the small inconvenience of nominating a directory each time.

Below is the code

Please Login or Register  to view this content.

and attached file
Cheers