|
#1
01-25-2007, 11:17 AM
|
|||
|
|||
|
excel vb macro - strip html??
Hello,
I am creating a report that has a column with html tags in almost every box. I am trying to create a vb macro that will remove all html tags from this column and leave the original data. Does anyone know of a script that is already written that I might be able to change for what I want to do? Can anyone offer any help in creating this from scratch? Any and all help will be much appreciated. Thanks a lot, Rich 
|
|
#2
01-25-2007, 11:40 AM
|
|||
|
|||
|
Set a reference to the Microsoft VBScript Regular Expressions library, then do this: http://www.4guysfromrolla.com/webtech/042501-1.shtml
__________________
Phil Weber http://www.philweber.com Please post questions to the forums, where others may benefit. I do not offer free assistance by e-mail. Thank you!
|
|
#3
11-13-2008, 05:34 PM
|
|||
|
|||
|
Hi Phil, thanks a lot for the advice. I tested Scott Mitchell's live demo for html stripping function and that is exactly what i need to do. I need to remove html code from a column in my excel file, but how do i do that using Mitchell's code? I only know how to create functions starting with an equal sign...can anyone help me?
|
|
#4
11-14-2008, 08:09 AM
|
||||
|
||||
|
Welcome to DevX
 Have you ever done any VBA coding before?
__________________
I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.  Please use [Code]your code goes in here[/Code] tags when posting code. Before posting your question, did you look here? Got a question on Linux? Visit our Linux sister site. Modifications Required For VB6 Apps To Work On Vista  Microsoft MVP 2005/2006/2007/2008/2009
|
Will that be a problem? I'm willing to buy a software (test it first) that will help me do that but don't know what to buy. I downloaded Detagger trial version but didn't know how to use it.
|
#6
11-17-2008, 11:25 AM
|
||||
|
||||
|
Quote:
Code:
=SUBSTITUTE(A1,"<HTML>","")
__________________
I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section. Please use [Code]your code goes in here[/Code] tags when posting code. Before posting your question, did you look here? Got a question on Linux? Visit our Linux sister site. Modifications Required For VB6 Apps To Work On Vista Microsoft MVP 2005/2006/2007/2008/2009
|
|
#7
11-17-2008, 12:02 PM
|
|||
|
|||
|
Hi Hack, no , I tried your function
=SUBSTITUTE(A1,"<HTML>","") and it didnt work. That only removes <HTML> tags from my description cells, I would have to use the function again and again for other tags such as <DIV> or <SPAN> and that function doesn't even erase closing tags! I would have to use the function again for </DIV> and </SPAN>. Isn't there a function that will tell my excel file "erase everything between tags?" Thank you
|
|
#8
11-17-2008, 01:31 PM
|
||||
|
||||
|
I only posted the use of the built in Excel function using = because you said that is what you knew how to do. It was just an example.
Yes, you would need to expand that for every possiblity. Quote:
Once written you would need to call it to execute it. So, do you want to jump into something you have never done before?
__________________
I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section. Please use [Code]your code goes in here[/Code] tags when posting code. Before posting your question, did you look here? Got a question on Linux? Visit our Linux sister site. Modifications Required For VB6 Apps To Work On Vista Microsoft MVP 2005/2006/2007/2008/2009
|
|
#9
11-17-2008, 02:33 PM
|
|||
|
|||
Well, this is somethind i NEED to do, it will take me some hours to change that html code into text with the substitute function, and then i would have to do it every time i need to update my datafeed. so why not take those hours and learn how to create that vba code that will help me change my html into text in minutes?
Quote:
 If not, any advice where I can get started? 
