Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find string in web page
Now have a little other qst:
I have this web page attached, i want to find the word "Trovati:" and get the value near tath in this case is: 827 , after store 827 in a cell of sheet. tath is all. Note:already have a VBA code to naviate in web page. i use this part of code: ..... Dim IE As Object Set IE = CreateObject("InternetExplorer.Application") ShowWindow .hwnd, SW_MAXIMIZE ..Visible = True ..navigate "http://xxxx" .... Tks. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" <!-- saved from url=(0078)http://telefoni/trovadipendenze.asp?... RCA=&TIPRIC= -- <HTML<HEAD<TITLE</TITLE <META http-equiv=Content-Type content="text/html; charset=windows-1252" <META content="Microsoft FrontPage 5.0" name=GENERATOR</HEAD <BODY vLink=#ffffff link=#00ffff bgColor=#344ab1 <P style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 20px" align=center<B<FONT color=#ffffff<FONT face=Arial size=1Trovati </FONT<FONT face=Arial size=2827 </FONT</FONT</B<FONT face=Tahoma color=#ffffff size=1<BAggiornamento al : 06/12/2007</B</FONT </P <DIV align=center <CENTER <TABLE height=1 cellSpacing=0 cellPadding=0 width=731 background=trovadipendenze_file/sfondocell3.jpg border=0 <TBODY <TR <TD vAlign=center align=left width=232 height=4 <P style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 23px"<B<FONT face="MS Sans Serif" color=#ffff00 size=2Dipendenze</FONT</B </P</TD</CENTER <TD vAlign=center align=left width=81 height=4 </TD <CENTER <TD vAlign=center align=left width=70 height=4 <P align=center<FONT face="MS Sans Serif" color=#ffff00 size=2<BTelefono</B</FONT</P</TD <TD vAlign=center align=left width=75 height=4 </TD <TD vAlign=center align=left width=43 height=4 </TD <TD vAlign=center align=left width=120 height=4 </TD <TD vAlign=center align=left width=71 height=4<FONT face="MS Sans Serif" color=#ffff00 size=2Cod.Sede</FONT </TD</TR</TBODY</TABLE</CENTER</DIV <P style="MARGIN-TOP: 5px; MARGIN-BOTTOM: 5px" <DIV align=center <CENTER <TABLE height=24 cellSpacing=0 cellPadding=0 width=731 background=trovadipendenze_file/sfondocell2.gif border=0 <TBODY <TR <TD vAlign=center align=left width=31 height=6 <P align=center<FONT face="MS Sans Serif" size=1</FONT </P</TD <TD vAlign=center align=left width=216 height=1<B<FONT face="Microsoft Sans Serif" color=#ffffff size=1ABBIATEGRASSO </FONT</B</TD <TD vAlign=center align=left width=98 height=6 <P align=left </P</TD <TD vAlign=center align=middle width=281 height=6 <P align=left<B<FONT face=Arial color=#ffffff size=202 94963900 - 94963915 fax </FONT</B</P</TD <TD vAlign=center align=left width=105 height=6<FONT face="Microsoft Sans Serif" color=#ffffff size=2 <P align=center4375</FONT</P</TD</TR <TR <TD vAlign=center align=left width=310 colSpan=3 height=18 <P style="MARGIN-LEFT: 30px"<FONT face=Arial color=#00ffff size=1<A title="Risultato: Tutte le sedi" href="http://telefoni/TrovaDipendenze.asp?START=1&NPAG=1&VPARTENZA=1&VCE RCA=REGIONE-NORD OVEST&TIPRIC=5"REGIONE-NORD OVEST </A</FONT</P</TD <TD vAlign=center align=left width=346 colSpan=2 height=18<FONT face=Arial size=1<FONT color=#00ffff20081 ABBIATEGRASSO - PIAZZA CASTELLO, 19 </FONT</FONT</TD</TR</TBODY</TABLE</CENTER</DIV <P style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 15px" align=center <DIV align=center <CENTER <TABLE height=24 cellSpacing=0 cellPadding=0 width=731 background=trovadipendenze_file/sfondocell2.gif border=0 <TBODY <TR <TD vAlign=center align=left width=31 height=6 <P align=center<FONT face="MS Sans Serif" size=1</FONT </P</TD <TD vAlign=center align=left width=216 height=1<B<FONT face="Microsoft Sans Serif" color=#ffffff size=1ACIREALE </FONT</B</TD <TD vAlign=center align=left width=98 height=6 <P align=left </P</TD <TD vAlign=center align=middle width=281 height=6 <P align=left<B<FONT face=Arial color=#ffffff size=2095 891890 - 891734 fax </FONT</B</P</TD <TD vAlign=center align=left width=105 height=6<FONT face="Microsoft Sans Serif" color=#ffffff size=2 <P align=center1752</FONT</P</TD</TR <TR <TD vAlign=center align=left width=310 colSpan=3 height=18 <P style="MARGIN-LEFT: 30px"<FONT face=Arial color=#00ffff size=1<A title="Risultato: Tutte le sedi" href="http://telefoni/TrovaDipendenze.asp?START=1&NPAG=1&VPARTENZA=1&VCE RCA=REGIONE SUD&TIPRIC=5"REGIONE SUD </A</FONT</P</TD <TD vAlign=center align=left width=346 colSpan=2 height=18<FONT face=Arial size=1<FONT color=#00ffff95024 ACIREALE - CORSO ITALIA, 2 </FONT</FONT</TD</TR</TBODY</TABLE</CENTER</DIV <P style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 15px" align=center <DIV align=center <CENTER <TABLE height=24 cellSpacing=0 cellPadding=0 width=731 background=trovadipendenze_file/sfondocell2.gif border=0 <TBODY <TR <TD vAlign=center align=left width=31 height=6 <P align=center<FONT face="MS Sans Serif" size=1</FONT </P</TD <TD vAlign=center align=left width=216 height=1<B<FONT face="Microsoft Sans Serif" color=#ffffff size=1ACQUI TERME </FONT</B</TD <TD vAlign=center align=left width=98 height=6 <P align=left </P</TD <TD vAlign=center align=middle width=281 height=6 <P align=left<B<FONT face=Arial color=#ffffff size=20144 356090 - 356088 fax </FONT</B</P</TD <TD vAlign=center align=left width=105 height=6<FONT face="Microsoft Sans Serif" color=#ffffff size=2 <P align=center0346</FONT</P</TD</TR <TR <TD vAlign=center align=left width=310 colSpan=3 height=18 <P style="MARGIN-LEFT: 30px"<FONT face=Arial color=#00ffff size=1<A title="Risultato: Tutte le sedi" href="http://telefoni/TrovaDipendenze.asp?START=1&NPAG=1&VPARTENZA=1&VCE RCA=REGIONE. NORD-OVEST&TIPRIC=5"REGIONE. NORD-OVEST </A</FONT</P</TD <TD vAlign=center align=left width=346 colSpan=2 height=18<FONT face=Arial size=1<FONT color=#00ffff15011 ACQUI TERME - PIAZZA LEVI, 11 </FONT</FONT</TD</TR</TBODY</TABLE</CENTER</DIV <P style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 15px" align=center <DIV align=center <CENTER <TABLE height=24 cellSpacing=0 cellPadding=0 width=731 background=trovadipendenze_file/sfondocell2.gif border=0 <TBODY <TR <TD vAlign=center align=left width=31 height=6 <P align=center<FONT face="MS Sans Serif" size=1</FONT </P</TD <TD vAlign=center align=left width=216 height=1<B<FONT face="Microsoft Sans Serif" color=#ffffff size=1AGRIGENTO </FONT</B</TD <TD vAlign=center align=left width=98 height=6 <P align=left </P</TD <TD vAlign=center align=middle width=281 height=6 <P align=left<B<FONT face=Arial color=#ffffff size=20922 402506 - 402335 fax </FONT</B</P</TD <TD vAlign=center align=left width=105 height=6<FONT face="Microsoft Sans Serif" color=#ffffff size=2 <P align=center4755</FONT</P</TD</TR <TR <TD vAlign=center align=left width=310 colSpan=3 height=18 <P style="MARGIN-LEFT: 30px"<FONT face=Arial color=#00ffff size=1<A title="Risultato: Tutte le sedi" href="http://telefoni/TrovaDipendenze.asp?START=1&NPAG=1&VPARTENZA=1&VCE RCA=REGIONE SUD&TIPRIC=5"REGIONE SUD </A</FONT</P</TD <TD vAlign=center align=left width=346 colSpan=2 height=18<FONT face=Arial size=1<FONT color=#00ffff92100 AGRIGENTO - VIA IMERA, 203 </FONT</FONT</TD</TR</TBODY</TABLE</CENTER</DIV <P style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 15px" align=center <DIV align=center <CENTER <TABLE height=24 cellSpacing=0 cellPadding=0 width=731 background=trovadipendenze_file/sfondocell2.gif border=0 <TBODY <TR <TD vAlign=center align=left width=31 height=6 <P align=center<FONT face="MS Sans Serif" size=1</FONT </P</TD <TD vAlign=center align=left width=216 height=1<B<FONT face="Microsoft Sans Serif" color=#ffffff size=1ALBA </FONT</B</TD <TD vAlign=center align=left width=98 height=6 <P align=left </P</TD <TD vAlign=center align=middle width=281 height=6 <P align=left<B<FONT face=Arial color=#ffffff size=20173 362551 - 35461 fax</FONT</B</P</TD <TD vAlign=center align=left width=105 height=6<FONT face="Microsoft Sans Serif" color=#ffffff size=2 <P align=center2443</FONT</P</TD</TR <TR <TD vAlign=center align=left width=310 colSpan=3 height=18 <P style="MARGIN-LEFT: 30px"<FONT face=Arial color=#00ffff size=1<A title="Risultato: Tutte le sedi" href="http://telefoni/TrovaDipendenze.asp?START=1&NPAG=1&VPARTENZA=1&VCE RCA=REGIONE NORD-OVEST&TIPRIC=5"REGIONE NORD-OVEST</A</FONT </P</TD <TD vAlign=center align=left width=346 colSpan=2 height=18<FONT face=Arial size=1<FONT color=#00ffff12051 ALBA - CORSO LANGHE</FONT</FONT </TD</TR</TBODY</TABLE</CENTER</DIV <P style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 15px" align=center <DIV align=center <CENTER <TABLE height=24 cellSpacing=0 cellPadding=0 width=731 background=trovadipendenze_file/sfondocell2.gif border=0 <TBODY <TR <TD vAlign=center align=left width=31 height=6 <P align=center<FONT face="MS Sans Serif" size=1</FONT </P</TD <TD vAlign=center align=left width=216 height=1<B<FONT face="Microsoft Sans Serif" color=#ffffff size=1ALBANO LAZIALE </FONT</B</TD <TD vAlign=center align=left width=98 height=6 <P align=left </P</TD <TD vAlign=center align=middle width=281 height=6 <P align=left<B<FONT face=Arial color=#ffffff size=206 9324483/4 - 9324485 fax </FONT</B</P</TD <TD vAlign=center align=left width=105 height=6<FONT face="Microsoft Sans Serif" color=#ffffff size=2 <P align=center6337</FONT</P</TD</TR <TR <TD vAlign=center align=left width=310 colSpan=3 height=18 <P style="MARGIN-LEFT: 30px"<FONT face=Arial color=#00ffff size=1<A title="Risultato: Tutte le sedi" href="http://telefoni/TrovaDipendenze.asp?START=1&NPAG=1&VPARTENZA=1&VCE RCA=REGIONE LAZIO E SARDEGNA&TIPRIC=5"REGIONE LAZIO E SARDEGNA </A</FONT</P</TD <TD vAlign=center align=left width=346 colSpan=2 height=18<FONT face=Arial size=1<FONT color=#00ffff00041 ALBANO LAZIALE - VIA TORRIONE, 1 </FONT</FONT</TD</TR</TBODY</TABLE</CENTER</DIV <P style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 15px" align=center <DIV align=center <CENTER <TABLE height=24 cellSpacing=0 cellPadding=0 width=731 background=trovadipendenze_file/sfondocell2.gif border=0 <TBODY <TR <TD vAlign=center align=left width=31 height=6 <P align=center<FONT face="MS Sans Serif" size=1</FONT </P</TD <TD vAlign=center align=left width=216 height=1<B<FONT face="Microsoft Sans Serif" color=#ffffff size=1ALBENGA </FONT</B</TD <TD vAlign=center align=left width=98 height=6 <P align=left </P</TD <TD vAlign=center align=middle width=281 height=6 <P align=left<B<FONT face=Arial color=#ffffff size=20182 555318 - 555318 fax </FONT</B</P</TD <TD vAlign=center align=left width=105 height=6<FONT face="Microsoft Sans Serif" color=#ffffff size=2 <P align=center6746</FONT</P</TD</TR <TR <TD vAlign=center align=left width=310 colSpan=3 height=18 <P style="MARGIN-LEFT: 30px"<FONT face=Arial color=#00ffff size=1<A title="Risultato: Tutte le sedi" href="http://telefoni/TrovaDipendenze.asp?START=1&NPAG=1&VPARTENZA=1&VCE RCA=REGIONE. NORD-OVEST&TIPRIC=5"REGIONE. NORD-OVEST </A</FONT</P</TD <TD vAlign=center align=left width=346 colSpan=2 height=18<FONT face=Arial size=1<FONT color=#00ffff17031 ALBENGA - VIA TRIESTE, 49 </FONT</FONT</TD</TR</TBODY</TABLE</CENTER</DIV <P style="MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 15px" align=center<FONT face=Tahoma color=#00ffff size=1<BPagine : </B</FONT<FONT face=Tahoma color=#ffffff size=2(1) </FONT<FONT face=Tahoma color=#00ffff size=1<B<A href="http://telefoni/trovadipendenze.asp?start=7&npag=2&VPARTENZA=1&VCE RCA=&TIPRIC=" target=_self2</A </B</FONT <FONT face=Tahoma color=#00ffff size=1<B<A href="http://telefoni/trovadipendenze.asp?start=14&npag=3&VPARTENZA=1&VC ERCA=&TIPRIC=" target=_self3</A </B</FONT <FONT face=Tahoma color=#00ffff size=1<B<A href="http://telefoni/trovadipendenze.asp?start=21&npag=4&VPARTENZA=1&VC ERCA=&TIPRIC=" target=_self4</A </B</FONT <FONT face=Tahoma color=#00ffff size=1<B<A href="http://telefoni/trovadipendenze.asp?start=28&npag=5&VPARTENZA=1&VC ERCA=&TIPRIC=" target=_self5</A </B</FONT <FONT face=Tahoma color=#00ffff size=1<B<A href="http://telefoni/trovadipendenze.asp?start=35&npag=6&VPARTENZA=1&VC ERCA=&TIPRIC=" target=_self6</A </B</FONT <FONT face=Tahoma color=#00ffff size=1<B<A href="http://telefoni/trovadipendenze.asp?start=42&npag=7&VPARTENZA=1&VC ERCA=&TIPRIC=" target=_self7</A </B</FONT <FONT face=Tahoma color=#00ffff size=1<B<A href="http://telefoni/trovadipendenze.asp?start=49&npag=8&VPARTENZA=1&VC ERCA=&TIPRIC=" target=_self8</A </B</FONT <FONT face=Tahoma color=#00ffff size=1<B<A href="http://telefoni/trovadipendenze.asp?start=56&npag=9&VPARTENZA=1&VC ERCA=&TIPRIC=" target=_self9</A </B</FONT <FONT face=Tahoma color=#00ffff size=1<B<A href="http://telefoni/trovadipendenze.asp?start=63&npag=10&VPARTENZA=1&V CERCA=&TIPRIC=" target=_self10</A </B</FONT <FONT face=Tahoma color=#00ffff size=1<B<A href="http://telefoni/trovadipendenze.asp?start=70&npag=11&VPARTENZA=11& VCERCA=&TIPRIC=" target=_selfSuccessive</A </B</FONT </P</BODY</HTML |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find string in web page
On Fri, 7 Dec 2007 13:59:01 -0800, sal21
wrote: Now have a little other qst: I have this web page attached, i want to find the word "Trovati:" and get the value near tath in this case is: 827 , after store 827 in a cell of sheet. tath is all. Note:already have a VBA code to naviate in web page. I note that the string "Trovati:" is NOT in the web page you posted, although the string "Trovati" is present. In any event, the following function should look in Src for a phrase (str) and return the first word in the first phrase that is not enclosed in <.... In your example, if Src is your web page, and str is "Trovati", the function will return "827". I'm not sure if it will take into account all of the possible variations in context, so be sure to test it thoroughly. ======================================== Option Explicit Function GetDataAfter(Src As String, str As String) As String 'Src is the page being searched 'str is the string to be found within that page 'returns the first word in a string following str that is not within a tag Dim re As Object Dim mc As Object Dim sPat As String sPat = str & "[\s\S]*?(<[\s\S]*?)*(\S+)" Set re = CreateObject("vbscript.regexp") With re .Pattern = sPat .Global = True .ignorecase = True End With If re.test(Src) = True Then Set mc = re.Execute(Src) GetDataAfter = mc(0).submatches(1) End If End Function ============================== --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find string in web page
Hi Ron tks for reply....
But i am a newbie how can use the function? wath is the correct sintyax if i want use in my original code?How to store the "827" in a Var? Tks from Napoli. Note: Yes the word is "Trovati" and not "Trovati:", sorry me. "Ron Rosenfeld" wrote: On Fri, 7 Dec 2007 13:59:01 -0800, sal21 wrote: Now have a little other qst: I have this web page attached, i want to find the word "Trovati:" and get the value near tath in this case is: 827 , after store 827 in a cell of sheet. tath is all. Note:already have a VBA code to naviate in web page. I note that the string "Trovati:" is NOT in the web page you posted, although the string "Trovati" is present. In any event, the following function should look in Src for a phrase (str) and return the first word in the first phrase that is not enclosed in <.... In your example, if Src is your web page, and str is "Trovati", the function will return "827". I'm not sure if it will take into account all of the possible variations in context, so be sure to test it thoroughly. ======================================== Option Explicit Function GetDataAfter(Src As String, str As String) As String 'Src is the page being searched 'str is the string to be found within that page 'returns the first word in a string following str that is not within a tag Dim re As Object Dim mc As Object Dim sPat As String sPat = str & "[\s\S]*?(<[\s\S]*?)*(\S+)" Set re = CreateObject("vbscript.regexp") With re .Pattern = sPat .Global = True .ignorecase = True End With If re.test(Src) = True Then Set mc = re.Execute(Src) GetDataAfter = mc(0).submatches(1) End If End Function ============================== --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find string in web page
On Sat, 8 Dec 2007 10:17:00 -0800, sal21
wrote: Hi Ron tks for reply.... But i am a newbie how can use the function? wath is the correct sintyax if i want use in my original code?How to store the "827" in a Var? Tks from Napoli. Note: Yes the word is "Trovati" and not "Trovati:", sorry me. You can store the function in the same module as the one you are using to manipulate the web page. Then you can just use the function from your routine that is storing the web page. So in your code you might have something like this: ======================= Dim sVar as string sVar = GetDataAfter(Src, "Trovati") 'where Src is your web page ============================ --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find String in another string - only between spaces | Excel Worksheet Functions | |||
Find Many String options in ONE String | Excel Worksheet Functions | |||
search a string withing a string : find / search hangs | Excel Programming | |||
backwards find function to find character in a string of text | Excel Programming | |||
find a string inside another string | Excel Programming |