getting cell values from worksheet

  • Thread starter Thread starter dre
  • Start date Start date
D

dre

I have a an excel file that contains one worksheet.
The worksheet contains more than 1500 rows of hyperlinks.
I need to get the actual value of each cell that contains
a hyperlink on the worksheet. For example, The display
value for cell A2 is "Link to document". The actual value
for the cell A2 is "A2=hyperlink("document1.doc","Link to
document")". I need to parse through the cell values
extracting the values that is within the hyperlink
parentheses.

Any help would be appreciated.
 
Sub Tester12()
For Each cell In Selection
sForm = cell.Formula
If InStr(1, sForm, "=hyperlink(", vbTextCompare) Then
sform1 = Right(sForm, Len(sForm) - 10)
iloc = InStr(sform1, ",")
sForm2 = Left(sform1, iloc - 1)
sForm2 = Right(sForm2, Len(sForm2) - 1)
sForm3 = Right(sform1, Len(sform1) - iloc)
sForm3 = Left(sForm3, Len(sForm3) - 1)

End If
cell.Offset(0, 1).Value = Evaluate(sForm2)
cell.Offset(0, 2).Value = Evaluate(sForm3)
Next

End Sub
 
Great,
How can I write the results to a text file though? Also,
some of the values appear like #VALUE! I would assume that
the length of the data was too long?
 
I got lazy and used evaluate to remove the double quotes - apparently some
of your labels are being interpreted as worksheet functions and returning
#Value. Here is a revision (change C:\Data\Textfile1.txt to reflect the
text file to write to)

Sub Tester12aa()
Dim f As Long, sform As String, sFrom1 As String
Dim sForm2 As String, sForm2a As String
Dim sForm3 As String, sForm3a As String
f = FreeFile()
Open "C:\Data\Textfile1.Txt" For Output As #f
For Each cell In Selection
sform = cell.Formula
If InStr(1, sform, "=hyperlink(", vbTextCompare) Then
sform1 = Right(sform, Len(sform) - 10)
iloc = InStr(sform1, ",")
sForm2 = Left(sform1, iloc - 1)
sForm2 = Right(sForm2, Len(sForm2) - 1)
sForm3 = Right(sform1, Len(sform1) - iloc)
sForm3 = Left(sForm3, Len(sForm3) - 1)

End If
sForm2a = Application.Substitute(sForm2, Chr(34), "")
sForm3a = Application.Substitute(sForm3, Chr(34), "")
sLine = sForm2a & ", " & sForm3a
Print #f, sLine
Next
Close #f
End Sub
 
Thanks a million. Worked like a charm!
-----Original Message-----
I got lazy and used evaluate to remove the double quotes - apparently some
of your labels are being interpreted as worksheet functions and returning
#Value. Here is a revision (change
Click to expand...
C:\Data\Textfile1.txt to reflect the
 
Back
Top