PDA

View Full Version : Importing html's source code into Excel !



q1q2q3
03-19-2007, 08:51 AM
I open the source code of html and import it into the excel file. Unfortunately, the source code has no format which is in a chaos after importing into it, I want to execute some criteria. My criteria collects all different full path of hyperlinks like http://hongkong.scmp.com/hknews/ZZZKAG1TZYE.html" only

I have written the program as follow:


Dim num As String
Dim r As Range


finalrow = Range("A" & Rows.Count).End(xlUp).Row

num = InputBox("Enter Number to find:")


X = 2
h = 1


While X < finalrow
If num <> "" Then
Set r = Sheets("file").Cells.Find(num, LookIn:=xlValues)
MsgBox r
If Not r Is Nothing Then
Sheets("file").Rows(r.Row).Copy Destination:=Sheets("search").Rows(h)

End If
End If
X = X + 1
If Not r Is Nothing Then
h = h + 1
End If
Wend

End Sub

This program is extracted all the prefix like "ZZZ" into the other sheet.
After running the program, it will copy the whole row which contains the prefix like "ZZZ" into the new sheet.

Afterwards, the findings are not accurate. it is because one only cell contains much of the html's reserve words and html's command statement like one cell containing

A masked gunman shot three times at security guard Zafar Iqbal Khan, and claimed to be carrying a bomb when he robbed HK$500,000 in cash from a Tsuen Wan bank in 2001, the Coroner's Court heard on Friday. <P></P> <FONT COLOR="#000066" FACE="ARIAL,HELVETICA" SIZE="4"><B><A HREF="http://hongkong.scmp.com/hknews/ZZZ21G1TZYE.html" class="tslink">Li Ka-shing hits No.9 on global billionaires' list</A></B></FONT><BR>

Because of this, the search statement cannot perform very well.

Now, how can I improve

1. one cell containing much strings which make the findings inaccurate.

2. collect all hyperlinks only


Thank you