Hi Folks,
I've been trying this for a week now and honestly i dont know what I'm doing.
My issue is this. I am trying to connect to a website and pull data back. Sounds straight forward but the login to the website is embedded (not a popup) which seems to be an issue for me as the last time I was able to put the username and password into the web address. I have created a Macro to do this but it will not hold the username and password details. The script for the Macro is...
' Macro7 Macro
'
'
Range("C1:AP81").Select
Selection.QueryTable.Delete
Selection.QueryTable.Delete
Selection.QueryTable.Delete
Selection.ClearContents
Range("C1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://10.42.18.119/home.htm", Destination:=Range("$C$1"))
.Name = "home_40"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "11"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
I guess i need to input the username and password details somewhere in this script but I dont know how. The code from the login page is this...
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>APC | Log On</title>
****** http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
****** http-equiv="Expires" content="-1">
****** http-equiv="Pragma" content="no-cache">
****** http-equiv="Cache-Control" content="no-cache">
<link href="core.css" rel="stylesheet" type="text/css" />
******** type="text/javascript">
function isValidBrowser() {
var nVer = navigator.appVersion;
var nAgt = navigator.userAgent;
var fullVersion = parseFloat(nVer);
// In Internet Explorer, the true version is after "MSIE"
if ((verOffset = nAgt.indexOf("MSIE")) != -1) {
fullVersion = parseFloat(nAgt.substring(verOffset+5,nAgt.length));
}
// In Opera, the true version is after "Opera"
if ((verOffset = nAgt.indexOf("Opera")) != -1) {
fullVersion = parseFloat(nAgt.substring(verOffset+6,nAgt.length));
}
if (((navigator.appName == "Microsoft Internet Explorer") && (fullVersion >= 5.5)) || ((navigator.appName == "Netscape") && (fullVersion >= 5.0))) {
return true;
} else {
return false;
}
}
if (isValidBrowser() == false) {
alert("Browser is incompatible with this device!");
}
function areCookiesEnabled() {
var tmpcookie = new Date();
chkcookie = (tmpcookie.getTime() + '');
document.cookie = "chkcookie=" + chkcookie + "; path=/";
if (document.cookie.indexOf(chkcookie,0) < 0) {
return false;
} else {
return true;
}
}
if (areCookiesEnabled() == false) {
alert("Browser does not have cookies enabled!");
}
function placeFocus() {
if (document.forms.length > 0) {
var field = document.forms[0];
for (i = 0; i < field.length; i++) {
if ((field.elements.type == "text") || (field.elements.type == "textarea")) {
document.forms[0].elements.focus();
break;
}
}
}
}
*********> </head>
<body ******="placeFocus()">
<table cellpadding="0" cellspacing="0" id="message">
<tr>
<td class="edging"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="msg">
<form method="post" action="/Forms/login1" name="HashForm1">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="27%" class="dataName">User Name:</td>
<td width="73%"><input type="text" name="login_username" size="17" maxlength="16" value="" />
</td>
</tr>
<tr>
<td class="dataName">Password:</td>
<td><input type="password" name="login_password" size="17" maxlength="32" value="" />
</td>
</tr>
<tr>
<td colspan="2" class="applyCancel"><input type="submit" name="submit" value="Log On" class="btn" />
<input type="reset" value="Reset" />
</td>
</tr>
</table>
</form>
</td>
</tr>
<tr>
<td class="apclogo"><a href="http://www.apc.com"><img src="images/smapc.gif" alt="APC Website" width="59" height="17" border="0" align="top" /></a> </td>
</tr>
</table>
</body>
</html>
I dont know how to take the details from this and combine it with my Macro script.
Username will be = Admin
Password will be = Password
I'm sure if I can sort out the login piece I can look after pulling the rest of the data.
Any help is appreciated.
I've been trying this for a week now and honestly i dont know what I'm doing.
My issue is this. I am trying to connect to a website and pull data back. Sounds straight forward but the login to the website is embedded (not a popup) which seems to be an issue for me as the last time I was able to put the username and password into the web address. I have created a Macro to do this but it will not hold the username and password details. The script for the Macro is...
' Macro7 Macro
'
'
Range("C1:AP81").Select
Selection.QueryTable.Delete
Selection.QueryTable.Delete
Selection.QueryTable.Delete
Selection.ClearContents
Range("C1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://10.42.18.119/home.htm", Destination:=Range("$C$1"))
.Name = "home_40"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "11"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
I guess i need to input the username and password details somewhere in this script but I dont know how. The code from the login page is this...
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>APC | Log On</title>
****** http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
****** http-equiv="Expires" content="-1">
****** http-equiv="Pragma" content="no-cache">
****** http-equiv="Cache-Control" content="no-cache">
<link href="core.css" rel="stylesheet" type="text/css" />
******** type="text/javascript">
function isValidBrowser() {
var nVer = navigator.appVersion;
var nAgt = navigator.userAgent;
var fullVersion = parseFloat(nVer);
// In Internet Explorer, the true version is after "MSIE"
if ((verOffset = nAgt.indexOf("MSIE")) != -1) {
fullVersion = parseFloat(nAgt.substring(verOffset+5,nAgt.length));
}
// In Opera, the true version is after "Opera"
if ((verOffset = nAgt.indexOf("Opera")) != -1) {
fullVersion = parseFloat(nAgt.substring(verOffset+6,nAgt.length));
}
if (((navigator.appName == "Microsoft Internet Explorer") && (fullVersion >= 5.5)) || ((navigator.appName == "Netscape") && (fullVersion >= 5.0))) {
return true;
} else {
return false;
}
}
if (isValidBrowser() == false) {
alert("Browser is incompatible with this device!");
}
function areCookiesEnabled() {
var tmpcookie = new Date();
chkcookie = (tmpcookie.getTime() + '');
document.cookie = "chkcookie=" + chkcookie + "; path=/";
if (document.cookie.indexOf(chkcookie,0) < 0) {
return false;
} else {
return true;
}
}
if (areCookiesEnabled() == false) {
alert("Browser does not have cookies enabled!");
}
function placeFocus() {
if (document.forms.length > 0) {
var field = document.forms[0];
for (i = 0; i < field.length; i++) {
if ((field.elements.type == "text") || (field.elements.type == "textarea")) {
document.forms[0].elements.focus();
break;
}
}
}
}
*********> </head>
<body ******="placeFocus()">
<table cellpadding="0" cellspacing="0" id="message">
<tr>
<td class="edging"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="msg">
<form method="post" action="/Forms/login1" name="HashForm1">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="27%" class="dataName">User Name:</td>
<td width="73%"><input type="text" name="login_username" size="17" maxlength="16" value="" />
</td>
</tr>
<tr>
<td class="dataName">Password:</td>
<td><input type="password" name="login_password" size="17" maxlength="32" value="" />
</td>
</tr>
<tr>
<td colspan="2" class="applyCancel"><input type="submit" name="submit" value="Log On" class="btn" />
<input type="reset" value="Reset" />
</td>
</tr>
</table>
</form>
</td>
</tr>
<tr>
<td class="apclogo"><a href="http://www.apc.com"><img src="images/smapc.gif" alt="APC Website" width="59" height="17" border="0" align="top" /></a> </td>
</tr>
</table>
</body>
</html>
I dont know how to take the details from this and combine it with my Macro script.
Username will be = Admin
Password will be = Password
I'm sure if I can sort out the login piece I can look after pulling the rest of the data.
Any help is appreciated.
