Inserting pictures into excel columns

[maddengm33]

Here is what I would like to be able to do:

In cells A1:A10, I have the numbers 1,2,3....10.
I have jpg images in a folder named PIX in my C drive, each of these pictures is numbered 1,2,3....10.
I would like to be able to place a formula in cell B1 which looks at cell A1 and if the number in A1 matches the name of a picture in the folder PIX, I would like for that picture to be displayed in cell B1. If the number in cell A1 does not match an image, then I would like the cell to be blank. The same goes for B2 looking at the value in A2, etc.
Is this possible?
I am not very familiar with macros or VBA; therefore, any instructions involving macros and/or VBA would need to be detailed.
Any help is greatly appeciated.

Jim

 

[maddengm33]

All the pictures will be jpg pictures. They will be named by just using a number. In the sheet cells, only the numbers will be used.

Let's try and tackle this project one piece at a time.

In cells A1 through A10 I will have the numbers 1 through 10. In the location C:\PIX I will have pictures that are named with numbers in the range of 1 through 10, but not every number in that range will have a picture (meaning, there will be less than 10 pictures). In cells B1 through B10, I will have the Hyperlink formula that will allow me to view the picture if the matching cell in the A column has a number that matches a picture in C:\PIX.

What I need is a formula/macro/code, etc. in cell C1 that basically says "Is there a picture in C:\PIX that matches the number in cell A1?" A simple yes or no answer in cell C1 will be fine. Then, I want to just copy that down the column so that when the pictures are updated in C:\PIX, I can just look at the A and C columns and see if the number in the A column has a picture.

Let me know if I need to clarify further.

Thanks.

Jim

 

[maddengm33]

still looking...

 

[Joe Was]

Store this in a module, like: Module1.
then in the cell you want to have the True or False show up in, put"

=FileExists(, A1,)

Note: Above is assuming the file name is in cell: A1.

Copy the cell's custom function and paste it into your range.

Note this function only works when the sheet is re-calculated, so unless a sheet cell is updated the function will not be dynamic as to the C:\PIX folder!

Private Function FileExists(fTag, fName, fExt) As Boolean
'Custom Sheet Function!
'Standard module code, like: Module1.
'Syntax: =FileExists("Drive:/Folder(s)/FileName")
'If file is found: Returns TRUE, else FALSE.
Dim myFolder As String

fTag = "C:\PIX"
fExt = ".jpg"
myFolder = Dir(fTag & fName & fExt)

If UCase(fName) = "HELP" Then
MsgBox " Syntax: =FileExists("", FileName,"")" & vbCr & vbCr & _
"Like ==> =FileExists("", Test,"")"
End If

If myFolder <> "" Then
FileExists = True
Else
FileExists = False
End If
End Function

 

[maddengm33]

The problem was solved by using this code:

Private Sub Worksheet_Activate()
Dim c As Range
For Each c In ActiveSheet.Range("B1:B10").Cells
If Dir(c.Cells.Value) = "" Then
c.Offset(0, 1).Cells.Value = "No"
Debug.Print "No"
Else
c.Offset(0, 1).Cells.Value = "Yes"
Debug.Print "Yes"
End If
Next
End Sub

Private Sub Worksheet_Calculate()
Dim c As Range
For Each c In ActiveSheet.Range("B1:B10").Cells
If Dir(c.Cells.Value) = "" Then
c.Offset(0, 1).Cells.Value = "No"
Debug.Print "No"
Else
c.Offset(0, 1).Cells.Value = "Yes"
Debug.Print "Yes"
End If
Next
End Sub

Thanks for helping me out.

 

[Joe Was]

Your code will give an error under some conditions try my function or use this modification of your code:

Private Sub Worksheet_Calculate()
Dim c As Range, myFile As String, myTest

For Each c In ActiveSheet.Range("B1:B10").Cells
myFile = "C:\PIX" & c.Cells.Value & ".jpg"
myTest = Dir(myFile)

If myTest <> "" Then
c.Offset(0, 1).Cells.Value = "Yes"
Debug.Print "Yes"
Else
c.Offset(0, 1).Cells.Value = "No"
Debug.Print "No"
End If
Next c
End Sub

Cell Function: in the cell that gets the True False add:
=FileExists(,B1,)

Private Function FileExists(fTag, fName, fExt) As Boolean
'Custom Sheet Function!
'Standard module code, like: Module1.
'Syntax: =FileExists(,FileName or cell Location,)
'If file is found: Returns TRUE, else FALSE.
Dim myFolder As String

fTag = "C:\PIX"
fExt = ".jpg"
myFolder = Dir(fTag & fName & fExt)

'To activate help input: FileExists(,"Help",)
If UCase(fName) = "HELP" Then
MsgBox " Syntax: =FileExists("",FileName,"")" & vbCr & vbCr & _
"Like ==> =FileExists("",Test,"")"
End If

If myFolder <> "" Then
FileExists = True
Else
FileExists = False
End If
End Function