Word VBA & URL's

[bigal.nz]

Hi All,

I want to automate a process that we have which involves:

(a) logging into a website
- username
- password
- session cookie
(b) navigating to another page in that site, which will be determined by a variable 'StrIDNumber'

I have started to play with WinHttp.WinHttpRequest.5.1 in VBA.

Its hard however without seeing the results of my GET and POST requests to see whether I am being successful logging in, and if not what the error is - because I cant see the full html being returned.

I tried to display the result in a msgbox, but its got all the html in it, and is truncated.

How can I display the results so I can work through this a bit easier?

I have some test code below but it doesn't have anything to handle the session cookies as yet.

Cheers

-Al





Sub Test()

Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")

UserName = "user"
Password = "secret"

LoginBody = "j_username:" & UserName & " j_password:" & Password

URL = "https://sv.mycompany.com.au/ip/logon"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send (LoginBody)

MsgBox (objHTTP.ResponseText)

'experiment to find a string within result
StrStart = (InStr(1, objHTTP.ResponseText, "<small>") + 7)
StrEnd = (InStr(1, objHTTP.ResponseText, "</small>"))
StrLength = StrEnd - StrStart
MsgBox (StrLength)


End Sub

[fumei]

Have you look at all the available functions?

http://msdn.microsoft.com/en-us/library/windows/desktop/aa384257(v=vs.85).aspx

[bigal.nz]

Hmm, ok - I have learned how to use the immediate's window so I can see whats coming back (ish).

But I need to learn a little more about what this website is expecting to successfully login.

After login with firefox I can see the request contained:

Host: test.com.au
User Agent: Mozilla/5.0 (Windows NT 5.1; rv:33.0) Gecko/20100101 Firefox/33.0
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept Language: en-US,en;q=0.5
Accept Encoding: gzip, deflate
Referrer: h##p://test.com.au/psp/EMP/HRMS/?cmd=logout
Cookie: SignOnDefault=user; JSESSIONID=xxxxxxxxxxxxxxxx; PSLOGINLIST=xxxx; PSTOKEN=xxxxx
Connection: Keep Alive

The question becomes how many of these headers are necessary?

How do I make the VBA com object follow redirects?

Presumeably the headers that I decide I do need should be parsed to the server with something like:




Sub Test()

Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1)

' Set some headers
username = user
password = secret
URL = test.com.au

'Get Session Cookie
objHTTP.Open "GET", URL, False
obj.HTTP.send ("")
Cookie = (objHTTP.getResponseHeader("Set-Cookie")

LoginBody = "JSESSIONID:" & Cookie "userid:" & user "password:" & password" etc etc

objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send (LoginBody)
objHTTP.WaitForResponse
Debug.Print (objHTTP.responseText)



The only problem with this is that I am not parsing half of the headers I need to like encoding, language etc. Are the essential though?

Help!!

-Al

[bigal.nz]

If my end game is to login to this website and obtain one of the web pages as a pdf - is there a better (easier) way to do this?

Some sort of way to control IE perhaps?

[bigal.nz]

Perhaps I need to use the InternetExplorer object:

Something like:



Dim IE
Dim Helem

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = 1
IE.navigate "http://www.example.com"

Set Helem = IE.document.getElementByID("formUsername")
Helem.Value = "username" ' change this to yours
Set Helem = IE.document.getElementByID("formPassword")
Helem.Value = "password" ' change this to yours
Set Helem = IE.document.Forms(0)
Helem.Submit

Do While (IE.Busy)
WScript.Sleep 10
Loop

Dim someElement
Set someElement = IE.document.getElementByID("someElement")

If Len(someElement.innerText) > 0 Then
MsgBox "logged in"
End If

[bigal.nz]

Ok, this seems to open webpage ok, but the printing isnt working.

Am I missing something?




Sub Test()

Dim IE
Dim Helem

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = 1
IE.Navigate "http://www.text.com.au"

Do
DoEvents
Loop Until IE.ReadyState = 4


Set Helem = IE.Document.getElementById("userid")
Helem.Value = "user" ' change this to yours
Set Helem = IE.Document.getElementById("pwd")
Helem.Value = "secret" ' change this to yours
'Set Helem = IE.Document.Forms(0)
'Helem.Submit
IE.Document.getElementById("submit").Click

Do
DoEvents
Loop Until IE.ReadyState = 4

IE.Navigate "http://www.text.com.au"

Do
DoEvents
Loop Until IE.ReadyState = 4

IE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER


End Sub

[bigal.nz]

Ok, this seems to open webpage ok, but the printing isnt working.

Am I missing something?




Sub Test()

Dim IE
Dim Helem

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = 1
IE.Navigate "http://www.text.com.au"

Do
DoEvents
Loop Until IE.ReadyState = 4


Set Helem = IE.Document.getElementById("userid")
Helem.Value = "user" ' change this to yours
Set Helem = IE.Document.getElementById("pwd")
Helem.Value = "secret" ' change this to yours
'Set Helem = IE.Document.Forms(0)
'Helem.Submit
IE.Document.getElementById("submit").Click

Do
DoEvents
Loop Until IE.ReadyState = 4

IE.Navigate "http://www.text.com.au"

Do
DoEvents
Loop Until IE.ReadyState = 4

IE.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER


End Sub



OK!

The problem with my method above is that :

(a) Doing it through the COM object doesnt let me select the printer (though there is a registry hack to change that); and
(b) Even if I could select our PDF creater or Adobe PDF printer, I still would have no way to progmatically pass the file path that I want to PDF saved to.

One other option I am considering is copy and pasting the whole webpage into word and then saving it as PDF from word.

Any better ideas?

-Al