[SOLVED:] Help with Salesforce Login from Excel [Archive]

View Full Version : [SOLVED:] Help with Salesforce Login from Excel

Jacob Hilderbrand

01-19-2015, 10:45 AM

I am trying to login to Salesforce with VBA. Once I get past the login screen I can then navigate with the macro and get the data I want but I am stuck on the login.

URL = "https://cs17.salesforce.com/"
IE.Navigate URL
Do While IE.Busy Or IE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
Set Doc = IE.Document

Doc.getElementById("username").Value = "Test"
Doc.getElementById("pw").Value = "Test"
Doc.getElementById("login").Click

The problem is the button does not click, and it is not a typical Input button so that is probably why.

I tried submitting the form:

Doc.forms(1).elements("username").Value = "Test"
Doc.forms(1).elements("pw").Value = "Test
Doc.forms(1).submit

But this just clears the form, so perhaps there are hidden settings that need to be set?

The final thing I tried was to execute the javascript that the button uses:

IE.Document.parentWindow.execScript "handleLogin();"

But this gives an Access Denied error.

If anyone has any suggestions I would appreciate it.

SamT

01-19-2015, 11:19 AM

Wrong text posted in a hurry.

Jacob Hilderbrand

01-19-2015, 02:18 PM

Was there a suggestion in that, or was this just for reference?

The handlelogin routine gives an Access Denied error so I figured security settings from the site were blocking that.

I can set the variable to the login button but the click method does nothing.

snb

01-19-2015, 03:42 PM

Try: (mutatis mutandis)

Sub M_snb()
With CreateObject("MSXML2.XMLHTTP")
.Open "get", "https://cs17.salesforce.com?clrUsr=username&clrPw=password", False
.send

MsgBox .responsetext
End With
End Sub

Jacob Hilderbrand

01-19-2015, 05:48 PM

Yeah I use that method for another site and it works just fine but for this site it doesn't return anything useful.

The Response Text is:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta HTTP-EQUIV="PRAGMA" CONTENT="NO-CACHE">

<script>
if (this.SfdcApp && this.SfdcApp.projectOneNavigator) { SfdcApp.projectOneNavigator.handleRedirect('https://test.salesforce.com/?ec=302&startURL=%2F_ui%2Fsearch%2Fui%2FUnifiedSearchResults%3Fstr%3D61ffaa6b507941 c1bcae9897e492484b%253FclrUsr%253Drohit.sharma%2540fundingcircle.com.stagin g%26clrPw%3DRevival123%26sen%3D005%26sen%3D006%26sen%3D00Q%26sen%3D001%26se n%3D003%26searchType%3D2'); } else
if (window.location.replace){
window.location.replace('https://test.salesforce.com/?ec=302&startURL=%2F_ui%2Fsearch%2Fui%2FUnifiedSearchResults%3Fstr%3D61ffaa6b507941 c1bcae9897e492484b%253FclrUsr%253Drohit.sharma%2540fundingcircle.com.stagin g%26clrPw%3DRevival123%26sen%3D005%26sen%3D006%26sen%3D00Q%26sen%3D001%26se n%3D003%26searchType%3D2');
} else {;
window.location.href ='https://test.salesforce.com/?ec=302&startURL=%2F_ui%2Fsearch%2Fui%2FUnifiedSearchResults%3Fstr%3D61ffaa6b507941 c1bcae9897e492484b%253FclrUsr%253Drohit.sharma%2540fundingcircle.com.stagin g%26clrPw%3DRevival123%26sen%3D005%26sen%3D006%26sen%3D00Q%26sen%3D001%26se n%3D003%26searchType%3D2';
}
</script>

</head>

</html>


<script type="text/javascript">function bodyOnLoad(){if(window.PreferenceBits){window.PreferenceBits.prototype.csrf Token="null";};}function bodyOnBeforeUnload(){}function bodyOnFocus(){}function bodyOnUnload(){}</script>

</body>
</html>

Blade Hunter

01-19-2015, 07:24 PM

Why not use the Salesforce API? There are some examples out there of how to do this (I need to do it in a few months). Then you can use SOQL to get the data you need.

SamT

01-20-2015, 10:48 AM

Jacob, I was in a major hurry by the time I had that reference text ready and failed to notice that I hadn't even copied all of it.

The first form is the Domain picker. Form.Name = "login" is the second form. I quickly scanned all the js and didn't see any that would effect a simple log in. most of them seemed to be for use with blade Hunter's suggestion.

There are a lot of hidden fields in the Log In Form. Like snb hinted at, the Password is not part of that form.

Here are the form fields

<fieldset style="display:none">
<input name="un" value="" type="hidden">
<input name="width" value="" type="hidden">
<input name="height" value="" type="hidden">
<input name="hasRememberUn" value="true" type="hidden">
<input id="login_startUrl" name="startURL" value="" type="hidden">
<input name="loginURL" value="" type="hidden">
<input name="loginType" value="" type="hidden">
<input name="useSecure" value="true" type="hidden">
<input name="local" value="" type="hidden">
<input name="lt" value="standard" type="hidden">


<input name="qs" value="r=http%3A%2F%2Fwww.vbaexpress.com%2Fforum%2Fshowthread.php" type="hidden">

<input type="hidden" name="qs" value="" />

<input name="locale" value="" type="hidden">
<input name="oauth_token" value="" type="hidden">
<input name="oauth_callback" value="" type="hidden">
<input name="login" value="" type="hidden">
<input name="serverid" value="" type="hidden">
<input name="display" value="page" type="hidden">
</fieldset>
The Function handleLogin assigns your user name to the field un and assigns the screens width and height to the form fields ,too, so I assume they are required. I believe that the rest of the form fields are for use with Blade Hunters suggestion.

I think that you need snb's code to Push the password and Post the un, width, and height fields.

The attached is the highlights of the source code of SaleForce's Log In page. Remove the .zip extension I used to bypass the filter. It is a text file.

Blade Hunter

01-20-2015, 02:21 PM

Here is the code I have stored away for when I need to move on this project in a few months, hope it helps you.

Public Function SFLogin(UserName, Password) As Boolean
On Error Resume Next
'create a session object
Set g_sfApi = New SForceOfficeToolkitLib4.SForceSession4
'make a login call
SampleLogin = g_sfApi.Login(UserName, Password)
End Function

Public Function SFQuery()
Dim qr As QueryResultSet4
Dim v As Variant
Dim s As SObject4
Set qr = g_sfApi.Query("select * from task", False)
For Each v In qr
'loop through the results
'cast to an SObject4 to see more helpful debug info
Set s = v
'use the object
s("Name") = "Query"
's.Update
Next v
End Function

This is from the help area of Salesforce Wiki for the VBA implementation of the API.

Jacob Hilderbrand

01-24-2015, 06:55 PM

Thanks