Printing what is between tags

[Mark]

Hello, I curretly have some code to grab a line from a text document
that contains source code for a website. Anytime it sees a reference
to "href" it prints that entire line into a column, like the
following:

"
<P><STRONG>Exelon <BR></STRONG>Ethics Help Line <BR>Report an
anonymous and confidential ethics concern<BR>1-800-23-ETHIC
(1-800-233-8442)<BR><A href=""https://www.compliance-helpline.com/
welcomeExelon.jsp"">Employee Ethics Web Site</A></P></span></P>"

What I need tho, is just a part of that entire line, the <A
href:"...to be exact to make it look like this:

https://www.compliance-helpline.com/welcomeExelon.jsp.

Is there a way to reformat it after its been grabbed and printed into
the excel columns?

 

[Rick Rothstein \(MVP - VB\)]

It would be easier to fix up BEFORE you place it into the worksheet cell.
Let's assume the variable name the line is stored in when you first grab it
from the file is named TextInVariable, then this line will return just the
part of the text you want...

TextInVariable = Replace(Split(Split(HTML, "href=")(1), ">")(0), """", "")

So simply place the contents of TextInVariable into your cell instead.

Rick

 

[Rick Rothstein \(MVP - VB\)]

I'm not sure... is "href" always lower case letters? If not, you can use
this line of code instead of what I posted originally...

TextInVariable = Replace(Split(Split(HTML, "href=", , _
vbTextCompare)(1), ">")(0), """", "")

Rick

 

[David]

Hi,
You can use "text" functions once it is written into a worksheet to pull out
what you want. This does not quite work, but you can experiment and get it,
if you see what is happeneing.

Assuming it is in "A1"
=MID(A1,FIND("http",A1,1),LEN(A1)-FIND(">",A1,A2))

 

[Dave Peterson]

Can you change the cells in place?

If yes, then

Select the range to fix
Edit|Replace
what: * href=""
with: (leave blank)
replace all

followed by:
edit|replace
what: "">*
with: (leave blank)
replace all

If you can't do it in place, copy the range to a different location first. Then
fix the new location.

And if you need a macro, you can record one when you do it manually one time.

 

[James Snell]

You've got a few steps to go through there...

First you really want to get the pasted content back into single string.

To do that you'd need to either concatenate the cells together, which may or
may not be trivial. Or... if you can rely on the content still being in the
paste buffer then something like worksheet.pastespecial (to a temporary
worksheet if needed) to give you a nice, easy to work with string.

Once you have that then a bit of code like this would do it...


Sub ProcessHTMLFragment(fragment As String)
Dim tagSet As Variant
Dim tmpStr As Variant

tagSet = Split(fragment, ">")
For Each tmpStr In tagSet
If LCase(Left(tmpStr, 2)) = "<a" Then ProcessHTMLTag (tmpStr)
Next tmpStr
End Sub

Sub ProcessHTMLTag(tag As String)
Dim result As String
result = tag

result = Mid(tag, 1 + InStr(InStr(LCase(tag), "href"), tag, """"))
result = Left(result, InStr(2, result, """") - 1)
MsgBox result ' replace this with something useful
End Sub