How to import multiple filenames from a folder into Excel?

[jbrphoto]

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.

 

[lrobbo314]

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.

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]

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]

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]

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]

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]

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]

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]

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]

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!