vba to send http request

[overbet]

I need to send multiple http request using vba. i have searched the forums but cant find any info on this subject. Is anyone here familiar with this process? I currently have a piece of code I am using to accomplish this but it is painfully slow. It does only about 1 url a second. I was hoping to improve this by at least 5 times that amount. I can post the code im using if that might help or if someone could point me in the right direction i would be grateful. ty

 

[kpark91]

Putting the code up would help.

 

[overbet]

Dim http As New WinHttp.WinHttpRequest
Public RunWhen As Double
Public NoMatch As Boolean
Public Timer1 As Long

Option Explicit

Private Declare Function InternetOpen Lib "wininet.dll" 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 InternetOpenUrl Lib "wininet.dll" Alias "InternetOpenUrlA" (ByVal hInternetSession As Long, ByVal sURL As String, ByVal sHeaders As String, ByVal lHeadersLength As Long, ByVal lFlags As Long, ByVal lContext As Long) As Long
Private Declare Function InternetReadFile Lib "wininet.dll" (ByVal hFile As Long, ByVal sBuffer As String, ByVal lNumBytesToRead As Long, lNumberOfBytesRead As Long) As Integer
Private Declare Function InternetCloseHandle Lib "wininet.dll" (ByVal hInet As Long) As Integer

Private Const IF_FROM_CACHE = &H1000000
Private Const IF_MAKE_PERSISTENT = &H2000000
Private Const IF_NO_CACHE_WRITE = &H4000000

Private Const BUFFER_LEN = 256
Dim source As String

Public Function GetURLSource(sURL As String)
On Error GoTo ErrorHandler

Dim sBuffer As String * BUFFER_LEN, iResult As Integer, sData As String
Dim hInternet As Long, hSession As Long, lReturn As Long

hSession = InternetOpen("vb wininet", 1, vbNullString, vbNullString, 0)
If hSession Then hInternet = InternetOpenUrl(hSession, sURL, vbNullString, 0, IF_NO_CACHE_WRITE, 0)

If hInternet Then
iResult = InternetReadFile(hInternet, sBuffer, BUFFER_LEN, lReturn)
sData = sBuffer
Do While lReturn <> 0
iResult = InternetReadFile(hInternet, sBuffer, BUFFER_LEN, lReturn)
sData = sData + Mid(sBuffer, 1, lReturn)
Loop
End If

iResult = InternetCloseHandle(hInternet)

GetURLSource = sData

Exit Function
ErrorHandler:
Resume Next
End Function


Function HttpExists(sURL As String) As Boolean
On Error GoTo HttpError
http.Open "GET", sURL
http.Send
HttpExists = (http.Status = 200)
Exit Function
HttpError:
HttpExists = False
Resume Next
End Function

 

[overbet]

Can anyone offer any assistance? tks

 

[overbet]

I realize this board gets a lot of questions so I would really appreciate any input from those that are knowledgeable in the this area. I need to make this program fun faster one way or the other even if it requires having someone write it in another language. I would highly prefer to keep it in vb. Pretty pretty pretty please give me some feedback. thank you much

 

[kpark91]

I'm afraid I cannot help you. You can try looking at this thread and see if it helps:
http://www.mrexcel.com/forum/showthread.php?t=352868

Also, you might want to consider looking into WinHTTP.WinHTTPrequest.5 or WinHTTP.WinHTTPrequest.5.1 to send http request using IE.

Moreover, make sure your firewall is turned off when you're running the code.

 

[Ruddles]

I need to send multiple http request using vba... I currently have a piece of code I am using to accomplish this but it is painfully slow. It does only about 1 url a second. I was hoping to improve this by at least 5 times that amount.

What basis do you have for believing that you can generate the request and have the remote end respond with that sort of speed? You're reliant on network response times and the speed with which the far end can reply to your request, aren't you?

If I'm reading your code correctly and you're actually transferring files, I think one per second is quite impressive!

 

[xenou]

^^ that was my first thought too. Whether you can achieve 5 requests per second will depend on what you are doing, network conditions, as well as the code you use.

 

[Darkspartan]

What basis do you have for believing that you can generate the request and have the remote end respond with that sort of speed? You're reliant on network response times and the speed with which the far end can reply to your request, aren't you?

If I'm reading your code correctly and you're actually transferring files, I think one per second is quite impressive!

I agree with this. This has been my greatest hinderance when working with webpages at work. The speed of your macro is dependent on how fast the web page loads.

To try an assist however, is use this code to wait for a webpage to load

While IE.Busy
DoEvents
Wend

This will let your maro rest for however milliseconds you want

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sleep 25

 

[overbet]

Thanks for the replies. I realize that most of my wait time will be reliant on recieving a response from their server but I have the slower servers seperated from the faster ones as far as response time goes and I meant 5 per second for the faster ones. I read serveral posts online while searching that led me to believe much faster speeds were possible such as this post in the link below. Am I misunderstand this or are they saying 63 request per second is possible. On my end the network speeds are fast. I believe its a T3 connection at work. Thanks again.

"It takes about 16 seconds to do 1000 regular GETs on google.com, about 63 requests per second." see link

http://stackoverflow.com/questions/1272750/send-http-request-as-fast-as-possible