What does the hyperlink look like (just curious)?

Option Explicit
Sub testme()
'select range, Dave Peterson
Dim wks As Worksheet
Dim myFormula As String
Dim QuotePos As Long
Dim myRng As Range
Dim myCell As Range

Set wks = ActiveSheet
With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, .UsedRange)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "not in the used range"
Exit Sub
End If

For Each myCell In myRng.Cells
If myCell.HasFormula Then
myFormula = LCase(myCell.Formula)
If myFormula Like "=hyperlink(""*" Then
myFormula = Mid(myFormula, 13)
QuotePos = InStr(1, myFormula, Chr(34), vbTextCompare)
If QuotePos = 0 Then
'do nothing
Else
myFormula = Left(myFormula, QuotePos - 1)
If myCell.Column 1 Then
myCell.Offset(0, -1).Value = myFormula
End If
Select Case Right(myFormula, 4)
Case Is = ".jpg", ".bmp", ".gif", ".png"
If InsertPicComment(myCell, myFormula) = True Then
myCell.Offset(0, 5).Value = ""
Else
myCell.Offset(0, 5).Value = "x"
End If
End Select
End If
End If
End If
Next myCell


End Sub
Function InsertPicComment(myCell As Range, PictFileName As String) As Boolean

Dim testStr As String

testStr = ""
On Error Resume Next
testStr = Dir(PictFileName)
On Error GoTo 0

InsertPicComment = False
If testStr = "" Then
'do nothing, picture not found
Else
If myCell.Comment Is Nothing Then
myCell.AddComment Text:="" 'testStr
Else
myCell.Comment.Text Text:="" 'myCell.Comment.Text & "--" & testStr
End If
On Error Resume Next
myCell.Comment.Shape.Fill.UserPicture PictFileName
If Err.Number < 0 Then
'it failed
Err.Clear
Else
InsertPicComment = True
End If
On Error Resume Next
End If

End Function


Max wrote:

"Dave Peterson" wrote:
If you start a new worksheet and recreate that hyperlink manually
-- not copy and paste, what happens?

Just tried it as suggested above,
[ via Insert Hyperlink, checked link is good,
then manually "converted" the cell's hyperlink to a HYPERLINK formula,
double-checked formula link's good as well and then ran the sub ]
but was hit with the same error msg with these few stubborn cells

The sub writes the path ok in the col to the left,
but the pics don't insert in the comments (empty comment created)

Could I request for a tweak to the sub
to skip such instances and resume its run
with perhaps an "X" written in an empty col ?
(say, 5 cols to the right of the selected col with the HYPERLINK formula)

I could then filter out these "X"'s for manual insertion/action later

Thanks
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

--

Dave Peterson