send login details to web form using Microsoft.XMLHTTP

**robert101083** · 06-10-2013, 04:34 AM

Hi,

I like to download a CSV file from a website. I can access this file using a direct link, but I need to login first before I can access this file.

What I have so far works just fine. I can open the url and save the content to a CSV file. But the thing being saved in the file at the moment is the HTML of the page which says I need to login first.

The VBA:

Please Login or Register  to view this content.

The login Form:

HTML Code:

I do not wan't any browsers to open, it needs to run in the background.

I've tried a lot of different methods to login - for the last 4 hours, but they all didn't work so I leave them out. And cant remember which they all where either.

Thanks for any help!

Cheers,
Robert

**Kyle123** · 06-10-2013, 04:35 AM

I suspect you need to use the WinHTTPRequest object in place of msxml, msxml doesn't support cookies which you will almost certainly need.

**robert101083** · 06-10-2013, 05:08 AM

Originally Posted by Kyle123

I suspect you need to use the WinHTTPRequest object in place of msxml, msxml doesn't support cookies which you will almost certainly need.

Hi Kyle,

Thank you very much for the quick reply.

I used this vba to do a quck test:

Please Login or Register  to view this content.

I used my username and password in the VBA above and the real URL and when I run it I get the following error:
A certificate is required to complete client authentication

The debug selects "MyRequest.Send".

I'm not sure if the method I used is for a login like with .htaccess and ftp or for login using a web form.

**Kyle123** · 06-10-2013, 05:21 AM

You can't do it like that, you need 2 requests, first to get a session cookie, then a second one to get the actual file. Something like:

Please Login or Register  to view this content.

**robert101083** · 06-10-2013, 05:59 AM

Originally Posted by Kyle123

You can't do it like that, you need 2 requests, first to get a session cookie, then a second one to get the actual file. Something like:

Please Login or Register  to view this content.

Unfortunately I still end up with the same error: "A certificate is required to complete client authentication". The debugger stops at ".Send "user_session[email]=" & UserName & "&" _ & "user_session[password]=" & PassWord"

** EDIT **: There is an input field called "authenticity_token" in the form. Could that be the problem? Should I try to read the value of that one first and add it to the send data?

Please Login or Register  to view this content.

**Kyle123** · 06-10-2013, 06:50 AM

Ok, I'd missed the https, you need to reference the client certificate on your machine - have a look here: http://msdn.microsoft.com/en-us/libr...(v=vs.85).aspx

Essentially, you need to tell WinhttpRequest, which certificate to use on your machine, so something like:

Please Login or Register  to view this content.

**robert101083** · 06-10-2013, 07:04 AM

** EDIT ** Got email about your latest post later than I posted this post ** End EDIT **

This is what I have right now, including the code to retrieve the token:

Please Login or Register  to view this content.

If I read the AuthToken in debug, it's every time the same value: "C9NmjXesybezcdcUtQOxoCLEGQwQ9lpip1CPEfo4xO8=" /></div> <table style="margin-bottom:5px;font-weight:bold;width:100%"

So I think there is something wrong with the regex - which I do not how to fix - and it doesn't update it's value. Even when I set a Watch and change the value to "C9NmjXesybezcdcUtQOxoCLEGQwQ9lpip1CPEfo4xO8=" the same error keeps popping up.

Thank you for any help!

**Kyle123** · 06-10-2013, 07:18 AM

Ok, is the certificate stuff working?

Stick to WinHttp, drop the xml stuff, using the two libraries is confusing.

**robert101083** · 06-10-2013, 07:33 AM

Originally Posted by Kyle123

Ok, is the certificate stuff working?

Stick to WinHttp, drop the xml stuff, using the two libraries is confusing.

Well, that's what I tried first. To use WinHttp for the AuthToken as well. But then I got that error again when .send was run for that part of the code as well.

I was still reading about using .SetClientCertificate. I don't know where to look for the right name of the certificate. I am familiar with using the registry but can't find it. Read something about session based login as well, they store the certificate some where else...?