Hi, I am trying to re-build a workbook with the following criteria. I have since left my old company and I can't remember how the Macro was written and the files it was linked to don't exist on my computer. Please help Thanks in advance.
Objective: To use a Macro/Formula to populate a jpeg from a specific file into a specific cell. We are hoping to have a file that we can add the jpeg name/number into and see the image used v having to search/clip/paste each one individually.
Comments:
* The file naming convention for the file path that was used previously was:
File Path\Jpeg Name.jpeg (Note: This is what "AB20" is in the example below)
* Here is an example of the old formula we used to populate an image (in the below case, it went into cell AS42):
=SHOWPIC(AB20,AS42,-20,-40,130,220)
* Here is the Macro written. There were two modules (Note: I have changed some of the letters to xxx so the original file name/path isn't exposed)
Module 1
Function ShowPic(PicFile As String, celDestination As Range, DeltaPointX As Long, DeltaPointY As Long, _
SizePointX As Long, SizePointY As Long) As Boolean
Dim shp As Shape
On Error GoTo Errhandler
With celDestination
For Each shp In .Parent.Shapes
If shp.TopLeftCell.Address = celDestination.Address Then
shp.Delete
ElseIf Round(shp.Left) = Round(.Left + DeltaPointX) And Round(shp.Top) = Round(.Top + DeltaPointY) Then
shp.Delete
End If
Next
Set shp = .Parent.Shapes.AddPicture(PicFile, True, True, .Left + DeltaPointX, .Top + DeltaPointY, _
SizePointX, SizePointY)
End With
ShowPic = True
Exit Function
Errhandler:
ShowPic = False
End Function
Module 2
Sub Showpics()
End Sub
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 08/14/2006 by Alex Gxxxxxg
'
' Keyboard Shortcut: Ctrl+o
'
Range("G3").Select
Selection.Copy
Range("G1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "C:\Documents and settings\gxxxxxxx\desktop\file"
Range("G3").Select
Selection.ShapeRange.Delete
Selection.Copy
Range("G1").Select
Application.CutCopyMode = False
End Sub
Objective: To use a Macro/Formula to populate a jpeg from a specific file into a specific cell. We are hoping to have a file that we can add the jpeg name/number into and see the image used v having to search/clip/paste each one individually.
Comments:
* The file naming convention for the file path that was used previously was:
File Path\Jpeg Name.jpeg (Note: This is what "AB20" is in the example below)
* Here is an example of the old formula we used to populate an image (in the below case, it went into cell AS42):
=SHOWPIC(AB20,AS42,-20,-40,130,220)
* Here is the Macro written. There were two modules (Note: I have changed some of the letters to xxx so the original file name/path isn't exposed)
Module 1
Function ShowPic(PicFile As String, celDestination As Range, DeltaPointX As Long, DeltaPointY As Long, _
SizePointX As Long, SizePointY As Long) As Boolean
Dim shp As Shape
On Error GoTo Errhandler
With celDestination
For Each shp In .Parent.Shapes
If shp.TopLeftCell.Address = celDestination.Address Then
shp.Delete
ElseIf Round(shp.Left) = Round(.Left + DeltaPointX) And Round(shp.Top) = Round(.Top + DeltaPointY) Then
shp.Delete
End If
Next
Set shp = .Parent.Shapes.AddPicture(PicFile, True, True, .Left + DeltaPointX, .Top + DeltaPointY, _
SizePointX, SizePointY)
End With
ShowPic = True
Exit Function
Errhandler:
ShowPic = False
End Function
Module 2
Sub Showpics()
End Sub
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 08/14/2006 by Alex Gxxxxxg
'
' Keyboard Shortcut: Ctrl+o
'
Range("G3").Select
Selection.Copy
Range("G1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "C:\Documents and settings\gxxxxxxx\desktop\file"
Range("G3").Select
Selection.ShapeRange.Delete
Selection.Copy
Range("G1").Select
Application.CutCopyMode = False
End Sub