Inserting file names.

caminochaos

New Member
Joined
Jan 4, 2006
Messages
15
I am making a spreadsheet of all the files in a paticular folder. Is there a way just to copy the file name and paste it into an excel spreadsheet?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
=CELL("filename",A1) will display the file name. Is that what you mean?

edit: Nevermind. By mbb's post below, I see that I misinterpreted what you were trying to do.
 
Upvote 0
Adjust folder and sheetname accordingly:

Sub test()

Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("C:\")
Set Files = folder.Files

ctr = 1

For Each file In Files
ThisWorkbook.Sheets("Sheet1").Cells(ctr, 1) = file.Name
ctr = ctr + 1
Next

End Sub
 
Upvote 0
mbb4331,

I am a semi-begginer to this so I'm not really sure what all that means. Could you explain to how to do that. If it's not to much trouble.

Thanks
 
Upvote 0
To use the posted code; (by Eric Van Geit)
Start the Visual Basic Editor (via Menu Tools, Macro or press ALT+F11).
On the Insert menu in the VBE, click Module. (if necessary)
In the module (the white area at the right), paste the your code

How to create a button and assign a macro to it:

If you don't already have the "Forms" toolbar active, click on Tools>Customize>Toolbars and check "Forms". The toolbar will appear on your screen; drag it down to the bottom of your screen to anchor it.

Click on the Button icon, and drag out a button on the screen. When you release the left mouse button, an "Assign Macro" dialogue box will appear. Highlight whatever macro you want, and click OK. The macro will now be assigned to that button.

You can also run the code by hitting Alt-F8 and selecting the macro name.
 
Upvote 0
Change:
Code:
ThisWorkbook.Sheets("Sheet1").Cells(ctr, 1) = file.Name
to:
Code:
ThisWorkbook.Sheets("Sheet1").Cells(ctr, 1) = file.DateLastModified
 
Upvote 0
Change:
Code:
ThisWorkbook.Sheets("Sheet1").Cells(ctr, 1) = file.Name
to:
Code:
ThisWorkbook.Sheets("Sheet1").Cells(ctr, 1) = file.Name
ThisWorkbook.Sheets("Sheet1").Cells(ctr, 2) = file.DateLastModified
This will put the name in column A with the date in column B.
 
Upvote 0
Yes is good however it only gives me the file names I would like the file path as well. There are multiple file folders containing multiple files
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top