Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing what is between tags
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<STRONGExelon <BR</STRONGEthics Help Line <BRReport an anonymous and confidential ethics concern<BR1-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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing what is between tags
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 "Mark" wrote in message ... 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<STRONGExelon <BR</STRONGEthics Help Line <BRReport an anonymous and confidential ethics concern<BR1-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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing what is between tags
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 "Rick Rothstein (MVP - VB)" wrote in message ... 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 "Mark" wrote in message ... 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<STRONGExelon <BR</STRONGEthics Help Line <BRReport an anonymous and confidential ethics concern<BR1-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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing what is between tags
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)) "Mark" wrote: 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<STRONGExelon <BR</STRONGEthics Help Line <BRReport an anonymous and confidential ethics concern<BR1-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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing what is between tags
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. Mark wrote: 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<STRONGExelon <BR</STRONGEthics Help Line <BRReport an anonymous and confidential ethics concern<BR1-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? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing what is between tags
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 "Mark" wrote: 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<STRONGExelon <BR</STRONGEthics Help Line <BRReport an anonymous and confidential ethics concern<BR1-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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
name tags | Excel Discussion (Misc queries) | |||
Smart Tags | Excel Discussion (Misc queries) | |||
Edit Mp3 tags | Excel Programming | |||
smart tags | Excel Worksheet Functions | |||
mp3 tags | Excel Programming |