Casey_2
07-01-2010, 10:23 AM
Is there a way to send a HTTP request with a custom header in VBA?
While this is quite straightforward in VB.Net:
Dim SR As StreamReader: Dim reqr As HttpWebResponse: Dim req As HttpWebRequest = DirectCast(HttpWebRequest.Create("http://www.google.com/"), HttpWebRequest)
req.Method = "GET"
req.Headers.Add("Accept-Language", "en-gb,en-us")
reqr = req.GetResponse
SR = New StreamReader(reqr.GetResponseStream)
I couldn't figure out how to do it in VBA.
Actually the VBA routine needs to be quite fast and I'm currently using the following VBA code:
Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szUrl As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Sub DownloadFile(ByVal TheURL As String)
Dim TMPStr As String, Value As Long
Dim FSO As Object, FSOStream As Object
TheFile = "C:\TMP.txt"
If Dir$(TheFile) <> "" Then Kill TheFile
Value = URLDownloadToFile(0, TheURL, TheFile, 0, 0)
Set FSO = CreateObject("Scripting.FileSystemObject"): Set FSO = FSO.GetFile(TheFile)
Set FSOStream = FSO.OpenAsTextStream
TMPStr = FSOStream.readall
FSOStream.Close: Set FSOStream = Nothing: Set FSO = Nothing
Kill TheFile
'... etc.
End Sub
But I have no idea on how to enable custom headers.
Googling didn't help either (all references relate to VB.Net and not VBA).
Should be compatible with Excel 2003 and above.
While this is quite straightforward in VB.Net:
Dim SR As StreamReader: Dim reqr As HttpWebResponse: Dim req As HttpWebRequest = DirectCast(HttpWebRequest.Create("http://www.google.com/"), HttpWebRequest)
req.Method = "GET"
req.Headers.Add("Accept-Language", "en-gb,en-us")
reqr = req.GetResponse
SR = New StreamReader(reqr.GetResponseStream)
I couldn't figure out how to do it in VBA.
Actually the VBA routine needs to be quite fast and I'm currently using the following VBA code:
Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szUrl As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
Sub DownloadFile(ByVal TheURL As String)
Dim TMPStr As String, Value As Long
Dim FSO As Object, FSOStream As Object
TheFile = "C:\TMP.txt"
If Dir$(TheFile) <> "" Then Kill TheFile
Value = URLDownloadToFile(0, TheURL, TheFile, 0, 0)
Set FSO = CreateObject("Scripting.FileSystemObject"): Set FSO = FSO.GetFile(TheFile)
Set FSOStream = FSO.OpenAsTextStream
TMPStr = FSOStream.readall
FSOStream.Close: Set FSOStream = Nothing: Set FSO = Nothing
Kill TheFile
'... etc.
End Sub
But I have no idea on how to enable custom headers.
Googling didn't help either (all references relate to VB.Net and not VBA).
Should be compatible with Excel 2003 and above.