Automatic Picture Insert

[Harley]

I have a "storyboard" template set up in an Excel workbook. Currently I manually insert(Insert, Picture from File) seven .jpg files into the workbook and move them to the appropriate location on the "storyboard" template. Then I have to resize them.

I'm wondering if there is any way I can have this done automatically. I would like excel to look for the pictures in a folder on my hard drive and automatically insert the pictures into 7 different locations on the template. Is that possible?

 

[Chas17]

Image1.Picture = LoadPicture _ ("H:OPSTAGGINGINFO_and_HelpSupport Filesstd1.jpg")

In () is the path to your jpg, jpg listed at end.
Chas

 

[Harley]

I'm getting the #NAME? error after I hit enter. Here is what I enter in the cell where I want the picture to be inserted:

=LoadPicture_("C:Documents and SettingsUserDesktopCMAGSNAG-00.jpg")

SNAG-00.jpg is the picture I'm trying to insert. Any other ideas?

 

[Chas17]

I thought you were using a userform, the prior response was vba code for a userform.

I sent you file via email, use an image box.

chas

 

[KenMacksey]

Hi
try this for each picture. Change the range for each location and the size factor if the pictures are different sizes.


' top left corner of picture
Range("C4").Select

ActiveSheet.Pictures.Insert( _
"C:Documents and SettingsKen MackseyMy Documents30 inch long salmon.jpg"). _
Select
' Vary the scalewidth and scaleheight value to suit the picture size and location size
Selection.ShapeRange.ScaleWidth 0.15, msoFalse, msoScaleFromTopLeft
Selection.ShapeRange.ScaleHeight 0.15, msoFalse, msoScaleFromTopLeft


HTH

Ken

 

[Harley]

I am still having trouble with this. I've created the image boxes and I am able to insert the pictures manually in the properties of each image. However, I still can't get the pictures to be inserted automatically.

 

[ernie mil]

Were you ever able to get the pictures to be inserted automatically?

I have a similar project inserting maps. I would like to know how to get excel to find the image by file name based on what is entered in Cell A1 AND automatically insert the map image.

Example:

Cell A1 = "N01"

For cell D5: Automatically find bmp file "N01.bmp" in MapsFolder and insert the file in Cell D5

 

[NateO]

Hello,

I am still having trouble with this. I've created the image boxes and I am able to insert the pictures manually in the properties of each image. However, I still can't get the pictures to be inserted automatically.

Better late than never?

Here's an example:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> InsrPics()
<SPAN style="color:darkblue">Dim</SPAN> i <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Integer</SPAN>, myPic <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, cl <SPAN style="color:darkblue">As</SPAN> Range
<SPAN style="color:darkblue">With</SPAN> Application.FileSearch
    .NewSearch
    .LookIn = "C:\Documents and Settings\nate\My Documents\My Pictures\"
    .Filename = ".jpg"
    <SPAN style="color:darkblue">If</SPAN> .Execute() > 0 <SPAN style="color:darkblue">Then</SPAN>
        Application.ScreenUpdating = 0
        <SPAN style="color:darkblue">For</SPAN> i = 1 <SPAN style="color:darkblue">To</SPAN> .FoundFiles.Count
            <SPAN style="color:darkblue">Set</SPAN> cl = Sheets(1).Cells(i * i, i * i)
            <SPAN style="color:darkblue">Set</SPAN> myPic = Sheets(1).Pictures.Insert(.FoundFiles(i))
            <SPAN style="color:darkblue">With</SPAN> myPic
                .Top = cl.Top
                .Left = cl.Left
                .ShapeRange.Height = cl.RowHeight * 4.2 <SPAN style="color:green">'4.2 rows tall</SPAN>
            <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
        <SPAN style="color:darkblue">Next</SPAN> i
        <SPAN style="color:darkblue">Set</SPAN> cl = Nothing: <SPAN style="color:darkblue">Set</SPAN> myPic = <SPAN style="color:darkblue">Nothing</SPAN>
        Application.ScreenUpdating = 1
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

You can do different things will cells and the loop.

 

[NateO]

Hello Ernie,

I have a similar project inserting maps. I would like to know how to get excel to find the image by file name based on what is entered in Cell A1 AND automatically insert the map image.

Example:

Cell A1 = "N01"

For cell D5: Automatically find bmp file "N01.bmp" in MapsFolder and insert the file in Cell D5

This looks like a slightly different animal. How about something like the following:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> Chk_Insrt()
<SPAN style="color:darkblue">Dim</SPAN> fso <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>, MyDir <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, Nme <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">String</SPAN>, myPic <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Object</SPAN>
<SPAN style="color:darkblue">Dim</SPAN> ws <SPAN style="color:darkblue">As</SPAN> Worksheet
<SPAN style="color:darkblue">Set</SPAN> fso = CreateObject("Scripting.FileSystemObject")
MyDir = "c:\temp\" <SPAN style="color:green">'Change Directory</SPAN>
Nme = Sheets(1).[a1] & ".bmp" <SPAN style="color:green">'Change Filename</SPAN>
<SPAN style="color:darkblue">If</SPAN> fso.FileExists(MyDir & Nme) <SPAN style="color:darkblue">Then</SPAN>
    Application.ScreenUpdating = 0
    <SPAN style="color:darkblue">Set</SPAN> ws = Sheets(1) <SPAN style="color:green">'Change the target sheet</SPAN>
    <SPAN style="color:darkblue">Set</SPAN> myPic = ws.Pictures.Insert(MyDir & Nme)
    <SPAN style="color:darkblue">With</SPAN> myPic
        .Top = ws.[d5].Top
        .Left = ws.[d5].Left
        .ShapeRange.Height = ws.[d5].RowHeight * 4.2 <SPAN style="color:green">'4.2 rows tall</SPAN>
    <SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">With</SPAN>
    <SPAN style="color:darkblue">Set</SPAN> myPic = Nothing: <SPAN style="color:darkblue">Set</SPAN> ws = <SPAN style="color:darkblue">Nothing</SPAN>
    Application.ScreenUpdating = 1
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
<SPAN style="color:darkblue">Set</SPAN> fso = <SPAN style="color:darkblue">Nothing</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Don't necessarily see the need for a full-blown search here. Hope this helps.

 

[Ekim]

Harley,

See Ron de Bruin's routine at:

http://makeashorterlink.com/?I52D22994

HTH

Mike