Results 1 to 10 of 10

How to import multiple filenames from a folder into Excel?

This is a discussion on How to import multiple filenames from a folder into Excel? within the Excel Questions forums, part of the Question Forums category; I'm new to the forum, so please bare with me. I have a folder full of about 1000 .jpg files ...

  1. #1
    New Member
    Join Date
    Sep 2009
    Posts
    4

    Default How to import multiple filenames from a folder into Excel?

    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.

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    894

    Default Re: How to import multiple filenames from a folder into Excel?

    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

  3. #3
    GTO
    GTO is offline
    Board Regular
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    4,844

    Default Re: How to import multiple filenames from a folder into Excel?

    Greetings,

    Here would be one way:

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


    Hope that helps,

    Mark

  4. #4
    New Member
    Join Date
    Sep 2009
    Posts
    4

    Default Re: How to import multiple filenames from a folder into Excel?

    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

  5. #5
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    894

    Default Re: How to import multiple filenames from a folder into Excel?

    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

  6. #6
    New Member
    Join Date
    Sep 2009
    Posts
    4

    Default Re: How to import multiple filenames from a folder into Excel?

    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.

  7. #7
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    894

    Default Re: How to import multiple filenames from a folder into Excel?

    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.

  8. #8
    New Member
    Join Date
    Sep 2009
    Posts
    4

    Default Re: How to import multiple filenames from a folder into Excel?

    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.

  9. #9
    GTO
    GTO is offline
    Board Regular
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    4,844

    Default Re: How to import multiple filenames from a folder into Excel?

    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:

    Const strPath As String = "G:\My Images\Lee's Family Albums\All Lee's Albums2\"

    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:

        ThisWorkbook.Worksheets("MySheet").Resize(UBound(aryJPGList)).Value = Application.Transpose(aryJPGList)

    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

  10. #10
    New Member
    Join Date
    Apr 2010
    Posts
    1

    Default Re: How to import multiple filenames from a folder into Excel?

    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!

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com