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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,016
Office Version
  1. 365
=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
 

Forum statistics

Threads
1,141,427
Messages
5,706,382
Members
421,446
Latest member
rena jhon

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
Top