Insert a picture

Hi hutchval, like this?

ActiveSheet.Pictures.Insert("C:\PlatMaps\" & [a1].value & ")"

Yes you only need add the cell value to the path.

ActiveSheet.Pictures.Insert ("C:\PlatMaps\" & Range("A1"))

Where cell "A1" contains a valid picture name ie MyPic.bmp
or MyPic.jpg
etc.

I see Colo beat me to it, same answer though!!!!!

at 15:31. and same time. LOL

On a related topic, I'm trying to insert a jpg image into an Image Control using VB LoadPicture. This works, but creates a huge (many mb's) excel file. The jpg may be converted to bmp.

Is there a better way to insert a jpg? I can insert the image manually with Insert - Picture without the huge file increase so I know excel can do it.

My brief testing with ActiveSheet.Pictures.Insert(FileSpec) show that it will insert the image without the huge excel file increase. Can I specify the insertion location and image size with Pictures.Insert?

thanks.

Hi Anonymous, your name sounds familiar?

I dont know how big your pics are, but they can increase the size of the Excel file quite dramatically.

This code will clear Image1 on Sheet1 before saving, and Load it again when workbook is opened.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Sheet1").Image1.Picture = Nothing
End Sub

Private Sub Workbook_Open()
Sheets("Sheet1").Image1.Picture = LoadPicture("C:\My Documents\MyPic.jpg")
End Sub

You can adapt this to suit your needs.

Insomniac

Insomniac, thanks for the suggestion. This is one of the better solutions on the table. Load/Remove images at start/exit would solve some of the problems, but I'm concerned there may be other side effects. If any other forumites have additional suggestions, please submit them.

My excel file is 1.5 mb. I want to add approx 10 jpg/gif images whose size ranges from 25 kb to 120kb. After adding the images, the excel file is 20 mb. I'm running a new P4 2.5 ghz. The 20 mb file slows down my machine, performance will be worse on other user's older machines.

I'd also like to keep the images in the excel file to avoid losing them. I need to keep the report for approx 5 years in case i'm reviewed by a state agency.

I will print the excel file to a pdf document and provide the pdf document to my client. Surprisingly, when I printed my 20 gb excel file to pdf, the pdf file was 400 kb (very acceptable for the final pdf report).

The self contained pdf file may be adequate for a self contained archive, but it would be nice to ensure I can regenerate the report from the excel file.

thanks.

Another user (Keith) submitted this suggestion:

Yes use AddPicture

Syntax
expression.AddPicture(FileName, LinkToFile, SaveWithDocument, Left, Top,
Width, Height)

Dim mysht As Worksheet
Set mysht = ActiveSheet

mysht.Shapes.AddPicture "C:\sample.jpg", True, msoCTrue, 100, 100, 100, 100

Keith

AddPicture inserts the images without adversely affecting the Excel file size.
I also implemented a "Delete Loaded Pictures" button which will remove the pictures so they may be reselected and reloaded:

For Each ShapeObject In ActiveSheet.Shapes
If Left(ShapeObject.Name, = "Picture " Then
MsgBox "delete " & ShapeObject.Name
ShapeObject.Delete
End If
Next ShapeObject

muchas gracias.