PDA

View Full Version : Updating external reference of a picture



Wilko v A
03-06-2009, 01:49 AM
Hi Everyone,

To collect a number of graphs in one workbook as a convenience for the user I use pictures with reference formulas (created with camera tool or Paste Special Picture Link). Now the file names where the pictures get their info from changes all the time (version number update). So I have to change the reference in the picture as well to point to the right file. Since I have the list of files available I can do this from within VBA, or so I thought. This is the code I use (forget about the complicated string manipulation in the beginning, it is used to calculate the reference):


Sub UpdatePictureReference()

Dim strFileName As String
Dim strOrganizationName As String
Dim strFormula As String

strOrganizationName = Right(ActiveSheet.Name, Len(ActiveSheet.Name) - InStr(1, ActiveSheet.Name, "ges") - 3)
strFileName = ThisWorkbook.Path & Application.PathSeparator & Application.Names("Source.file." & strOrganizationName & ".range").RefersToRange.Value
strFormula = "'" & strFileName & "'!View." & strOrganizationName & "." & ".graph"

On Error Resume Next
ActiveSheet.Shapes("Referenced Picture").Select
Selection.Formula = strFormula
If Err.Number <> 0 Then
MsgBox "Error updating the picture reference formula.", vbOKOnly, "Error"
End If
On Error GoTo 0
End Sub


Running the code results in a 1004 error: "Unable to set the Formula property of the Picture class". The actual syntax of setting the formula in the shape is the same as in a recorded macro.

Anybody any idea what could make this work??

Thanx in advance

Wilko