|
|
#10
11-17-2008, 02:38 PM
|
||||
|
||||
|
Getting started is the biggie. Have a look at these and start playing around. When you have questions pop on back.
http://msdn.microsoft.com/en-us/library/aa203714.aspx http://www.exceluser.com/help/vba/long005.htm http://education.mondadori.it/libri/...87-2_extra.pdf http://www.mvps.org/dmcritchie/excel/getstarted.htm
__________________
I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section. Please use [Code]your code goes in here[/Code] tags when posting code. Before posting your question, did you look here? Got a question on Linux? Visit our Linux sister site. Modifications Required For VB6 Apps To Work On Vista Microsoft MVP 2005/2006/2007/2008/2009
|
|
#11
11-18-2008, 07:35 PM
|
|||
|
|||
|
Thanks a lot, Hack. I have started studying macros and VBA but it will take longer than I thought to learn how to do what I need.
Do you know any software I can use to strip HTML code/convert to text my productdescription column on my excel file? thanks
|
|
#12
11-18-2008, 08:51 PM
|
|||
|
|||
|
Here is a function that removes HTML tags and common HTML Escape
characters: Code:
Public Function StripTags(html As String) As String
On Error Resume Next
Dim lt As Long
Dim gt As Long
Dim buf As String
Dim tag As String
'Save original string into buffer
buf = html
'Find and remove all HTML tags
lt = InStr(buf, "<")
gt = InStr(buf, ">")
Do While lt > 0 And gt > 0 And gt > lt
'extract tag
tag = Mid$(buf, lt, gt - lt + 1)
'strip tag from from buffer
buf = Replace(buf, tag, "")
'look for next tag
lt = InStr(buf, "<")
gt = InStr(buf, ">")
Loop
'Next remove any extranious carriage returns and line feeds
'- from the beginnig
Do While Left(buf, 1) = vbCr Or Left(buf, 1) = vbLf
buf = Mid$(buf, 2)
Loop
'- from the end
Do While Right(buf, 1) = vbCr Or Right(buf, 1) = vbLf
buf = Left$(buf, Len(buf) - 1)
Loop
'Next fix common HTML escape codes
buf = Replace(buf, " ", " ")
buf = Replace(buf, "&", "&")
buf = Replace(buf, """, """")
buf = Replace(buf, "&#", "#")
buf = Replace(buf, "<", "<")
buf = Replace(buf, ">", ">")
buf = Replace(buf, "%20", " ")
'strip off extra leading and trailing spaces
buf = Trim$(buf)
'return the result
StripTags = buf
End Function
|
|
#13
11-19-2008, 09:36 AM
|
||||
|
||||
|
Do you understand what Ron is doing in that function and how to run it?
__________________
I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section. Please use [Code]your code goes in here[/Code] tags when posting code. Before posting your question, did you look here? Got a question on Linux? Visit our Linux sister site. Modifications Required For VB6 Apps To Work On Vista Microsoft MVP 2005/2006/2007/2008/2009
|
|
#14
10-21-2009, 06:06 PM
|
|||
|
|||
|
Help
I would very much like to know how to run this module, can anyone help, I've copied it into the visual basic thingy, but don't know how to run it, i have a spreadsheet with tons of html in such as and I need to strip out all html and just leave the text? Can anyone help?
Take care Laura Last edited by Phil Weber; 10-22-2009 at 05:32 PM.
|
|
#15
10-22-2009, 05:32 PM
|
|||
|
|||
|
__________________
Phil Weber http://www.philweber.com Please post questions to the forums, where others may benefit. I do not offer free assistance by e-mail. Thank you!
|
 |
| Bookmarks |
«
Previous Thread
|
Next Thread
»
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
Linear Mode
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Access Table to Excel via VB | BMWBEAR | VB Classic | 2 | 12-13-2005 03:19 PM |
| MMFAN Retires | MMFAN | .NET | 20 | 09-23-2002 12:54 AM |
| VB & Excel Database | Byron Coker | VB Classic | 2 | 02-11-2002 04:00 PM |
| Extracting Excel Data Chart and Storing into 2-D array in VB | Eugene Lim | VB Classic | 1 | 07-25-2001 07:17 PM |
| VB and an Excel Spreadsheet | Dale Boyer | VB Classic | 6 | 01-29-2001 01:57 PM |
All times are GMT -4. The time now is 01:53 AM.