Solved

Create macro to read filename in a column and insert same name file in cell as a picture

Posted on 2007-12-07
6
899 Views
Last Modified: 2012-06-22
I would like to know how I can create a macro that when run will look in a cell for a filename and if one exists, look on the hard drive in a folder for an image with same filename and then place that image in the cell next to the one with the filename.
i.e cell A1 would be blank, cell B1 would contain the text "image1"
i would want the macro to read the name of the image in cell B2, then look in a folder for an image file with the same name, then place that image into cell A1.
I would want to run it on a range of entries, for example B1-B20 may all contain image names.
Also the column with the image names may only contain part of the image name, and it would not have the extension, so I would need it to take the entry of B1 in the above example, and add .gif, so the filename its searching for would be image1.gif
 
0
Comment
Question by:paul_at_work
  • 3
  • 3
6 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 20428360
The code below, when added to a worksheet code module, displays the picture associated with a file name entered in a specific cell. For the code to work, the cell containing the file name must be named "EntryCell" and the range of cells where the picture is to be placed named "ImageArea". The image is fitted into the image area range so that it fits within the range but maintains its proportions.

[Begin Code Segment]

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Picture As Picture
   Dim Cell As Range

   If Not Intersect(Target, [B1:B20]) Is Nothing Then
   For Each Cell In Intersect(Target, [B1:B20]).Cells
      On Error Resume Next
      Shapes("Picture" & Cell.Address(False, False)).Delete
      On Error GoTo 0
      If Len(Cell) > 0 Then
         Set Picture = Me.Pictures.Insert("C:\Full\Path\To\Pictures\" & Cell & ".jpg")
         Picture.Name = "Picture" & Cell.Address(False, False)
         Picture.Top = Cell.Offset(0, 1).Top
         Picture.Left = Cell.Offset(0, 1).Left
         Picture.Width = Picture.Width * Cell.Offset(0, 1).Height / Picture.Height
         Picture.Height = Cell.Offset(0, 1).Height
      Next Cell
   End If

End Sub

[End Code Segment]

Kevin
0
 

Author Comment

by:paul_at_work
ID: 20429026
Hi Kevin, thanks for the quick response.
I am having some trouble with the code above.
firstly
pasting it exactly into a new module and closing the window does not appear to save the macro. When I attempt to run the macro there is nothing listed.
I do not know very much about VB, but after playing around i could get the module to appear as a macro to be run, but only by changing the first line to
Sub Worksheet_Change()
Then when I run the macro, i get the following error
compile error - sub or function not defined and it highlights the word shape on line 7
If this because of the line  "Dim Picture As Picture"- should that be Dim Shapes As Picture
If I change it to
DIm Shapes As Picture the code runs further but then fails with " invalid use of me keyword"
so i removed me. from the code and it goes a bit further
But this time fails with "compile error Next without for" and it highlights the word Next

As I say I have no experience of coding other than years ago playing around with basic on a spectrum! So I have no idea if the changes i made above make it better or worse, but I do know that using the code as is above fails.

Hope you can help.

cheers
Paul
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 20431112
Sorry about that. I did not test the code. Place the in the code module belonging to the worksheet on which you want to place the images. No need to run a macro. Just enter a value in cells B1:B20 and the picture, if it exists, will appear in column A. Change the path in the routine to point to your images folder before testing.

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Picture As Picture
   Dim Cell As Range
   
   Const Path = "C:\Documents and Settings\Your Name\My Documents\My Pictures\"

   If Not Intersect(Target, [B1:B20]) Is Nothing Then
      For Each Cell In Intersect(Target, [B1:B20]).Cells
         On Error Resume Next
         Shapes("Picture" & Cell.Address(False, False)).Delete
         On Error GoTo 0
         If Len(Cell) > 0 Then
            If Len(Dir(Path & Cell & ".jpg")) > 0 Then
               Application.ScreenUpdating = False
               Set Picture = Me.Pictures.Insert(Path & Cell & ".jpg")
               Picture.Name = "Picture" & Cell.Address(False, False)
               Picture.Top = Cell.Offset(0, -1).Top
               Picture.Left = Cell.Offset(0, -1).Left
               Picture.Width = Picture.Width * Cell.Offset(0, -1).Height / Picture.Height
               Picture.Height = Cell.Offset(0, -1).Height
               Application.ScreenUpdating = True
            End If
         End If
      Next Cell
   End If

End Sub

Kevin
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:paul_at_work
ID: 20441281
Hi Kevin,
thanks again for the reply, unfortunately I done what you said and nothing happens when i type an entry in the range of cells b1:b20. i started excel with a blank workbook. pressed ALT+F11, insert module, pasted it in from above, changed the path to the images and the 2 jpg to gif, closed and returned to excel, tried typing in image1 into cell b1and nothing happens (there is an image called image1.gif in the c:\excel_test path

i changed this line of code
Const Path = "C:\excel_test\"
thats the path that contains my images

i also changed the filename extensions to .gif as im using gifs rather than jpgs althought i did try it with jpgs aswell and it didnt work. so i changed these 2 lines- replacing jpg with gif

If Len(Dir(Path & Cell & ".gif")) > 0 Then
               Application.ScreenUpdating = False
               Set Picture = Me.Pictures.Insert(Path & Cell & ".gif")

Any ideas?

Many Thanks
Paul
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 20502660
Did you place the code in the worksheet code module? It won't work unless you do.

To add VBA code to a worksheet module in an Excel workbook, right-click on the worksheet tab at the bottom of the window and select View Code. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook. To find a worksheet module when already in the VBE, press CTRL+R to open the VBE project explorer. Find the worksheet module in which the code will be placed - each worksheet module is pre-assigned a name such as "Sheet1 (Sheet1)" where the name inside the parenthesis is the worksheet's tab name. Double-click the desired module and paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

Kevin
0
 

Author Closing Comment

by:paul_at_work
ID: 31413383
Thanks Kevin, that worked great.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel Macro - Lookup-Highlight 4 25
VLOOKUP Function MS Excel 2010 2 22
Macro 3 21
Determine 90 days from most recent date 3 26
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now