Web Query: Cannot retrieve data, will not login

admiralg

New Member
Joined
Jan 13, 2012
Messages
20
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.
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Watch MrExcel Video

Forum statistics

Threads
1,123,079
Messages
5,599,636
Members
414,326
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top