Re: Capture request/response HTTP headers with VBA

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


Hi again

and thanks again

ok, well, you make it look so easy :)

i must be doing somethng wrong here.
This is what i've got:

[code]
Function GetHeader(sURL As String) As String
Dim oXHTTP As Object
Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
oXHTTP.Open "HEAD", sURL, False
oXHTTP.send
GetHeader = oXHTTP.getAllResponseHeaders()
Dim c As Range
End Function
'----------------------------------------------------------
Sub test()
Set ie = CreateObject("InternetExplorer.Application")
With ie
..Visible = True
..Navigate "http://my.msn.com";
Do Until .ReadyState = 4: DoEvents: Loop
Do While ie.busy: DoEvents: Loop
End With
For Each c In ThisWorkbook.Sheets("Sheet1").Range("A2:A50")
If c.Value <> "" Then c.Offset(0, 1).Value = GetHeader(c.Value)
Next c
End Sub

[/code]

the range A2:A50 is still empty.

basically, and i did not make it that clear, what i am looking for is a
routine that will record all the header action, as it occurs during
navigation.

for example, if i navigate to http://my.msn.com, just typing that one URL in
my address bar and hitting enter on my keyboard, i get all this header
activity (courtesy of DebugBar 4.2)

---------------------------------
GET / HTTP/1.1
Accept: image/gif, image/x-xbitmap, image/jpeg, image/pjpeg,
application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword,
application/x-shockwave-flash, */*
Referer: http://www.msn.com/
Accept-Language: en-us
Accept-Encoding: gzip, deflate
User-Agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)
Host: my.msn.com
Connection: Keep-Alive
Cookie: MC1=V=3&GUID=1b9fd9574b9a4952a6148750072c065c; mh=MSFT;
CULTURE=EN-US; ushpsvr=M:5|F:5|T:5|E:5|D:blu|W:F;
ushpcli=0|H.0.1|G.0.1|Z.0.1|R.0.1.cap|C.0.1.lg:newyorkny|L.0.1.LN:WNBC;
ushpwea=wc:USNY0996; ushppr=H:1:070308

HTTP/1.1 302 Found
nnCoection: close
Date: Sat, 10 Mar 2007 01:04:29 GMT
Server: Microsoft-IIS/6.0
X-Powered-By: ASP.NET
P3P:CP="BUS CUR CONo FIN IVDo ONL OUR PHY SAMo TELo"
X-AspNet-Version: 1.1.4322
Location:
http://login.live.com/login.srf?lc=1033&id=6528&ru=http%3a%2f%2fmy.msn.com%2f&tw=14400&kv=9&ct=1173488669&cb=SiteID=msft&msppjph=1&ver=2.1.6000.1&rn=6Tw2AA7i&tpf=9bdebc94d476ff79d7a26ff9ace50a14
Cache-Control: no-cache
Pragma: no-cache
Expires: -1
Content-Type: text/html; charset=utf-8
Content-Length: 351

GET
/login.srf?lc=1033&id=6528&ru=http%3a%2f%2fmy.msn.com%2f&tw=14400&kv=9&ct=1173488669&cb=SiteID=msft&msppjph=1&ver=2.1.6000.1&rn=6Tw2AA7i&tpf=9bdebc94d476ff79d7a26ff9ace50a14 HTTP/1.1
Accept: image/gif, image/x-xbitmap, image/jpeg, image/pjpeg,
application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword,
application/x-shockwave-flash, */*
Referer: http://www.msn.com/
Accept-Language: en-us
Accept-Encoding: gzip, deflate
User-Agent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)
Host: login.live.com
Connection: Keep-Alive

HTTP/1.1 200 OK
Cache-Control: no-cache
Connection: close
Date: Sat, 10 Mar 2007 01:04:30 GMT
Pragma: no-cache
Transfer-Encoding: chunked
Content-Type: text/html; charset=iso-8859-1
Content-Encoding: gzip
Expires: Sat, 10 Mar 2007 01:03:30 GMT
Vary: Accept-Encoding
Server: Microsoft-IIS/6.0
PPServer: PPV: 30 H: BAYPPLOG2B05 V: 0
X-Powered-By: ASP.NET
P3P: CP="DSP CUR OTPi IND OTRi ONL FIN"
Set-Cookie: MSPRequ=lt=1173488670&co=1&id=6528; path=/;version=1
Set-Cookie: MSPOK=uuid-79e09a36-40d4-4158-a4f1-3e859545e560;
domain=login.live.com;path=/;version=1

--------------------------------------------------


so what i am trying to do is have my vba routine navigate to, in this
example, my.msn.com, and then some how be picking up all that header detail
and either placing it on the spreadsheet or sending it to a tet file for
future analysis.

so conceivably the code would look something like this:

[code]

Function GetHeader(sURL As String) As String
Dim oXHTTP As Object
Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
oXHTTP.Open "HEAD", sURL, False
oXHTTP.send
GetHeader = oXHTTP.getAllResponseHeaders()
Dim c As Range
End Function
'----------------------------------------------------------
Sub test()

'begin recording all headers and write to spreadsheeet or text file

Set ie = CreateObject("InternetExplorer.Application")
With ie
..Visible = True
..Navigate "http://my.msn.com";
Do Until .ReadyState = 4: DoEvents: Loop
Do While ie.busy: DoEvents: Loop
End With
' stop recording of headers

End Sub

[/code]

so when i go to take a look at all the headers recorded in the process, i
finally can get to the text string i need.

thanks again



.

Relevant Pages

  • Re: Is it possible for me to have an alert pop-up when I open a do
    ... them and clean up the whole header. ... Dim TheWeekOfStr As String ... After I enabled macros and changed the security level, as per Dave Peterson, ... I got almost what I wanted, except that the pop-up box contains the font ...
    (microsoft.public.excel.misc)
  • Re: Zero terminated strings
    ... For doing major string handling you want a language where strings are a ... Check the header bytes as they are coming in and assume that you have dropped at least one byte if any of them are wrong. ... When you have a complete valid message (at the final destination, however many hops down the line it is) the final destination acknowledges it, and keeps sending ACKs for it until it receives the next message. ... The sender keeps trying to send it until it gets the acknowledgment. ...
    (comp.lang.c)
  • Re: swapping commas for spaces in a string..
    ... don't use a second string ssvstr. ... Valid value, not part of a header, thus backtrack one record ... infile comes from the command line. ...
    (comp.lang.fortran)
  • Re: Compile Error (ADO) Ron De Bruin
    ... Public Sub GetData(SourceFile As Variant, SourceSheet As String, _ ... Dim rsData As ADODB.Recordset 'THE ERROR IS ALWAYS HERE ... If Header = False Then ...
    (microsoft.public.excel.programming)
  • Re: Error With ADO (Starting to pull my hair out)
    ... 'It will copy the Header row also ... Public Sub GetData(SourceFile As Variant, SourceSheet As String, _ ... Dim rsData As ADODB.Recordset ...
    (microsoft.public.excel.programming)