So I have a Web query on my excel sheet to pull data off a website. My problem is my people need to log in each time the sheet is open. Is there a way to input my information in a excel script or macro to auto log me in?
So I have a Web query on my excel sheet to pull data off a website. My problem is my people need to log in each time the sheet is open. Is there a way to input my information in a excel script or macro to auto log me in?
This even possible?
It all depends on the website.
But usually you wont be able to run a web query through a website which requires a login first.
However it is fairly straight forward to use some code that will pass the username & password details to the site, then access the page you need and pull in the relevant data ie, a table of data.
I would need the website address & also a copy of the url for the page required plus a copy of the source code for that page. select ctrl + U and then copy and paste all the data to notepad.
Regards
Sean
Please add to my reputation if you think i helped (click on the star below the post)
Mark threads as "Solved" if you have your answer (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [code]
Please supply a workbook containing example Data: It makes its easier to answer your problem & saves time!
so the address is
the source code isHTML Code:
<HTML>
<HEAD><TITLE>SSO Login - Welcome to GE Consumer & Industrial</TITLE>
<META content=no-cache http-equiv=pragma>
<META content=0 http-equiv=expires><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<LINK REL=stylesheet TYPE="text/css" HREF="sso-styles.css">
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META content="MSHTML 5.00.2919.6307" name=GENERATOR>
<META content=FrontPage.Editor.Document name=ProgId>
<script language=javascript>
function setUserNameValue()
{
if(document.signon.USER.value == "")
return;
else
{
//trim user username value
document.signon.USER.value = eval(trim(document.signon.USER.value));
//alert(document.signon.USER.value.length);
}
}//setValues()
// SSO Cookie code starts from here
function setCookie(name, value, expires, path, domain, secure) {
var curCookie = name + "=" + escape(value) +
((expires) ? "; expires=" + expires.toGMTString() : "") +
((path) ? "; path=" + path : "") +
((domain) ? "; domain=" + domain : "") +
((secure) ? "; secure" : "");
document.cookie = curCookie;
}
function saveSSOID () {
var currentSSOID = getCookies("ssouserid");
if (currentSSOID != document.signon.USER.value && currentSSOID != null) {
if (!confirm("The login screen is currently remembering the SSO User ID of " + currentSSOID + ". OK to change this to " + document.login.USER.value + "?")) {
return;
}
}
var now = new Date();
var expireDate = new Date();
expireDate.setTime(now.getTime() + 365 * 24 * 60 * 60 * 1000);
var TrimmedUserName = eval( trim(document.signon.USER.value));
setCookie("ssouserid", TrimmedUserName, expireDate, "/", ".ge.com");
var SSOID = getCookies("ssouserid");
if (SSOID == document.signon.USER.value && SSOID != currentSSOID) {
alert ("SSO login screens used by GE Consumer & Industrial are now set to remember and prepopulate your SSO User ID when accessed from this computer. Please note that SSO login screens used by other GE businesses may not have this feature. For your security, login screens do not remember your SSO Password.");
}
}
function window_onload() {
var SSOID = getCookies("ssouserid");
if (SSOID == null) SSOID="";
if (SSOID == "") {
document.signon.USER.focus();
} else {
document.signon.USER.value=eval(trim(SSOID));
document.signon.PASSWORD.focus();
}
}
function getCookies(name) {
var dc = document.cookie;
var prefix = name + "=";
var begin = dc.indexOf("; " + prefix);
if (begin == -1) {
begin = dc.indexOf(prefix);
if (begin != 0) return null;
} else
begin += 2;
var end = document.cookie.indexOf(";", begin);
if (end == -1)
end = dc.length;
return unescape(dc.substring(begin + prefix.length, end));
}
function deleteCookie(name, path, domain) {
if (getCookies(name)) {
document.cookie = name + "=" +
((path) ? "; path=" + path : "") +
((domain) ? "; domain=" + domain : "") +
"; expires=Thu, 01-Jan-70 00:00:01 GMT";
}
}
function clearSSOID () {
document.signon.USER.focus();
var SSOID = getCookies("ssouserid");
if (SSOID == null || SSOID == "") {
return;
}
if (confirm("Are you sure you no longer want GE Specialty Materials login screens to remember and prepopulate your SSO User ID? Note: Clicking OK will not delete your SSO account.")) {
deleteCookie ("ssouserid", "/", ".ge.com");
document.signon.USER.value = "";
}
}
// SSO Cookie code ends here
function fnFocus()
{
document.signon.USER.focus();
}
function LgValidateParams()
{
var msg = "Please enter the following values:" + "nn";
var error= false ;
if(trim(document.signon.USER.value) == "")
{
msg = msg + " SSO User ID" + "nn";
error = true;
alert("SSO id is empty");
return false;
}
if(trim(document.signon.PASSWORD.value) == "")
{
msg = msg + "SSO Password " ;
error = true;
alert("password is empty");
return false;
}
if (document.signon.saveID != null) {
if(document.signon.saveID.checked){
saveSSOID ();
}
}
setCookie("target", "HTTP://supplychainweb.consind.ge.com/ids/Dispatcher?REQUEST=SHOWASSIGNMENT&SHIPTYPE=OUTBOUND", null, "/", ".ge.com");
document.signon.submit();
return true;
}
function trim(s) {
var lTrim = /^s+/;
var rTrim = /s+$/;
var os = String(s);
os = os.replace(rTrim, '');
return os.replace(lTrim, '');
}
function openFAQ() {
var URL = "https://www.ge-registrar.com/gecentral/ssofaqpage.jsp?i18n=en_US";
var WindowName = "FAQ";
window.open(URL,WindowName,
'toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=no,copyhistory=no,width=500,height=550');
}
</script>
<style fprolloverstyle>A:hover {color: #3B73B9; font-weight: bold}
</style>
</HEAD>
<BODY aLink=#FFFFFF leftMargin=10 link=#FFFFFF ; topMargin=0 vLink=#FFFFFF onload="javascript:window_onload();">
<FORM method=post name=signon autocomplete="OFF" onSubmit="return LgValidateParams()">
<TABLE border=0 cellPadding=0 cellSpacing=0 align="center">
<TR>
<!-- Masthead image -->
<table border="0" cellpadding="0" cellspacing="0" align="center">
<tr bgcolor="#ffffff">
<td width='100%'><img src='images/sso-mastheadsmall.gif' alt='' border='0'></td>
</tr>
</table>
</TR>
</TABLE>
<!-- Table Format begin -->
<table border="0" align="center">
<tr><td height="1px"><font size="1px"> </font></td></tr>
<tr>
<td ><font size="2" face="Arial" color="#3B73B9">Enter your User ID and Password to Login</font>
</td>
</tr>
<tr>
<td width="262" >
<!-- Login user id password details begin-->
<TABLE border=0 cellPadding=10 cellSpacing=0 >
<input type=hidden name=target value="HTTP://supplychainweb.consind.ge.com/ids/Dispatcher?REQUEST=SHOWASSIGNMENT&SHIPTYPE=OUTBOUND">
<input type=hidden name=smauthreason value="0">
<input type=hidden name=smtryno value=0>
<INPUT type=hidden value=1 name=lsubmit>
<TR>
<TD width="100%">
<FONT face=Arial size=2 color="#808080"><b>User ID:</b></FONT><br>
<INPUT maxLength=30 name=USER size=20 onBlur="setUserNameValue()">
</TD>
</TR>
<TR>
<TD noWrap width="100%">
<FONT face=Arial size=2 color="#808080"><b>Password:</b></FONT><BR>
<INPUT maxLength=30 name=PASSWORD size=20 type=password><BR>
<INPUT class=Submit type=submit value='Log In'>
<span style="font-size:12.0pt;font-family:"MS Sans Serif";
mso-fareast-font-family:"MS Sans Serif";mso-ansi-language:EN-US;mso-fareast-language:
EN-US;mso-bidi-language:AR-SA"><img src='images/navBul.gif' v:shapes="_x0000_i1025" width="3" height="5">
</span>
<a href="https://www.ge-registrar.com/gecentral/reset.jsp?referrer=null"><font size="1" face="MS Sans Serif" color="#3B73B9">Forgot your Password?</font></a>
</TD>
</TR>
</Table>
<!-- Login user id password details end -->
</td>
</tr>
</table>
<table border="0" align="center">
<tr>
<td >
<!--start footer row-->
<table border="0" cellpadding="0" cellspacing="0" bgcolor="#ffffff">
<tr>
<td class="Copy" align="left">
<font face="Arial" size="1" color="#808080">Copyright General Electric Company 2000-2005</font>
</td>
</tr>
<tr>
<td class="Copy" align="right"><font face="Arial" size="1" color="#808080">Login page hosted by: GE Consumer & Industrial</font>
</td>
</tr>
</table>
<!--end footer row-->
</td>
</tr>
</table>
</Table></form>
</BODY>
</HTML>
okay slight problem!
I cannot access that site from either of my computers.
I may not be able to help for that reason alone.
however I might be able to guide you.
Could you take a screen shot of the first screen so I can see what is there in terms of text boxes & buttons.
And can you also give the source code for the first screen without the username or password entered.
I will then let you know if it is possible to do this without me having access.
The first code is without user name or pw entered
I can't upload the file either as my company blocks me from uploading it. I could email it ?
I am not going to be able to help on this occasion as i cannot access the web site to be able write the code you need. But here is some info on how you can do it.
For some reason I am erroring at
it says User-Defined type not definedPlease Login or Register to view this content.
Please Login or Register to view this content.
Last edited by vitt4300; 01-20-2015 at 12:58 PM.
Add a userform with a button on to your workbook.
Im not a %100 sure i follow, sorry.
The issue you have is that you're missing the library from your workbook where the DataObject lives, the easiest way to add it is to put a userform in your workbook
i know this is in the developer tab but i dont know which to insert. none say userform
I went to references and checked my object library and Microsoft internet controls
Remove/comment Dim clip As DataObject and other lines which have clip from the code and see if the code works.
it started working now errors at the loging in part. I think I am just going to scrap this idea.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks