ExcelBanter - How do I use WinHttp within Excel 2003 Macro?

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - How do I use WinHttp within Excel 2003 Macro? (https://www.excelbanter.com/excel-programming/412716-how-do-i-use-winhttp-within-excel-2003-macro.html)

How do I use WinHttp within Excel 2003 Macro?

Hi all, I want to get web content into Excel and store it to variables (not
in the Cells), how can I do it? I know WinHttp can help but I do not know how
to 'include' WinHttp in Excel. Please help.

How do I use WinHttp within Excel 2003 Macro?

Within the VBA project, you need to use menu option Tools
References, then add the XMLHTTP reference library (Microsoft XML, x.
0).

On Jun 17, 3:47 am, Mak wrote:
Hi all, I want to get web content into Excel and store it to variables (not
in the Cells), how can I do it? I know WinHttp can help but I do not know how
to 'include' WinHttp in Excel. Please help.

How do I use WinHttp within Excel 2003 Macro?

Hi all,
Pls find the code:

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

Sub Fetch()
Dim tTxt As String

tTxt = DownloadPage(Range("URL"))

End Sub

Function DownloadPage(sURL As String, Optional Length As Long) As String

Dim hOpen As Long, hFile As Long, sBuffer As String, Ret As Long

If Length = 0 Then Length = 10000

sBuffer = Space(Length)
hOpen = InternetOpen("", INTERNET_OPEN_TYPE_DIRECT, vbNullString,
vbNullString, 0)
hFile = InternetOpenUrl(hOpen, sURL, "", 0, INTERNET_FLAG_RELOAD, ByVal
0&)
InternetReadFile hFile, sBuffer, Length, Ret
InternetCloseHandle hFile
InternetCloseHandle hOpen
DownloadPage = Left$(sBuffer, Ret)

End Function

"Don Guillett" wrote:

The archives would appreciate your final solution to be posted

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mak" wrote in message
...
Don,
I have made use of Windows API and it works now. Thanks for your help
anyway.
Mak.

"Don Guillett" wrote:

What you want is easy to do. Post your code and/or send your workbook to
my
address below.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mak" wrote in message
...
I have downloaded some files, for those VBA that I can view, they are
using
QueryTable with Destination = a cell or range. But I want to store the
web
content in variables first. Any hints?

"Don Guillett" wrote:

Just send the email
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mak" wrote in message
...
Could you pls guide me how to access

step
by step? Thanks in advance.

"Don Guillett" wrote:

goto

and look in the files section. There are several free files.
I have a couple of free files under author donalb36

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mak" wrote in message
...
Hi all, I want to get web content into Excel and store it to
variables
(not
in the Cells), how can I do it? I know WinHttp can help but I do
not
know
how
to 'include' WinHttp in Excel. Please help.

How do I use WinHttp within Excel 2003 Macro?

On Jul 19, 3:53*pm, Mak wrote:
Hi all,
Pls find the code:

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

Sub Fetch()
Dim tTxt As String

tTxt = DownloadPage(Range("URL"))

End Sub

Function DownloadPage(sURL As String, Optional Length As Long) As String

* * Dim hOpen As Long, hFile As Long, sBuffer As String, Ret As Long

* * If Length = 0 Then Length = 10000

* * sBuffer = Space(Length)
* * hOpen = InternetOpen("", INTERNET_OPEN_TYPE_DIRECT, vbNullString,
vbNullString, 0)
* * hFile = InternetOpenUrl(hOpen, sURL, "", 0, INTERNET_FLAG_RELOAD, ByVal
0&)
* * InternetReadFile hFile, sBuffer, Length, Ret
* * InternetCloseHandle hFile
* * InternetCloseHandle hOpen
* * DownloadPage = Left$(sBuffer, Ret)

End Function

"Don Guillett" wrote:
The archives would appreciate your final solution to be posted

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mak" wrote in message
...
Don,
I have made use of Windows API and it works now. *Thanks for your help
anyway.
Mak.

"Don Guillett" wrote:

What you want is easy to do. Post your code and/or send your workbook to
my
address below.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mak" wrote in message
...
I have downloaded some files, for those VBA that I can view, they are
using
QueryTable with Destination = a cell or range. *But I want to store the
web
content in variables first. *Any hints?

"Don Guillett" wrote:

Just send the email
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mak" wrote in message
...
Could you pls guide me how to access

step
by step? *Thanks in advance.

"Don Guillett" wrote:

* * * goto
* * *
* * * and look in the files section. There are several free files.
* * * I have a couple of free files under author donalb36

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mak" wrote in message
...
Hi all, I want to get web content into Excel and store it to
variables
(not
in the Cells), how can I do it? I know WinHttp can help but I do
not
know
how
to 'include' WinHttp in Excel. *Please help.- Hide quoted text -

- Show quoted text -

HI,
I have tried above code and function but it fatches nothing.
No errors but debug.pring is nothing.

Any idea?
It might be quite usefull to me.

Regards,
Madiya