getting cell values from worksheet
 

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.

getting cell values from worksheet
 

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

--
Regards,
Tom Ogilvy




"dre" wrote in message
...
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.

getting cell values from worksheet
 

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?
-----Original Message-----
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

--
Regards,
Tom Ogilvy




"dre" wrote in message
...
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.


.

getting cell values from worksheet
 

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

--
Regards,
Tom Ogilvy


"dre" wrote in message
...
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?
-----Original Message-----
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

--
Regards,
Tom Ogilvy




"dre" wrote in message
...
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.


.

getting cell values from worksheet
 

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
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

--
Regards,
Tom Ogilvy


"dre" wrote in message
...
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?
-----Original Message-----
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

--
Regards,
Tom Ogilvy




"dre" wrote in message
...
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.


.



.