Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
parsing Html page
Hi.
I need to get data from a web page from Excel (no query web). I need to put in a variable (es. mycode) all the html code, afterwards I will parse the string "mycode". I call in my sub a function DownloadPage (API) found in KPD-Team 1999 (see code below). It works but sometime the page processed is not completly loaded, so I lost some data. I am not expert, so I ask if there is the possibility to put in a string the html code with another method. Thanks M. ------------------------------------------- Option Explicit Const INTERNET_OPEN_TYPE_DIRECT = 1 Const INTERNET_OPEN_TYPE_PROXY = 3 Const INTERNET_FLAG_RELOAD = &H80000000 Private Declare Function InternetOpen Lib "wininet" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long Private Declare Function InternetCloseHandle Lib "wininet" (ByVal hInet As Long) As Integer Private Declare Function InternetReadFile Lib "wininet" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer Private Declare Function InternetOpenUrl Lib "wininet" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal lpszUrl As String, ByVal lpszHeaders As String, ByVal dwHeadersLength As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long Private Declare Function InternetAttemptConnect Lib "wininet" (ByVal dwReserved As Long) As Long Function DownloadPage(sURL As String, Optional Length As Long) As String 'this sub written by 'KPD-Team 1999 'URL: http://www.allapi.net/ 'E-Mail: Dim hOpen As Long, hFile As Long, sBuffer As String, Ret As Long 'Create a buffer for the file we're going to download If Length = 0 Then Length = 10000000 sBuffer = Space(Length) 'Create an internet connection hOpen = InternetOpen("", INTERNET_OPEN_TYPE_DIRECT, vbNullString, vbNullString, 0) 'Open the url hFile = InternetOpenUrl(hOpen, sURL, "", 0, INTERNET_FLAG_RELOAD, ByVal 0&) 'Read the first 10000 bytes of the file InternetReadFile hFile, sBuffer, Length, Ret 'clean up InternetCloseHandle hFile InternetCloseHandle hOpen 'Show our file DownloadPage = Left$(sBuffer, Ret) End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
parsing Html page
http://groups.google.com/group/micro...UTF-8&oe=UTF-8
has some suggestions. -- Regards, Tom Ogilvy "mino" wrote: Hi. I need to get data from a web page from Excel (no query web). I need to put in a variable (es. mycode) all the html code, afterwards I will parse the string "mycode". I call in my sub a function DownloadPage (API) found in KPD-Team 1999 (see code below). It works but sometime the page processed is not completly loaded, so I lost some data. I am not expert, so I ask if there is the possibility to put in a string the html code with another method. Thanks M. ------------------------------------------- Option Explicit Const INTERNET_OPEN_TYPE_DIRECT = 1 Const INTERNET_OPEN_TYPE_PROXY = 3 Const INTERNET_FLAG_RELOAD = &H80000000 Private Declare Function InternetOpen Lib "wininet" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long Private Declare Function InternetCloseHandle Lib "wininet" (ByVal hInet As Long) As Integer Private Declare Function InternetReadFile Lib "wininet" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer Private Declare Function InternetOpenUrl Lib "wininet" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal lpszUrl As String, ByVal lpszHeaders As String, ByVal dwHeadersLength As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long Private Declare Function InternetAttemptConnect Lib "wininet" (ByVal dwReserved As Long) As Long Function DownloadPage(sURL As String, Optional Length As Long) As String 'this sub written by 'KPD-Team 1999 'URL: http://www.allapi.net/ 'E-Mail: Dim hOpen As Long, hFile As Long, sBuffer As String, Ret As Long 'Create a buffer for the file we're going to download If Length = 0 Then Length = 10000000 sBuffer = Space(Length) 'Create an internet connection hOpen = InternetOpen("", INTERNET_OPEN_TYPE_DIRECT, vbNullString, vbNullString, 0) 'Open the url hFile = InternetOpenUrl(hOpen, sURL, "", 0, INTERNET_FLAG_RELOAD, ByVal 0&) 'Read the first 10000 bytes of the file InternetReadFile hFile, sBuffer, Length, Ret 'clean up InternetCloseHandle hFile InternetCloseHandle hOpen 'Show our file DownloadPage = Left$(sBuffer, Ret) End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
parsing Html page
Thanks, I will analyze it.
To use Visual studio object in Vb-Excel 2003 where can I find the add in for excel (reference)? thanks M. "Tom Ogilvy" ha scritto nel messaggio ... http://groups.google.com/group/micro...UTF-8&oe=UTF-8 has some suggestions. -- Regards, Tom Ogilvy "mino" wrote: Hi. I need to get data from a web page from Excel (no query web). I need to put in a variable (es. mycode) all the html code, afterwards I will parse the string "mycode". I call in my sub a function DownloadPage (API) found in KPD-Team 1999 (see code below). It works but sometime the page processed is not completly loaded, so I lost some data. I am not expert, so I ask if there is the possibility to put in a string the html code with another method. Thanks M. ------------------------------------------- Option Explicit Const INTERNET_OPEN_TYPE_DIRECT = 1 Const INTERNET_OPEN_TYPE_PROXY = 3 Const INTERNET_FLAG_RELOAD = &H80000000 Private Declare Function InternetOpen Lib "wininet" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long Private Declare Function InternetCloseHandle Lib "wininet" (ByVal hInet As Long) As Integer Private Declare Function InternetReadFile Lib "wininet" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer Private Declare Function InternetOpenUrl Lib "wininet" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal lpszUrl As String, ByVal lpszHeaders As String, ByVal dwHeadersLength As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long Private Declare Function InternetAttemptConnect Lib "wininet" (ByVal dwReserved As Long) As Long Function DownloadPage(sURL As String, Optional Length As Long) As String 'this sub written by 'KPD-Team 1999 'URL: http://www.allapi.net/ 'E-Mail: Dim hOpen As Long, hFile As Long, sBuffer As String, Ret As Long 'Create a buffer for the file we're going to download If Length = 0 Then Length = 10000000 sBuffer = Space(Length) 'Create an internet connection hOpen = InternetOpen("", INTERNET_OPEN_TYPE_DIRECT, vbNullString, vbNullString, 0) 'Open the url hFile = InternetOpenUrl(hOpen, sURL, "", 0, INTERNET_FLAG_RELOAD, ByVal 0&) 'Read the first 10000 bytes of the file InternetReadFile hFile, sBuffer, Length, Ret 'clean up InternetCloseHandle hFile InternetCloseHandle hOpen 'Show our file DownloadPage = Left$(sBuffer, Ret) End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
parsing Html page
I have no idea what you are asking for. Sorry.
-- Regards, Tom Ogilvy "mino" wrote: Thanks, I will analyze it. To use Visual studio object in Vb-Excel 2003 where can I find the add in for excel (reference)? thanks M. "Tom Ogilvy" ha scritto nel messaggio ... http://groups.google.com/group/micro...UTF-8&oe=UTF-8 has some suggestions. -- Regards, Tom Ogilvy "mino" wrote: Hi. I need to get data from a web page from Excel (no query web). I need to put in a variable (es. mycode) all the html code, afterwards I will parse the string "mycode". I call in my sub a function DownloadPage (API) found in KPD-Team 1999 (see code below). It works but sometime the page processed is not completly loaded, so I lost some data. I am not expert, so I ask if there is the possibility to put in a string the html code with another method. Thanks M. ------------------------------------------- Option Explicit Const INTERNET_OPEN_TYPE_DIRECT = 1 Const INTERNET_OPEN_TYPE_PROXY = 3 Const INTERNET_FLAG_RELOAD = &H80000000 Private Declare Function InternetOpen Lib "wininet" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long Private Declare Function InternetCloseHandle Lib "wininet" (ByVal hInet As Long) As Integer Private Declare Function InternetReadFile Lib "wininet" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer Private Declare Function InternetOpenUrl Lib "wininet" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal lpszUrl As String, ByVal lpszHeaders As String, ByVal dwHeadersLength As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long Private Declare Function InternetAttemptConnect Lib "wininet" (ByVal dwReserved As Long) As Long Function DownloadPage(sURL As String, Optional Length As Long) As String 'this sub written by 'KPD-Team 1999 'URL: http://www.allapi.net/ 'E-Mail: Dim hOpen As Long, hFile As Long, sBuffer As String, Ret As Long 'Create a buffer for the file we're going to download If Length = 0 Then Length = 10000000 sBuffer = Space(Length) 'Create an internet connection hOpen = InternetOpen("", INTERNET_OPEN_TYPE_DIRECT, vbNullString, vbNullString, 0) 'Open the url hFile = InternetOpenUrl(hOpen, sURL, "", 0, INTERNET_FLAG_RELOAD, ByVal 0&) 'Read the first 10000 bytes of the file InternetReadFile hFile, sBuffer, Length, Ret 'clean up InternetCloseHandle hFile InternetCloseHandle hOpen 'Show our file DownloadPage = Left$(sBuffer, Ret) End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
parsing Html page
Give this a try.
' /////////////////////////////// Sub ViewSource() Dim strURL As String Dim strPageHTML As String strURL = "http://www.google.com/" On Error Resume Next Set objHTTP = CreateObject("MSXML2.XMLHTTP") objHTTP.Open "GET", strURL, False objHTTP.Send strPageHTML = objHTTP.ResponseText MsgBox strPageHTML Set objHTTP = Nothing End Sub ' ////////////////////////////// Steve Yandl "mino" wrote in message ... Hi. I need to get data from a web page from Excel (no query web). I need to put in a variable (es. mycode) all the html code, afterwards I will parse the string "mycode". I call in my sub a function DownloadPage (API) found in KPD-Team 1999 (see code below). It works but sometime the page processed is not completly loaded, so I lost some data. I am not expert, so I ask if there is the possibility to put in a string the html code with another method. Thanks M. ------------------------------------------- Option Explicit Const INTERNET_OPEN_TYPE_DIRECT = 1 Const INTERNET_OPEN_TYPE_PROXY = 3 Const INTERNET_FLAG_RELOAD = &H80000000 Private Declare Function InternetOpen Lib "wininet" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long Private Declare Function InternetCloseHandle Lib "wininet" (ByVal hInet As Long) As Integer Private Declare Function InternetReadFile Lib "wininet" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer Private Declare Function InternetOpenUrl Lib "wininet" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal lpszUrl As String, ByVal lpszHeaders As String, ByVal dwHeadersLength As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long Private Declare Function InternetAttemptConnect Lib "wininet" (ByVal dwReserved As Long) As Long Function DownloadPage(sURL As String, Optional Length As Long) As String 'this sub written by 'KPD-Team 1999 'URL: http://www.allapi.net/ 'E-Mail: Dim hOpen As Long, hFile As Long, sBuffer As String, Ret As Long 'Create a buffer for the file we're going to download If Length = 0 Then Length = 10000000 sBuffer = Space(Length) 'Create an internet connection hOpen = InternetOpen("", INTERNET_OPEN_TYPE_DIRECT, vbNullString, vbNullString, 0) 'Open the url hFile = InternetOpenUrl(hOpen, sURL, "", 0, INTERNET_FLAG_RELOAD, ByVal 0&) 'Read the first 10000 bytes of the file InternetReadFile hFile, sBuffer, Length, Ret 'clean up InternetCloseHandle hFile InternetCloseHandle hOpen 'Show our file DownloadPage = Left$(sBuffer, Ret) End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
parsing Html page
Many Thanks STEVE & TOM!
I solved! "Steve Yandl" ha scritto nel messaggio . .. Give this a try. ' /////////////////////////////// Sub ViewSource() Dim strURL As String Dim strPageHTML As String strURL = "http://www.google.com/" On Error Resume Next Set objHTTP = CreateObject("MSXML2.XMLHTTP") objHTTP.Open "GET", strURL, False objHTTP.Send strPageHTML = objHTTP.ResponseText MsgBox strPageHTML Set objHTTP = Nothing End Sub ' ////////////////////////////// Steve Yandl "mino" wrote in message ... Hi. I need to get data from a web page from Excel (no query web). I need to put in a variable (es. mycode) all the html code, afterwards I will parse the string "mycode". I call in my sub a function DownloadPage (API) found in KPD-Team 1999 (see code below). It works but sometime the page processed is not completly loaded, so I lost some data. I am not expert, so I ask if there is the possibility to put in a string the html code with another method. Thanks M. ------------------------------------------- Option Explicit Const INTERNET_OPEN_TYPE_DIRECT = 1 Const INTERNET_OPEN_TYPE_PROXY = 3 Const INTERNET_FLAG_RELOAD = &H80000000 Private Declare Function InternetOpen Lib "wininet" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long Private Declare Function InternetCloseHandle Lib "wininet" (ByVal hInet As Long) As Integer Private Declare Function InternetReadFile Lib "wininet" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer Private Declare Function InternetOpenUrl Lib "wininet" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal lpszUrl As String, ByVal lpszHeaders As String, ByVal dwHeadersLength As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long Private Declare Function InternetAttemptConnect Lib "wininet" (ByVal dwReserved As Long) As Long Function DownloadPage(sURL As String, Optional Length As Long) As String 'this sub written by 'KPD-Team 1999 'URL: http://www.allapi.net/ 'E-Mail: Dim hOpen As Long, hFile As Long, sBuffer As String, Ret As Long 'Create a buffer for the file we're going to download If Length = 0 Then Length = 10000000 sBuffer = Space(Length) 'Create an internet connection hOpen = InternetOpen("", INTERNET_OPEN_TYPE_DIRECT, vbNullString, vbNullString, 0) 'Open the url hFile = InternetOpenUrl(hOpen, sURL, "", 0, INTERNET_FLAG_RELOAD, ByVal 0&) 'Read the first 10000 bytes of the file InternetReadFile hFile, sBuffer, Length, Ret 'clean up InternetCloseHandle hFile InternetCloseHandle hOpen 'Show our file DownloadPage = Left$(sBuffer, Ret) End Function |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
parsing Html page
Sorry Tom I am not expert.
I mean if it is possible to use in Vba-Excel object like WebClient (see below visual studio code) and if yes, how do it. Thanks M. [...] URL = http://www.pag............." client = New WebClient() data = client.OpenRead(URL) reader = New StreamReader(data) str = UCase(reader.ReadToEnd) reader.Close() data.Close() str = str.Replace(Chr(10), String.Empty) [...] "Tom Ogilvy" ha scritto nel messaggio ... I have no idea what you are asking for. Sorry. -- Regards, Tom Ogilvy "mino" wrote: Thanks, I will analyze it. To use Visual studio object in Vb-Excel 2003 where can I find the add in for excel (reference)? thanks M. "Tom Ogilvy" ha scritto nel messaggio ... http://groups.google.com/group/micro...UTF-8&oe=UTF-8 has some suggestions. -- Regards, Tom Ogilvy "mino" wrote: Hi. I need to get data from a web page from Excel (no query web). I need to put in a variable (es. mycode) all the html code, afterwards I will parse the string "mycode". I call in my sub a function DownloadPage (API) found in KPD-Team 1999 (see code below). It works but sometime the page processed is not completly loaded, so I lost some data. I am not expert, so I ask if there is the possibility to put in a string the html code with another method. Thanks M. ------------------------------------------- Option Explicit Const INTERNET_OPEN_TYPE_DIRECT = 1 Const INTERNET_OPEN_TYPE_PROXY = 3 Const INTERNET_FLAG_RELOAD = &H80000000 Private Declare Function InternetOpen Lib "wininet" Alias "InternetOpenA" (ByVal sAgent As String, ByVal lAccessType As Long, ByVal sProxyName As String, ByVal sProxyBypass As String, ByVal lFlags As Long) As Long Private Declare Function InternetCloseHandle Lib "wininet" (ByVal hInet As Long) As Integer Private Declare Function InternetReadFile Lib "wininet" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer Private Declare Function InternetOpenUrl Lib "wininet" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal lpszUrl As String, ByVal lpszHeaders As String, ByVal dwHeadersLength As Long, ByVal dwFlags As Long, ByVal dwContext As Long) As Long Private Declare Function InternetAttemptConnect Lib "wininet" (ByVal dwReserved As Long) As Long Function DownloadPage(sURL As String, Optional Length As Long) As String 'this sub written by 'KPD-Team 1999 'URL: http://www.allapi.net/ 'E-Mail: Dim hOpen As Long, hFile As Long, sBuffer As String, Ret As Long 'Create a buffer for the file we're going to download If Length = 0 Then Length = 10000000 sBuffer = Space(Length) 'Create an internet connection hOpen = InternetOpen("", INTERNET_OPEN_TYPE_DIRECT, vbNullString, vbNullString, 0) 'Open the url hFile = InternetOpenUrl(hOpen, sURL, "", 0, INTERNET_FLAG_RELOAD, ByVal 0&) 'Read the first 10000 bytes of the file InternetReadFile hFile, sBuffer, Length, Ret 'clean up InternetCloseHandle hFile InternetCloseHandle hOpen 'Show our file DownloadPage = Left$(sBuffer, Ret) End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tweaking to pull a local HTML page instead of a server page? | Excel Programming | |||
Excel & HTML Parsing | Excel Discussion (Misc queries) | |||
HTML parsing with VBA | Excel Programming | |||
parsing HTML table into excel. How? | Excel Programming | |||
Parsing Data From HTML Source into Excel using VBA | Excel Programming |