Macro to Import Multiple Images into Excel

[mmal]

I want to import a group of images into excel as pictures. I got a macro offline but it imports the file names not the image itself.

So here is the macro I'm currently using:

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\user\Desktop\Sample 2"
    
    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//
    
    ActiveSheet.Range("B7").Resize(UBound(aryJPGList)).Value = Application.Transpose(aryJPGList)
End Sub

So the result I'm getting from this is

Image01.jpg
Image02.jpg
Image03.jpg
Image04.jpg
.....

So this is doing exactly what I want it to except I want the images imported rather than the file names. Any suggestions?

Notes:

All files have the same name, the only thing that changes is the number and the number is always to digit. So Image01.jpg NOT image1.jpg
All files are in the same folder.


Any help would be much appreciated. Let me know if further clarification is needed.

 

[Shadow123]

This should cover that i think... untested (found it in the search function)

Sub InsertAllPictures()

Dim strPath As String
Dim strFileName As String
Dim myPict As Picture

With ActiveCell.Range("A1:B19") ' change to suit

strPath = "C:\Users\KRISHNA\Desktop\" ' change folder to suit
strFileName = Dir(strPath & "*.jpg") ' change file type to suit

Do While Len(strFileName) > 0
Set myPict = ActiveSheet.Pictures.Insert(strPath & strFileName)

With myPict
myPict.Top = ActiveCell.Top
myPict.Width = ActiveCell.Width
myPict.Height = ActiveCell.Height
myPict.Left = ActiveCell.Left
myPict.Placement = xlMoveAndSize



End With

strFileName = Dir

Loop

End With

End Sub

 

[caitlinlilly]

Ok -- I've never posted and I registered specifically to respond to this! (Also, I hope it's correct to just reply? Even with a mostly-new request? That's what the posting FAQ seem to indicate...)

I'm a macro/VBA idiot and for whatever reason I just can't even begin to get the rules/structure down. It's ridiculous and shameful. For like the past 2 years I've been trying to complete a side project at work involving getting images into Excel. I'll research it > try things > fail > download something > try it > fail > give up > remember 6 months later that I REALLY want to do this > start all over again.

Basically, I have a folder with about 1,300 images in it. It's a shared icon library. Every time someone requests that a new icon be added, I need to make sure that it's not a duplicate. This is easier if I can just maintain an Excel spreadsheet -- it's easier to scan, file names are often unpredictable, I can add comments, blah blah. We have one that someone else put together, but it's crazy out of date and jumbled, and I want to make a new one.

ANYWAY - this macro actually worked for me in terms of getting images from a folder into Excel. (Which is awesome! Other ones I try just do nothing and I can't figure out why.) BUT it plops all of them into the active cell, each stacked on top of each other.

How can I modify this so that it doesn't stack them all on top of each other? For example, I have a folder with Image1.png, Image2.png, Image3.png. B1 is the active cell. When I run this, I want Image1.png inserted into B1, Image2.png inserted into B2, Image3.png in B3, and so on? I searched around and think maybe it has something to do with "ActiveCell.Offset(0, -1).Select"? But that's as far as I got. Which is pathetic.

(Also it would be nice if it generated the file name in the adjacent column, but that's not a big deal. I'm not such an idiot where I can't figure out how to export a folder directory to a workbook.)

Thanks bunches!

ETA - ugh, this is way wordier than I meant it to be. Sorry! I was just really excited about that macro!

 

[mjbeam]

So do you have to visually check that an image isn't already in your library? If so, you could write a script to do a file comparison instead. There is an old DOS command "FC" that checks two files to see if they are the same internally, even binary files. A batch or vbs script could be made that could check to see if an image is already in your library.

 

[shadow12345]

Ok -- I've never posted and I registered specifically to respond to this! (Also, I hope it's correct to just reply? Even with a mostly-new request? That's what the posting FAQ seem to indicate...)

I'm a macro/VBA idiot and for whatever reason I just can't even begin to get the rules/structure down. It's ridiculous and shameful. For like the past 2 years I've been trying to complete a side project at work involving getting images into Excel. I'll research it > try things > fail > download something > try it > fail > give up > remember 6 months later that I REALLY want to do this > start all over again.

Basically, I have a folder with about 1,300 images in it. It's a shared icon library. Every time someone requests that a new icon be added, I need to make sure that it's not a duplicate. This is easier if I can just maintain an Excel spreadsheet -- it's easier to scan, file names are often unpredictable, I can add comments, blah blah. We have one that someone else put together, but it's crazy out of date and jumbled, and I want to make a new one.

ANYWAY - this macro actually worked for me in terms of getting images from a folder into Excel. (Which is awesome! Other ones I try just do nothing and I can't figure out why.) BUT it plops all of them into the active cell, each stacked on top of each other.

How can I modify this so that it doesn't stack them all on top of each other? For example, I have a folder with Image1.png, Image2.png, Image3.png. B1 is the active cell. When I run this, I want Image1.png inserted into B1, Image2.png inserted into B2, Image3.png in B3, and so on? I searched around and think maybe it has something to do with "ActiveCell.Offset(0, -1).Select"? But that's as far as I got. Which is pathetic.

(Also it would be nice if it generated the file name in the adjacent column, but that's not a big deal. I'm not such an idiot where I can't figure out how to export a folder directory to a workbook.)

Thanks bunches!

ETA - ugh, this is way wordier than I meant it to be. Sorry! I was just really excited about that macro!

Hi Caitlinlilly, and welcome to the posting world. You were close, but using activecell.offset(0,-1).select moves left on the the range of cells (-1) ...

Try changing the code to this bit ...

Do While Len(strFileName) > 0
Set myPict = ActiveSheet.Pictures.Insert(strPath & strFileName)

With myPict
myPict.Top = ActiveCell.Top
myPict.Width = ActiveCell.Width
myPict.Height = ActiveCell.Height
myPict.Left = ActiveCell.Left
myPict.Placement = xlMoveAndSize
ActiveCell.Offset(0, 1).Value = strFileName
ActiveCell.Offset(1, 0).Select


End With