Auto log onto a website through excel

Hi Gurus,
I have the following code from a website that I want to automatically log in from excel.
I need it change the value of the username to come from a hidden sheet called "login" (Location "A1") and the password to be "B1"

For now use big for the username and cheese for the password (from the sheet login A1,B1)

The website is:
http://apmpropertydata.com.au/Default.aspx

These passwords will error and that's ok. I just need the code to log in

id="ctl00_ContentSideBar_SignIn1_Login1_UserName" name="ctl00$ContentSideBar$SignIn1$Login1$UserName" value="big"

id="ctl00_ContentSideBar_SignIn1_Login1_Password" name="ctl00$ContentSideBar$SignIn1$Login1$Password" value="cheese"

Below is the code for the log in button. I am not sure what you need.

<input type="submit" value="Log In" class="button" id="ctl00_ContentSideBar_SignIn1_Login1_LoginButton" name="ctl00$ContentSideBar$SignIn1$Login1$LoginButton" onclick="WebForm_DoPostBackWithOptions(new WebForm_PostBackOptions(&quot;ctl00$ContentSideBar$SignIn1$Login1$LoginButton&quot;, &quot;&quot;, true, &quot;ctl00$ContentSideBar$SignIn1$Login1&quot;, &quot;&quot;, false, true))" />

thanks guys

the greals.

thegrealsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

thegrealsAuthor Commented:
Hi Madonna,

That doesn't work
atomsheepCommented:
Try something like the code below. You need to reference the Microsoft XML library - in the Tools menu, select References, then check the Microsoft XML, I have v6.0, check whatever version is highest on your computer.

Sourced from http://stackoverflow.com/questions/158633/how-can-i-send-an-http-post-request-to-a-server-from-excel-using-vba
Sub logInASP()

Dim objHTTP As ServerXMLHTTP
Dim URL As String
URL = "http://apmpropertydata.com.au/Default.aspx"
Set objHTTP = New ServerXMLHTTP

objHTTP.Open "POST", URL, , "big", "cheese"
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send
objHTTP.WaitForResponse 10
Debug.Print objHTTP.ResponseText

End Sub

Open in new window

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

thegrealsAuthor Commented:
Hi atomsheep,

I added the references to v6.0. When I run the macro it doesn't even open an internet browser. I have tried it even having firefox and IE open.

Any ideas?

You sure you don't have to reference the id:

id="ctl00_ContentSideBar_SignIn1_Login1_UserName" name="ctl00$ContentSideBar$SignIn1$Login1$UserName" value="big"


Regards,

the greals.



atomsheepCommented:
Sorry, didn't understand your question; didn't know you wanted an IE window to pop up. Try the attached code. You will need the references to Microsoft Internet Controls and Microsoft HTML Object Library.
Dim URL As String
URL = "http://apmpropertydata.com.au/Default.aspx"
Dim ie As InternetExplorer
Set ie = New InternetExplorer
Dim d As HTMLDocument
Dim e As IHTMLElement

ie.Visible = True
ie.Navigate URL
Do
    DoEvents
Loop Until ie.Busy = False
Set d = ie.Document
Set e = d.getElementById("ctl00_ContentSideBar_SignIn1_Login1_UserName")
e.innerText = "big"
Set e = d.getElementById("ctl00_ContentSideBar_SignIn1_Login1_Password")
e.innerText = "cheese"
Set e = d.getElementById("ctl00_ContentSideBar_SignIn1_Login1_LoginButton")
e.Click

Open in new window

thegrealsAuthor Commented:
Hi Atomsheep,

I have added the references for the Microsoft Internet Controls and Microsoft HTML Object library.

I run the macro it pops up an IE window and then nothing happens. I close the IE window and get an error:

Run time error 91
Object variable or With block variable not set


Regards,


the greals
atomsheepCommented:
That's strange, for me it works perfectly. Are you able to navigate to the apmpropertydata.com.au page in IE by itself, without going through the macro?
thegrealsAuthor Commented:
Hi Atomsheep,

I don't get the error anymore but nothing at all happens. It doesn't open the ie browser at all. I have also tried opening the page seperately to see if it will log me in to no avail.

Any thoughts - Am I missing another control?

Regards,


the greals
atomsheepCommented:
Hmm, what version of IE and Excel are you running?

Also, let's double check the references. I have: Visual Basic for Applications, Microsoft Excel Object Library, Microsoft Internet Controls, Microsoft HTML Object Library, Microsoft Office Object Library, OLE Automation.

Also, perhaps try putting a pause loop in the code to allow the page to load etc (see attached code, which replaces the macro I posted previously).
Dim URL As String
URL = "http://apmpropertydata.com.au/Default.aspx"
Dim ie As InternetExplorer
Set ie = New InternetExplorer
Dim d As HTMLDocument
Dim e As IHTMLElement

ie.Visible = True
ie.Navigate URL

Do
    DoEvents
Loop Until ie.Busy = False

Dim t As Date
t = Now + TimeValue("00:00:10")
Do Until Now > t
    DoEvents
Loop

Set d = ie.Document
Set e = d.getElementById("ctl00_ContentSideBar_SignIn1_Login1_UserName")
e.innerText = "big"
Set e = d.getElementById("ctl00_ContentSideBar_SignIn1_Login1_Password")
e.innerText = "cheese"
Set e = d.getElementById("ctl00_ContentSideBar_SignIn1_Login1_LoginButton")
e.Click

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.