Opening a jpg file

[Nasser]

Hi Hans,

We sometimes don't want to keep a jpg image on the excel sheet or on the ppt. So it's a good idea if we use a button to open it when needed. Something needs to be added in my code before “Open” right! to be able to open the jpg file when I am in excel.

Private Sub CommandButton1_Click()
.........................Open "C:\Users\admc\Desktop\tdstr.jpg"
ActiveWorkbook.Sheets(1).Select
End Sub

Similarly i want to do the same thing when i am using PPT. I need to show some jpg images kept on the desktop during my presentation by just clicking on the buttons.

I tried to use IMAGE (ActiveX Control) but it did not work with me.

Cheers,
Nasser.

[HansV]

Hello Nasser,

Please don't direct questions at me personally. I may be the most active poster here, but that will not always be so.

Do you want to embed the picture in a worksheet (or slide), or do you want to open it in a window by itself?

[Nasser]

Hi,

Worksheet and slides. In some sheets i want to open pictures as well as for my presentation by just clicking on buttons. All my picures are kept on my desktop.

What do you mean by "in a window by itself"?

Regards,
Nasser

[HansV]

By "in a window by itself" I meant that the picture would open in your default application for viewing .jpgs - Windows Picture Viewer or Paint, for example.

If you want to embed a picture in a worksheet, you can use code like this (adapted from Ababenchrist's code in Problem using a large amount of Range cells):

Code: Select all

Sub InsertPicture()
  Dim Pic As Excel.Picture
  Dim PicLocation As String
  Dim MyRange As Range

  PicLocation = Application.GetOpenFilename( _
    FileFilter:="JPG files (*.jpg), *.jpg", _
    Title:="Select a picture")
  If PicLocation = "False" Then Exit Sub
  Set Pic = ActiveSheet.Pictures.Insert(PicLocation)

  With Pic.ShapeRange
    .Left = ActiveCell.Left
    .Top = ActiveCell.Top
  End With
End Sub

In PowerPoint you can just insert a picture interactively while you're designing the presentation; I don't think you'd want to insert a picture during a slide show.

[Nasser]

Hi,

When i run the program, it opens the "select picture window" and then i have to select my pic. That's fine but i want to skip this stage. The location of my picture is for example: C:\Documents and Settings\nbouhenna\Desktop\recloser.jpg. When i click on the button my picture i want the program to open the picture automatically. Where shall i insert the location of the picture in the program?

Thanks,
Nasser.

[HansV]

In that case, you can specify PicLocation directly instead of using GetOpenFilename: change the lines

Code: Select all

  PicLocation = Application.GetOpenFilename( _
    FileFilter:="JPG files (*.jpg), *.jpg", _
    Title:="Select a picture")
  If PicLocation = "False" Then Exit Sub

to

Code: Select all

  PicLocation = "C:\Documents and Settings\nbouhenna\Desktop\recloser.jpg"

[Nasser]

Great :), thanks

Nasser.

[Jan Karel Pieterse]

Have you tried using hyperlinks instead of VBA?

[Nasser]

Hi,

No i did not try hyperlinks so far.

Cheers,
Nasser.

[Jan Karel Pieterse]

Suppose you have the path to the desktop in cell A1 and in cells B1:B10 some filenames, then this function will create a direct hyperlink to those files:
=HYPERLINK("file://"&$A$1&B1)
copy down to match your # of files.

[Nasser]

Hi Jan,

Step 1: You meant i right click on cell A1 and select "hyperlink" and what to do after that?
Step 2: I put in B1 the first file name and in B2 the second file name which are kept on the desktop in case if i have only two pictures to display

Few Clarifications will be nice

Cheers,
Nasser.

[HansV]

Jan Karel meant something like this:

x168.png

Enter the path of the folder containing the pictures in cell A1. Make sure that it ends in a backslash.
Enter the names of the picture files in cells B1, B2, etc.
Enter the following formula in C1 (you can see the formula in the formula bar in the screenshot):

=HYPERLINK("file://"&$A$1&B1)

This formula will produce a clickable hyperlink in cell C1. Clicking it will open the picture in the application associated with the type of picture.
Fill the formula from C1 down as far as needed.

[Nasser]

Hi Jan,

It works, good thanks for the information.

Cheers,
Nasser

[Nasser]

Hi,

With the VBA, is it possible to display the picture where i want it to be. Means by selecting the area where it should be put. So far it is following the active cell. The top left corner of the picture is exactely the active cell.

Cheers,
Nasser
Nasser.

[HansV]

In the bit of code

Code: Select all

  With Pic.ShapeRange
    .Left = ActiveCell.Left
    .Top = ActiveCell.Top
  End With

you can replace ActiveCell with a specific cell, e.g. Range("D14") if you want the upper left corner of the picture to be on cell D14.

[Nasser]

Many thanks Hans :).

Cheers,
Nasser

[Nasser]

Hi,

One more thing to add :) if possible otherwise i am happy with what i am getting. Now by clicking second time, third time etc ..., the pictures keep getting opened, one on the top of other. Is there a way to open only one time the picture even after clicking many times the button.

Cheers,
Nasser.

[HansV]

Perhaps something like this:

Code: Select all

Sub InsertPicture()
  Dim Pic As Excel.Picture
  Dim PicLocation As String
  Dim MyRange As Range
  Static HasRun As Boolean

  If HasRun = True Then
    Exit Sub
  End If
  HasRun = True

  PicLocation = Application.GetOpenFilename( _
    FileFilter:="JPG files (*.jpg), *.jpg", _
    Title:="Select a picture")
  If PicLocation = "False" Then Exit Sub
  Set Pic = ActiveSheet.Pictures.Insert(PicLocation)

  With Pic.ShapeRange
    .Left = Range("C4").Left
    .Top = Range("C4").Top
  End With
End Sub

[Nasser]

Hi,

Yes it works fine that's what i want but when i delete the picture and try to open again, it wont open. I have to reset the code. The user wont know how to do it.

Cheers,
Nasser.

[HansV]

The following version will delete the picture whose upper left corner is the specified cell, if there is one, before inserting the new picture:

Code: Select all

Sub InsertPicture()
  Dim Pic As Excel.Picture
  Dim PicLocation As String
  Dim MyRange As Range

  Set MyRange = Range("C4")
  
  PicLocation = Application.GetOpenFilename( _
    FileFilter:="JPG files (*.jpg), *.jpg", _
    Title:="Select a picture")
  If PicLocation = "False" Then Exit Sub

  For Each Pic In ActiveSheet.Pictures
    If Pic.Top = MyRange.Top And Pic.Left = MyRange.Left Then
      Pic.Delete
      Exit For
    End If
  Next Pic

  Set Pic = ActiveSheet.Pictures.Insert(PicLocation)

  With Pic.ShapeRange
    .Left = MyRange.Left
    .Top = MyRange.Top
  End With
End Sub