auto insert multiple images into file

[kydaki]

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>
I have searched and can't seem to find a similar post. If this is I apologize up front...<o></o>
<o></o>
I need to automatically insert 3 pictures into an Excel 2007 file. I have multiple files to do this in, but the process is exactly the same in every file.<o></o>
<o></o>
Pictures are named the same as my excel file with an added A, B or C, for example: 1310.xlsx, 1310A.jpg, 1310B.jpg and 1310C.jpg. All jpg files are sized correctly and need no adjustment, formatting, etc. Excel file lives in /datasheet directory, jpg’s live in /datasheet/images directory.<o></o>
<o></o>
In my Excel file (1310.xlsx) the file name (1310) is located in cell E3. I need to insert the 1310A.jpg file in cell A5, "B" in cell A18 and "C" in E18.<o></o>
<o></o>
I was hoping there was a way to use the file name in E3 to find the 3 jpg's and insert them in the correct cell listed above. <o></o>
<o></o>
Any help would be greatly appreciated!<o></o>

 

[Domenic]

Try...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] InsertPics()

    [color=darkblue]Dim[/color] strFileFolder [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strImageFolder [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] strFile [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] x [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] y [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] wkb [color=darkblue]As[/color] Workbook
    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] pic [color=darkblue]As[/color] Picture
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Integer[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]   [color=seagreen]'Turn off screen updating[/color]

    strFileFolder = "C:\datasheet\"   [color=seagreen]'Path to files[/color]
    
    [color=darkblue]If[/color] Right(strFileFolder, 1) <> "\" [color=darkblue]Then[/color] strFileFolder = strFileFolder & "\"   [color=seagreen]'Ensure path ends in a backslash[/color]
    
    strImageFolder = "C:\datasheet\Images\"   [color=seagreen]'Path to images[/color]
    
    [color=darkblue]If[/color] Right(strImageFolder, 1) <> "\" [color=darkblue]Then[/color] strImageFolder = strImageFolder & "\"   [color=seagreen]'Ensure path ends in a backslash[/color]
    
    strFile = Dir(strFileFolder & "*.xlsx")   [color=seagreen]'Call first file in folder[/color]
    
    x = Array("A", "B", "C")  [color=seagreen]'Assign an array of letters to a variable[/color]
    
    y = Array("A5", "A18", "E18")   [color=seagreen]'Assign an array of ranges to a variable[/color]
    
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Len(strFile) > 0
        [color=darkblue]Set[/color] wkb = Workbooks.Open(strFileFolder & strFile)   [color=seagreen]'Open the current file and assign the workbook to a variable[/color]
        [color=darkblue]Set[/color] wks = wkb.Worksheets(1)   [color=seagreen]'Assign the first sheet of the current file to a variable[/color]
        [color=darkblue]For[/color] i = [color=darkblue]LBound[/color](x) [color=darkblue]To[/color] [color=darkblue]UBound[/color](x)  [color=seagreen]'Insert the pictures in the current file[/color]
            [color=darkblue]Set[/color] pic = wks.Pictures.Insert(strImageFolder & wks.Range("E3") & x(i) & ".jpg")
            [color=darkblue]With[/color] pic
                .Top = wks.Range(y(i)).Top
                .Left = wks.Range(y(i)).Left
            [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]Next[/color] i
        wkb.Close savechanges:=[color=darkblue]True[/color]   [color=seagreen]'Save and close the current file[/color]
        strFile = Dir   [color=seagreen]'Call the next file in the folder[/color]
    [color=darkblue]Loop[/color]
    
    Application.ScreenUpdating = [color=darkblue]True[/color]   [color=seagreen]'Turn on screen updating[/color]
    
    MsgBox "Completed...", vbInformation
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[kydaki]

Domenic- This works beautifully!! Thanks so much!