How to import multiple filenames from a folder into Excel?

jbrphoto

New Member
Joined
Sep 30, 2009
Messages
4
I'm new to the forum, so please bare with me. I have a folder full of about 1000 .jpg files that I need to copy and paste each filename (which are very long, because they contain all image keywording) into an Excel spreadsheet. Is there a way to batch the process. I've tried a few obvious ways, only to get a worksheet full of hieroglyphics. The formating would be as such:

image1.jpg in cell D3
image2.jpg in cell D4
image3.jpg in cell D5 and so on a thousand times.

Thank you for any help you give.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,196
Office Version
  1. 365
Platform
  1. Windows
This will give you a list with hyperlinks that will open the file you choose. I can give you the code without the hyperliks as well if you want.

Code:
Sub Hyperlink_List()
Dim par, sfil As String
Dim r As Range
par = Application.InputBox("Enter Directory")
sfil = Dir(par & "*.*", vbDirectory)
Set r = ActiveCell
Do Until sfil = ""
If sfil = "." Or sfil = ".." Then GoTo skipit
r = sfil
ActiveCell.Hyperlinks.Add r, par & sfil
Set r = r.Offset(1)
skipit:
sfil = Dir$
Loop
End Sub
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Greetings,

Here would be one way:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br>    <br><SPAN style="color:#00007F">Sub</SPAN> JPGList()<br><SPAN style="color:#00007F">Dim</SPAN> FSO <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>   <SPAN style="color:#007F00">'<---FileSystemObject</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> FOL <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>   <SPAN style="color:#007F00">'<---Folder</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> FIL <SPAN style="color:#00007F">As</SPAN> Object   <SPAN style="color:#007F00">'<---File</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> aryJPGList<br>    <br>                        <SPAN style="color:#007F00">'// Change to suit//</SPAN><br><SPAN style="color:#00007F">Const</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "C:\Documents and Settings\stumpm\My Documents\My Pictures\"<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> FSO = CreateObject("Scripting.FileSystemObject")<br>    <SPAN style="color:#00007F">Set</SPAN> FOL = FSO.GetFolder(strPath)<br>        <br>    <SPAN style="color:#00007F">ReDim</SPAN> aryJPGList(0 <SPAN style="color:#00007F">To</SPAN> 0)<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> FIL <SPAN style="color:#00007F">In</SPAN> FOL.Files<br>        <SPAN style="color:#00007F">If</SPAN> FIL.Type = "JPEG Image" <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> aryJPGList(1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(aryJPGList) + 1)<br>            aryJPGList(UBound(aryJPGList)) = FIL.Name<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    <SPAN style="color:#007F00">'// Pick a place to plant the list//</SPAN><br>    Sheet1.Range("A2").Resize(UBound(aryJPGList)).Value = Application.Transpose(aryJPGList)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Hope that helps,

Mark
 

jbrphoto

New Member
Joined
Sep 30, 2009
Messages
4
Wow, thanks for the fast responses, guys. Unfortunately, I have no idea of what to do with the code you've provided. Can you tell me where to put it?

Thanks again
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,196
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Open the workbook you want to use
Hit ALT + F11, this will open vba editor
Go to insert, click module, then paste the code

After that you will hit ALT + F8, at which point you will be able to run the macro.

Hope that helps
 

jbrphoto

New Member
Joined
Sep 30, 2009
Messages
4
I've followed lrobbo's instructions, but I get an "Input" dialog box with the path of the folder of jpeg files and an input field below. If I put in a cell or a range of cells and click OK nothing happens. Also, Alt F8 didn't do anything so I just ran it from the tools-macro menu. Am I imnputing the script incorrectly?

Here's the path of the folder: G:\My Images\Lee's Family Albums\All Lee's Albums2

I also tried GTO's script but get a run time 76 error-path not found...

What am I doing wrong? I apologize for my lack of understanding, but I've never used scripts before.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,196
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

When the input box comes up, that's where you should enter the directory where the files are, not the range where you want them to be listed. Make sure that when you insert the module that you are on the current workbook. You should see a left panel that says the name of the open workbook, select a page then hit insert. That las part isn't compulsory though.
 

jbrphoto

New Member
Joined
Sep 30, 2009
Messages
4
Do I copy and paste your code as is or do I customize it? I get a hyperlink, but when I click it, "Cannot open the specified file". Is there a way to have it fill in the cells with the filenames automatically? I really appreciate the time your taking to help me.
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Greetings JB,

When you get an error, try stepping thru the code to see where it goes KABOOM!. You can do this by clicking someplace in the Sub, and start pressing F8. You will see it highlight a line at a time; the highlighted line being the next statement to process.

Anyways, path not found would indicate that we didn't find the path to your folder. Change the Const to:

<font face=Courier New><SPAN style="color:#00007F">Const</SPAN> strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "G:\My Images\Lee's Family Albums\All Lee's Albums2\"</FONT>

Also - please note that I simply used the sheet's codename (Sheet1) when specifying the destination. If you have changed the tab name of the sheet, look in the project explorer window (usually upper-left) in VBIDE (the code window) and (let's say your sheet's tab name was "MySheet") you will see the sheet listed similar to:

Sheet1(MySheet)

In this case, Sheet1 is the codename and MySheet is the worksheet's name (or tab name).

So, you might need to either update it to the correct sheet's codename, or you can use the sheet's tab name like:

<font face=Courier New>    ThisWorkbook.Worksheets("MySheet").Resize(UBound(aryJPGList)).Value = Application.Transpose(aryJPGList)</FONT>

I hope that makes sense; and of course you can alter a bit to include an input box to replace the Const.

@irobbo314:

Hi there,

Say, I don't use Dir very often, but I noted that if I used:

sfil = Dir(par & "*.*", vbDirectory)

...sfile would only return ".".

If I changed to: sfil = Dir(par & "*.*", vbNormal)

...then all the files in the folder would return.

If I changed to looking for just jpg's ("*.jpeg"), then either vbNormal or vbDirectory seems to work.

Does that make sense or am I missing something?

Mark
 

rmursewick

New Member
Joined
Apr 29, 2010
Messages
1
JB,
this macro is excellent. I would like to do a slight twist to it. How can one take each image and place it in an individual sheet and make the name of the sheet the filename. So if we have 20 photos names photo1.jpg to photo20.jpg, we end up with 20 tabs and the name of the tab is photo1 etc.

Thank you!
 

Forum statistics

Threads
1,136,328
Messages
5,675,132
Members
419,551
Latest member
thangxpm

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