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?
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
=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.
 

mbb4331

Board Regular
Joined
Dec 22, 2005
Messages
152
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
 

caminochaos

New Member
Joined
Jan 4, 2006
Messages
15
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
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

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.
 

caminochaos

New Member
Joined
Jan 4, 2006
Messages
15
Thank you all.

If I want to insert the "Modified Date" with the file name what do I edit in the module?
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985

ADVERTISEMENT

Change:
Code:
ThisWorkbook.Sheets("Sheet1").Cells(ctr, 1) = file.Name
to:
Code:
ThisWorkbook.Sheets("Sheet1").Cells(ctr, 1) = file.DateLastModified
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

rudevincy

Active Member
Joined
Feb 21, 2005
Messages
415
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,885
Messages
5,543,028
Members
410,583
Latest member
gazz57
Top