Retrieve data from company intranet page

A

acampbell012

Hi,

I am attempting to retrieve employee information from my company's web
site (employee directory). The web page uses frames (I think). Working
directly from the web page, you enter data on the form, select the
search field (eg. empl. name, ID, phone number, etc.) to return the
results.

I am attempting to retrieve the employee name based on the employee ID
which i already have stored as a variable in Excel.

I am getting better at coding in Excel, but this is new ground for me
and any help would be appreciated. I would like to bring the emloyee
name back to a cell within Excel.

I have pasted the code from the intranet "search frame" below.
Regardless of the search criteria, the search results are always listed
in a set order (e.g. name, id, phone, dept., etc.) Thanks in advance
for any responses.

Alan


<html>
<head>
<title>Employee Directory</title>
<LINK rel="stylesheet" type="text/css" href="/css/mycompany.css">
</head>

<SCRIPT LANGUAGE="JavaScript">
<!--
var w2 = null;
function IHRISHelp(){
if (w2 != null)
w2.close();

w2=window.open("help.htm",
"Help","width=400,height=300,navbar=yes,resizable=yes");

}

function submitTheForm(){
if (document.form0.searchval.value.length > 0) {
document.form0.submit();
}
}

function MainNumbers(){
if (w2 != null)
w2.close();
w2=window.open("mainnumbers/MainNumbers.htm",
"MainNumbers","width=600,height=350,scrollbars=yes,menubar=yes,navbar=yes,resizable=yes");
}

function LeadershipTeamReport(){
if (w2 != null)
w2.close();
w2=window.open("LeadershipTeamReport/LeadershipTeamReport.xls",
"LeadershipTeamReport","width=600,height=350,scrollbars=yes,menubar=yes,navbar=yes,resizable=yes");
}

function PrintEmployeeList(){
parent.EmployeeList.focus();
parent.EmployeeList.print();
}
//-->
</SCRIPT>

<body class="body5" onLoad="document.forms[0].searchval.focus();">


<h3>Employee Directory</h3>

<form method="POST" action="listingFrame.asp" target="EmployeeList"
name="form0">
<table border="0">

<tr>
<td>Search For:<input type="text" name="searchval" size="20"
onKeyUp="submitTheForm()"></td>
<td align='left' valign='center'>Search By:
<select name="Search_Field" size="1">
<option VALUE="Name">Name</option>
<option VALUE="Empnum">Empl. Number</option>
<option VALUE="Phone">Phone Extension</option>
<option VALUE="Mail">Mail</option>
<option VALUE="BusinessUnit">Organization</option>
<option VALUE="Department">Department/Area</option>
<option VALUE="Job">Job Title</option>
<option VALUE="SupervisorName">Manager</option>
<option VALUE="Phonetic">Phonetic Last Name</option>
</SELECT></td>
</tr></table>
<table border="0" width="100%">
<tr>
<td>
<input type="submit" value="Search" name="Submit">&nbsp;
<input type="button" value="Print" name="Print"
onclick="PrintEmployeeList();">&nbsp;
<input type="submit" value="Export" name="Submit">&nbsp;
<!--<input type="button" value="Main Numbers" name="Main"
onclick="MainNumbers();">&nbsp;-->
</td>
</tr>
</table>
<input type="hidden" name="appType" value="full">
<hr>
</form>

</body>
 
A

acampbell012

Hello again,

I am reposting to my original request for some help. One of my
company's web developers helped me (I think) with the problem in my
original post. However, I have another question I am hoping somebody
may be able to help me with.

The following link, placed in a web query, successfully retrieves the
employee name based on the I.D. (830598). However, this value needs to
change for each user that opens my spreadsheet. I have the employee ID
stored in a variable elsewhere in my spreadsheet for each user that
opens the workbook. However, I don't know how to programatically alter
this query to include that stored value in the query.

Any help would be greatly appreciated.

http://MyCompany/apps/empname/get_emp_name.asp?empnum=830598


Hi,

I am attempting to retrieve employee information from my company's web
site (employee directory). The web page uses frames (I think). Working
directly from the web page, you enter data on the form, select the
search field (eg. empl. name, ID, phone number, etc.) to return the
results.

I am attempting to retrieve the employee name based on the employee ID
which i already have stored as a variable in Excel.

I am getting better at coding in Excel, but this is new ground for me
and any help would be appreciated. I would like to bring the emloyee
name back to a cell within Excel.

I have pasted the code from the intranet "search frame" below.
Regardless of the search criteria, the search results are always listed
in a set order (e.g. name, id, phone, dept., etc.) Thanks in advance
for any responses.

Alan


<html>
<head>
<title>Employee Directory</title>
<LINK rel="stylesheet" type="text/css" href="/css/mycompany.css">
</head>

<SCRIPT LANGUAGE="JavaScript">
<!--
var w2 = null;
function IHRISHelp(){
if (w2 != null)
w2.close();

w2=window.open("help.htm",
"Help","width=400,height=300,navbar=yes,resizable=yes");

}

function submitTheForm(){
if (document.form0.searchval.value.length > 0) {
document.form0.submit();
}
}

function MainNumbers(){
if (w2 != null)
w2.close();
w2=window.open("mainnumbers/MainNumbers.htm",
"MainNumbers","width=600,height=350,scrollbars=yes,menubar=yes,navbar=yes,resizable=yes");
}

function LeadershipTeamReport(){
if (w2 != null)
w2.close();
w2=window.open("LeadershipTeamReport/LeadershipTeamReport.xls",
"LeadershipTeamReport","width=600,height=350,scrollbars=yes,menubar=yes,navbar=yes,resizable=yes");
}

function PrintEmployeeList(){
parent.EmployeeList.focus();
parent.EmployeeList.print();
}
//-->
</SCRIPT>

<body class="body5" onLoad="document.forms[0].searchval.focus();">


<h3>Employee Directory</h3>

<form method="POST" action="listingFrame.asp" target="EmployeeList"
name="form0">
<table border="0">

<tr>
<td>Search For:<input type="text" name="searchval" size="20"
onKeyUp="submitTheForm()"></td>
<td align='left' valign='center'>Search By:
<select name="Search_Field" size="1">
<option VALUE="Name">Name</option>
<option VALUE="Empnum">Empl. Number</option>
<option VALUE="Phone">Phone Extension</option>
<option VALUE="Mail">Mail</option>
<option VALUE="BusinessUnit">Organization</option>
<option VALUE="Department">Department/Area</option>
<option VALUE="Job">Job Title</option>
<option VALUE="SupervisorName">Manager</option>
<option VALUE="Phonetic">Phonetic Last Name</option>
</SELECT></td>
</tr></table>
<table border="0" width="100%">
<tr>
<td>
<input type="submit" value="Search" name="Submit">&nbsp;
<input type="button" value="Print" name="Print"
onclick="PrintEmployeeList();">&nbsp;
<input type="submit" value="Export" name="Submit">&nbsp;
<!--<input type="button" value="Main Numbers" name="Main"
onclick="MainNumbers();">&nbsp;-->
</td>
</tr>
</table>
<input type="hidden" name="appType" value="full">
<hr>
</form>

</body>
Click to expand...
 
N

NickHK

Depends how you are refreshing your web query.
From code:
querystr=http://MyCompany/apps/empname/get_emp_name.asp?empnum=" &
range("IDNumber").value

On a WS:
Look into "parameter query" from the Excel help.

NickHK

Hello again,

I am reposting to my original request for some help. One of my
company's web developers helped me (I think) with the problem in my
original post. However, I have another question I am hoping somebody
may be able to help me with.

The following link, placed in a web query, successfully retrieves the
employee name based on the I.D. (830598). However, this value needs to
change for each user that opens my spreadsheet. I have the employee ID
stored in a variable elsewhere in my spreadsheet for each user that
opens the workbook. However, I don't know how to programatically alter
this query to include that stored value in the query.

Any help would be greatly appreciated.

http://MyCompany/apps/empname/get_emp_name.asp?empnum=830598


Hi,

I am attempting to retrieve employee information from my company's web
site (employee directory). The web page uses frames (I think). Working
directly from the web page, you enter data on the form, select the
search field (eg. empl. name, ID, phone number, etc.) to return the
results.

I am attempting to retrieve the employee name based on the employee ID
which i already have stored as a variable in Excel.

I am getting better at coding in Excel, but this is new ground for me
and any help would be appreciated. I would like to bring the emloyee
name back to a cell within Excel.

I have pasted the code from the intranet "search frame" below.
Regardless of the search criteria, the search results are always listed
in a set order (e.g. name, id, phone, dept., etc.) Thanks in advance
for any responses.

Alan


<html>
<head>
<title>Employee Directory</title>
<LINK rel="stylesheet" type="text/css" href="/css/mycompany.css">
</head>

<SCRIPT LANGUAGE="JavaScript">
<!--
var w2 = null;
function IHRISHelp(){
if (w2 != null)
w2.close();

w2=window.open("help.htm",
"Help","width=400,height=300,navbar=yes,resizable=yes");

}

function submitTheForm(){
if (document.form0.searchval.value.length > 0) {
document.form0.submit();
}
}

function MainNumbers(){
if (w2 != null)
w2.close();
w2=window.open("mainnumbers/MainNumbers.htm",
Click to expand...
"MainNumbers","width=600,height=350,scrollbars=yes,menubar=yes,navbar=yes,re
sizable=yes");
}

function LeadershipTeamReport(){
if (w2 != null)
w2.close();
w2=window.open("LeadershipTeamReport/LeadershipTeamReport.xls",
Click to expand...
"LeadershipTeamReport","width=600,height=350,scrollbars=yes,menubar=yes,navb
ar=yes,resizable=yes");
}

function PrintEmployeeList(){
parent.EmployeeList.focus();
parent.EmployeeList.print();
}
//-->
</SCRIPT>

<body class="body5" onLoad="document.forms[0].searchval.focus();">


<h3>Employee Directory</h3>

<form method="POST" action="listingFrame.asp" target="EmployeeList"
name="form0">
<table border="0">

<tr>
<td>Search For:<input type="text" name="searchval" size="20"
onKeyUp="submitTheForm()"></td>
<td align='left' valign='center'>Search By:
<select name="Search_Field" size="1">
<option VALUE="Name">Name</option>
<option VALUE="Empnum">Empl. Number</option>
<option VALUE="Phone">Phone Extension</option>
<option VALUE="Mail">Mail</option>
<option VALUE="BusinessUnit">Organization</option>
<option VALUE="Department">Department/Area</option>
<option VALUE="Job">Job Title</option>
<option VALUE="SupervisorName">Manager</option>
<option VALUE="Phonetic">Phonetic Last Name</option>
</SELECT></td>
</tr></table>
<table border="0" width="100%">
<tr>
<td>
<input type="submit" value="Search" name="Submit">&nbsp;
<input type="button" value="Print" name="Print"
onclick="PrintEmployeeList();">&nbsp;
<input type="submit" value="Export" name="Submit">&nbsp;
<!--<input type="button" value="Main Numbers" name="Main"
onclick="MainNumbers();">&nbsp;-->
</td>
</tr>
</table>
<input type="hidden" name="appType" value="full">
<hr>
</form>

</body>
Click to expand...
Click to expand...
 
A

acampbell012

Thanks. This worked perfectly. I am refreshing the query every time the
workbook is opened. The I.D. number is refreshed based on the
individual's network i.d. which I obtailn through a separate function.
Depends how you are refreshing your web query.
From code:
querystr=http://MyCompany/apps/empname/get_emp_name.asp?empnum=" &
range("IDNumber").value

On a WS:
Look into "parameter query" from the Excel help.

NickHK

Hello again,

I am reposting to my original request for some help. One of my
company's web developers helped me (I think) with the problem in my
original post. However, I have another question I am hoping somebody
may be able to help me with.

The following link, placed in a web query, successfully retrieves the
employee name based on the I.D. (830598). However, this value needs to
change for each user that opens my spreadsheet. I have the employee ID
stored in a variable elsewhere in my spreadsheet for each user that
opens the workbook. However, I don't know how to programatically alter
this query to include that stored value in the query.

Any help would be greatly appreciated.

http://MyCompany/apps/empname/get_emp_name.asp?empnum=830598


Hi,

I am attempting to retrieve employee information from my company's web
site (employee directory). The web page uses frames (I think). Working
directly from the web page, you enter data on the form, select the
search field (eg. empl. name, ID, phone number, etc.) to return the
results.

I am attempting to retrieve the employee name based on the employee ID
which i already have stored as a variable in Excel.

I am getting better at coding in Excel, but this is new ground for me
and any help would be appreciated. I would like to bring the emloyee
name back to a cell within Excel.

I have pasted the code from the intranet "search frame" below.
Regardless of the search criteria, the search results are always listed
in a set order (e.g. name, id, phone, dept., etc.) Thanks in advance
for any responses.

Alan


<html>
<head>
<title>Employee Directory</title>
<LINK rel="stylesheet" type="text/css" href="/css/mycompany.css">
</head>

<SCRIPT LANGUAGE="JavaScript">
<!--
var w2 = null;
function IHRISHelp(){
if (w2 != null)
w2.close();

w2=window.open("help.htm",
"Help","width=400,height=300,navbar=yes,resizable=yes");

}

function submitTheForm(){
if (document.form0.searchval.value.length > 0) {
document.form0.submit();
}
}

function MainNumbers(){
if (w2 != null)
w2.close();
w2=window.open("mainnumbers/MainNumbers.htm",
Click to expand...
"MainNumbers","width=600,height=350,scrollbars=yes,menubar=yes,navbar=yes,re
sizable=yes");
}

function LeadershipTeamReport(){
if (w2 != null)
w2.close();
w2=window.open("LeadershipTeamReport/LeadershipTeamReport.xls",
Click to expand...
"LeadershipTeamReport","width=600,height=350,scrollbars=yes,menubar=yes,navb
ar=yes,resizable=yes");
}

function PrintEmployeeList(){
parent.EmployeeList.focus();
parent.EmployeeList.print();
}
//-->
</SCRIPT>

<body class="body5" onLoad="document.forms[0].searchval.focus();">


<h3>Employee Directory</h3>

<form method="POST" action="listingFrame.asp" target="EmployeeList"
name="form0">
<table border="0">

<tr>
<td>Search For:<input type="text" name="searchval" size="20"
onKeyUp="submitTheForm()"></td>
<td align='left' valign='center'>Search By:
<select name="Search_Field" size="1">
<option VALUE="Name">Name</option>
<option VALUE="Empnum">Empl. Number</option>
<option VALUE="Phone">Phone Extension</option>
<option VALUE="Mail">Mail</option>
<option VALUE="BusinessUnit">Organization</option>
<option VALUE="Department">Department/Area</option>
<option VALUE="Job">Job Title</option>
<option VALUE="SupervisorName">Manager</option>
<option VALUE="Phonetic">Phonetic Last Name</option>
</SELECT></td>
</tr></table>
<table border="0" width="100%">
<tr>
<td>
<input type="submit" value="Search" name="Submit">&nbsp;
<input type="button" value="Print" name="Print"
onclick="PrintEmployeeList();">&nbsp;
<input type="submit" value="Export" name="Submit">&nbsp;
<!--<input type="button" value="Main Numbers" name="Main"
onclick="MainNumbers();">&nbsp;-->
</td>
</tr>
</table>
<input type="hidden" name="appType" value="full">
<hr>
</form>

</body>
Click to expand...
Click to expand...
Click to expand...
 
A

acampbell012

I am back with another question. I am querying a company web page for
the phone number of an employee based on the employee i.d number (code
below). The web site uses frames.

I am using the PostText property to load the employee number in to the
"searchval" input box and the Employee I.D. (empnum) as the selectable
"Search_Field" box. From other posts I have read, I am pointing to the
"listingFrame" frame in my URL using the required search variables.

The results return the first employee on the list (alphabetically). The
web form uses the "onkeyup" method and I am wondering if this is
interfering with the results.

I would appreciate any help. The web code for the searchframe and
listingframe are included in this thread.


Sub Employee_Phone()
Dim emplnum As String
Dim Thiscell As Range
Set Thiscell = ActiveCell
emplnum = Left(GetUserName, 6) 'function that provides the employee ID
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://MyCompany/apps/ihris/listingFrame.asp", _
Destination:=Thiscell)
.PostText = "searchval=" & emplnum
.PostText = "Search_Field=Empnum"
.Name = "Get Phone"
.FieldNames = True
.RowNumbers = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
NickHK said:
Depends how you are refreshing your web query.
From code:
querystr=http://MyCompany/apps/empname/get_emp_name.asp?empnum=" &
range("IDNumber").value

On a WS:
Look into "parameter query" from the Excel help.

NickHK

Hello again,

I am reposting to my original request for some help. One of my
company's web developers helped me (I think) with the problem in my
original post. However, I have another question I am hoping somebody
may be able to help me with.

The following link, placed in a web query, successfully retrieves the
employee name based on the I.D. (830598). However, this value needs to
change for each user that opens my spreadsheet. I have the employee ID
stored in a variable elsewhere in my spreadsheet for each user that
opens the workbook. However, I don't know how to programatically alter
this query to include that stored value in the query.

Any help would be greatly appreciated.

http://MyCompany/apps/empname/get_emp_name.asp?empnum=830598


Hi,

I am attempting to retrieve employee information from my company's web
site (employee directory). The web page uses frames (I think). Working
directly from the web page, you enter data on the form, select the
search field (eg. empl. name, ID, phone number, etc.) to return the
results.

I am attempting to retrieve the employee name based on the employee ID
which i already have stored as a variable in Excel.

I am getting better at coding in Excel, but this is new ground for me
and any help would be appreciated. I would like to bring the emloyee
name back to a cell within Excel.

I have pasted the code from the intranet "search frame" below.
Regardless of the search criteria, the search results are always listed
in a set order (e.g. name, id, phone, dept., etc.) Thanks in advance
for any responses.

Alan


<html>
<head>
<title>Employee Directory</title>
<LINK rel="stylesheet" type="text/css" href="/css/mycompany.css">
</head>

<SCRIPT LANGUAGE="JavaScript">
<!--
var w2 = null;
function IHRISHelp(){
if (w2 != null)
w2.close();

w2=window.open("help.htm",
"Help","width=400,height=300,navbar=yes,resizable=yes");

}

function submitTheForm(){
if (document.form0.searchval.value.length > 0) {
document.form0.submit();
}
}

function MainNumbers(){
if (w2 != null)
w2.close();
w2=window.open("mainnumbers/MainNumbers.htm",
Click to expand...
"MainNumbers","width=600,height=350,scrollbars=yes,menubar=yes,navbar=yes,re
sizable=yes");
}

function LeadershipTeamReport(){
if (w2 != null)
w2.close();
w2=window.open("LeadershipTeamReport/LeadershipTeamReport.xls",
Click to expand...
"LeadershipTeamReport","width=600,height=350,scrollbars=yes,menubar=yes,navb
ar=yes,resizable=yes");
}

function PrintEmployeeList(){
parent.EmployeeList.focus();
parent.EmployeeList.print();
}
//-->
</SCRIPT>

<body class="body5" onLoad="document.forms[0].searchval.focus();">


<h3>Employee Directory</h3>

<form method="POST" action="listingFrame.asp" target="EmployeeList"
name="form0">
<table border="0">

<tr>
<td>Search For:<input type="text" name="searchval" size="20"
onKeyUp="submitTheForm()"></td>
<td align='left' valign='center'>Search By:
<select name="Search_Field" size="1">
<option VALUE="Name">Name</option>
<option VALUE="Empnum">Empl. Number</option>
<option VALUE="Phone">Phone Extension</option>
<option VALUE="Mail">Mail</option>
<option VALUE="BusinessUnit">Organization</option>
<option VALUE="Department">Department/Area</option>
<option VALUE="Job">Job Title</option>
<option VALUE="SupervisorName">Manager</option>
<option VALUE="Phonetic">Phonetic Last Name</option>
</SELECT></td>
</tr></table>
<table border="0" width="100%">
<tr>
<td>
<input type="submit" value="Search" name="Submit">&nbsp;
<input type="button" value="Print" name="Print"
onclick="PrintEmployeeList();">&nbsp;
<input type="submit" value="Export" name="Submit">&nbsp;
<!--<input type="button" value="Main Numbers" name="Main"
onclick="MainNumbers();">&nbsp;-->
</td>
</tr>
</table>
<input type="hidden" name="appType" value="full">
<hr>
</form>

</body>
Click to expand...
Click to expand...
Click to expand...
 
N

NickHK

I don't really do web stuff so I'm not sure, but I doubt you are Posting 2
values in your query, only the second value, as the first would be over
written.
Depends what the server is expecting, but can you combine the PostText into
a single value
..PostText = "searchval=" & emplnum & "<SomeSeparator>Search_Field=Empnum"
Or may combine the whole lot in the URL you are connecting to.

NickHK

I am back with another question. I am querying a company web page for
the phone number of an employee based on the employee i.d number (code
below). The web site uses frames.

I am using the PostText property to load the employee number in to the
"searchval" input box and the Employee I.D. (empnum) as the selectable
"Search_Field" box. From other posts I have read, I am pointing to the
"listingFrame" frame in my URL using the required search variables.

The results return the first employee on the list (alphabetically). The
web form uses the "onkeyup" method and I am wondering if this is
interfering with the results.

I would appreciate any help. The web code for the searchframe and
listingframe are included in this thread.


Sub Employee_Phone()
Dim emplnum As String
Dim Thiscell As Range
Set Thiscell = ActiveCell
emplnum = Left(GetUserName, 6) 'function that provides the employee ID
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://MyCompany/apps/ihris/listingFrame.asp", _
Destination:=Thiscell)
.PostText = "searchval=" & emplnum
.PostText = "Search_Field=Empnum"
.Name = "Get Phone"
.FieldNames = True
.RowNumbers = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
NickHK said:
Depends how you are refreshing your web query.
From code:
querystr=http://MyCompany/apps/empname/get_emp_name.asp?empnum=" &
range("IDNumber").value

On a WS:
Look into "parameter query" from the Excel help.

NickHK

Hello again,

I am reposting to my original request for some help. One of my
company's web developers helped me (I think) with the problem in my
original post. However, I have another question I am hoping somebody
may be able to help me with.

The following link, placed in a web query, successfully retrieves the
employee name based on the I.D. (830598). However, this value needs to
change for each user that opens my spreadsheet. I have the employee ID
stored in a variable elsewhere in my spreadsheet for each user that
opens the workbook. However, I don't know how to programatically alter
this query to include that stored value in the query.

Any help would be greatly appreciated.

http://MyCompany/apps/empname/get_emp_name.asp?empnum=830598


(e-mail address removed) wrote:
Hi,

I am attempting to retrieve employee information from my company's web
site (employee directory). The web page uses frames (I think). Working
directly from the web page, you enter data on the form, select the
search field (eg. empl. name, ID, phone number, etc.) to return the
results.

I am attempting to retrieve the employee name based on the employee ID
which i already have stored as a variable in Excel.

I am getting better at coding in Excel, but this is new ground for me
and any help would be appreciated. I would like to bring the emloyee
name back to a cell within Excel.

I have pasted the code from the intranet "search frame" below.
Regardless of the search criteria, the search results are always listed
in a set order (e.g. name, id, phone, dept., etc.) Thanks in advance
for any responses.

Alan


<html>
<head>
<title>Employee Directory</title>
<LINK rel="stylesheet" type="text/css" href="/css/mycompany.css">
</head>

<SCRIPT LANGUAGE="JavaScript">
<!--
var w2 = null;
function IHRISHelp(){
if (w2 != null)
w2.close();

w2=window.open("help.htm",
"Help","width=400,height=300,navbar=yes,resizable=yes");

}

function submitTheForm(){
if (document.form0.searchval.value.length > 0) {
document.form0.submit();
}
}

function MainNumbers(){
if (w2 != null)
w2.close();
w2=window.open("mainnumbers/MainNumbers.htm",
Click to expand...
"MainNumbers","width=600,height=350,scrollbars=yes,menubar=yes,navbar=yes,re
sizable=yes");
}

function LeadershipTeamReport(){
if (w2 != null)
w2.close();
w2=window.open("LeadershipTeamReport/LeadershipTeamReport.xls",
Click to expand...
"LeadershipTeamReport","width=600,height=350,scrollbars=yes,menubar=yes,navb
ar=yes,resizable=yes");
}

function PrintEmployeeList(){
parent.EmployeeList.focus();
parent.EmployeeList.print();
}
//-->
</SCRIPT>

<body class="body5" onLoad="document.forms[0].searchval.focus();">


<h3>Employee Directory</h3>

<form method="POST" action="listingFrame.asp" target="EmployeeList"
name="form0">
<table border="0">

<tr>
<td>Search For:<input type="text" name="searchval" size="20"
onKeyUp="submitTheForm()"></td>
<td align='left' valign='center'>Search By:
<select name="Search_Field" size="1">
<option VALUE="Name">Name</option>
<option VALUE="Empnum">Empl. Number</option>
<option VALUE="Phone">Phone Extension</option>
<option VALUE="Mail">Mail</option>
<option VALUE="BusinessUnit">Organization</option>
<option VALUE="Department">Department/Area</option>
<option VALUE="Job">Job Title</option>
<option VALUE="SupervisorName">Manager</option>
<option VALUE="Phonetic">Phonetic Last Name</option>
</SELECT></td>
</tr></table>
<table border="0" width="100%">
<tr>
<td>
<input type="submit" value="Search" name="Submit">&nbsp;
<input type="button" value="Print" name="Print"
onclick="PrintEmployeeList();">&nbsp;
<input type="submit" value="Export" name="Submit">&nbsp;
<!--<input type="button" value="Main Numbers" name="Main"
onclick="MainNumbers();">&nbsp;-->
</td>
</tr>
</table>
<input type="hidden" name="appType" value="full">
<hr>
</form>

</body>
Click to expand...
Click to expand...
Click to expand...
 
A

acampbell012

Thanks for your response. It may be that I have not coded the second
search criteria correctly. The first input is a text box for entering
the search value. The second value (Empnum) is selected from a
drop-down list on the search frame. The values to search on are:

I will try you suggestion and post back again if successful.
Unfortunately, there is not a lot of postings on this subject that I
can draw from to build my query. Thanks again.

Alan

I don't really do web stuff so I'm not sure, but I doubt you are Posting 2
values in your query, only the second value, as the first would be over
written.
Depends what the server is expecting, but can you combine the PostText into
a single value
.PostText = "searchval=" & emplnum & "<SomeSeparator>Search_Field=Empnum"
Or may combine the whole lot in the URL you are connecting to.

NickHK

I am back with another question. I am querying a company web page for
the phone number of an employee based on the employee i.d number (code
below). The web site uses frames.

I am using the PostText property to load the employee number in to the
"searchval" input box and the Employee I.D. (empnum) as the selectable
"Search_Field" box. From other posts I have read, I am pointing to the
"listingFrame" frame in my URL using the required search variables.

The results return the first employee on the list (alphabetically). The
web form uses the "onkeyup" method and I am wondering if this is
interfering with the results.

I would appreciate any help. The web code for the searchframe and
listingframe are included in this thread.


Sub Employee_Phone()
Dim emplnum As String
Dim Thiscell As Range
Set Thiscell = ActiveCell
emplnum = Left(GetUserName, 6) 'function that provides the employee ID
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://MyCompany/apps/ihris/listingFrame.asp", _
Destination:=Thiscell)
.PostText = "searchval=" & emplnum
.PostText = "Search_Field=Empnum"
.Name = "Get Phone"
.FieldNames = True
.RowNumbers = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
NickHK said:
Depends how you are refreshing your web query.
From code:
querystr=http://MyCompany/apps/empname/get_emp_name.asp?empnum=" &
range("IDNumber").value

On a WS:
Look into "parameter query" from the Excel help.

NickHK

Hello again,

I am reposting to my original request for some help. One of my
company's web developers helped me (I think) with the problem in my
original post. However, I have another question I am hoping somebody
may be able to help me with.

The following link, placed in a web query, successfully retrieves the
employee name based on the I.D. (830598). However, this value needs to
change for each user that opens my spreadsheet. I have the employee ID
stored in a variable elsewhere in my spreadsheet for each user that
opens the workbook. However, I don't know how to programatically alter
this query to include that stored value in the query.

Any help would be greatly appreciated.

http://MyCompany/apps/empname/get_emp_name.asp?empnum=830598


(e-mail address removed) wrote:
Hi,

I am attempting to retrieve employee information from my company's web
site (employee directory). The web page uses frames (I think). Working
directly from the web page, you enter data on the form, select the
search field (eg. empl. name, ID, phone number, etc.) to return the
results.

I am attempting to retrieve the employee name based on the employee ID
which i already have stored as a variable in Excel.

I am getting better at coding in Excel, but this is new ground for me
and any help would be appreciated. I would like to bring the emloyee
name back to a cell within Excel.

I have pasted the code from the intranet "search frame" below.
Regardless of the search criteria, the search results are always listed
in a set order (e.g. name, id, phone, dept., etc.) Thanks in advance
for any responses.

Alan


<html>
<head>
<title>Employee Directory</title>
<LINK rel="stylesheet" type="text/css" href="/css/mycompany.css">
</head>

<SCRIPT LANGUAGE="JavaScript">
<!--
var w2 = null;
function IHRISHelp(){
if (w2 != null)
w2.close();

w2=window.open("help.htm",
"Help","width=400,height=300,navbar=yes,resizable=yes");

}

function submitTheForm(){
if (document.form0.searchval.value.length > 0) {
document.form0.submit();
}
}

function MainNumbers(){
if (w2 != null)
w2.close();
w2=window.open("mainnumbers/MainNumbers.htm",

"MainNumbers","width=600,height=350,scrollbars=yes,menubar=yes,navbar=yes,re
sizable=yes");
}

function LeadershipTeamReport(){
if (w2 != null)
w2.close();
w2=window.open("LeadershipTeamReport/LeadershipTeamReport.xls",

"LeadershipTeamReport","width=600,height=350,scrollbars=yes,menubar=yes,navb
ar=yes,resizable=yes");
}

function PrintEmployeeList(){
parent.EmployeeList.focus();
parent.EmployeeList.print();
}
//-->
</SCRIPT>

<body class="body5" onLoad="document.forms[0].searchval.focus();">


<h3>Employee Directory</h3>

<form method="POST" action="listingFrame.asp" target="EmployeeList"
name="form0">
<table border="0">

<tr>
<td>Search For:<input type="text" name="searchval" size="20"
onKeyUp="submitTheForm()"></td>
<td align='left' valign='center'>Search By:
<select name="Search_Field" size="1">
<option VALUE="Name">Name</option>
<option VALUE="Empnum">Empl. Number</option>
<option VALUE="Phone">Phone Extension</option>
<option VALUE="Mail">Mail</option>
<option VALUE="BusinessUnit">Organization</option>
<option VALUE="Department">Department/Area</option>
<option VALUE="Job">Job Title</option>
<option VALUE="SupervisorName">Manager</option>
<option VALUE="Phonetic">Phonetic Last Name</option>
</SELECT></td>
</tr></table>
<table border="0" width="100%">
<tr>
<td>
<input type="submit" value="Search" name="Submit">&nbsp;
<input type="button" value="Print" name="Print"
onclick="PrintEmployeeList();">&nbsp;
<input type="submit" value="Export" name="Submit">&nbsp;
<!--<input type="button" value="Main Numbers" name="Main"
onclick="MainNumbers();">&nbsp;-->
</td>
</tr>
</table>
<input type="hidden" name="appType" value="full">
<hr>
</form>

</body>
Click to expand...
Click to expand...
Click to expand...
 
A

acampbell012

I applied your suggestion and have included the code change I made to
the "PostText" properties. I used the ampersand as the separator. It
worked perfectly. The code now retrieves the correct web information
based on the input box and selection box criteria. I hope this helps
others with similar web query questions. Thanks again.

Alan

.PostText = "searchval=" & emplnum & _
"&Search_Field=Empnum"

I don't really do web stuff so I'm not sure, but I doubt you are Posting 2
values in your query, only the second value, as the first would be over
written.
Depends what the server is expecting, but can you combine the PostText into
a single value
.PostText = "searchval=" & emplnum & "<SomeSeparator>Search_Field=Empnum"
Or may combine the whole lot in the URL you are connecting to.

NickHK

I am back with another question. I am querying a company web page for
the phone number of an employee based on the employee i.d number (code
below). The web site uses frames.

I am using the PostText property to load the employee number in to the
"searchval" input box and the Employee I.D. (empnum) as the selectable
"Search_Field" box. From other posts I have read, I am pointing to the
"listingFrame" frame in my URL using the required search variables.

The results return the first employee on the list (alphabetically). The
web form uses the "onkeyup" method and I am wondering if this is
interfering with the results.

I would appreciate any help. The web code for the searchframe and
listingframe are included in this thread.


Sub Employee_Phone()
Dim emplnum As String
Dim Thiscell As Range
Set Thiscell = ActiveCell
emplnum = Left(GetUserName, 6) 'function that provides the employee ID
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://MyCompany/apps/ihris/listingFrame.asp", _
Destination:=Thiscell)
.PostText = "searchval=" & emplnum
.PostText = "Search_Field=Empnum"
.Name = "Get Phone"
.FieldNames = True
.RowNumbers = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
NickHK said:
Depends how you are refreshing your web query.
From code:
querystr=http://MyCompany/apps/empname/get_emp_name.asp?empnum=" &
range("IDNumber").value

On a WS:
Look into "parameter query" from the Excel help.

NickHK

Hello again,

I am reposting to my original request for some help. One of my
company's web developers helped me (I think) with the problem in my
original post. However, I have another question I am hoping somebody
may be able to help me with.

The following link, placed in a web query, successfully retrieves the
employee name based on the I.D. (830598). However, this value needs to
change for each user that opens my spreadsheet. I have the employee ID
stored in a variable elsewhere in my spreadsheet for each user that
opens the workbook. However, I don't know how to programatically alter
this query to include that stored value in the query.

Any help would be greatly appreciated.

http://MyCompany/apps/empname/get_emp_name.asp?empnum=830598


(e-mail address removed) wrote:
Hi,

I am attempting to retrieve employee information from my company's web
site (employee directory). The web page uses frames (I think). Working
directly from the web page, you enter data on the form, select the
search field (eg. empl. name, ID, phone number, etc.) to return the
results.

I am attempting to retrieve the employee name based on the employee ID
which i already have stored as a variable in Excel.

I am getting better at coding in Excel, but this is new ground for me
and any help would be appreciated. I would like to bring the emloyee
name back to a cell within Excel.

I have pasted the code from the intranet "search frame" below.
Regardless of the search criteria, the search results are always listed
in a set order (e.g. name, id, phone, dept., etc.) Thanks in advance
for any responses.

Alan


<html>
<head>
<title>Employee Directory</title>
<LINK rel="stylesheet" type="text/css" href="/css/mycompany.css">
</head>

<SCRIPT LANGUAGE="JavaScript">
<!--
var w2 = null;
function IHRISHelp(){
if (w2 != null)
w2.close();

w2=window.open("help.htm",
"Help","width=400,height=300,navbar=yes,resizable=yes");

}

function submitTheForm(){
if (document.form0.searchval.value.length > 0) {
document.form0.submit();
}
}

function MainNumbers(){
if (w2 != null)
w2.close();
w2=window.open("mainnumbers/MainNumbers.htm",

"MainNumbers","width=600,height=350,scrollbars=yes,menubar=yes,navbar=yes,re
sizable=yes");
}

function LeadershipTeamReport(){
if (w2 != null)
w2.close();
w2=window.open("LeadershipTeamReport/LeadershipTeamReport.xls",

"LeadershipTeamReport","width=600,height=350,scrollbars=yes,menubar=yes,navb
ar=yes,resizable=yes");
}

function PrintEmployeeList(){
parent.EmployeeList.focus();
parent.EmployeeList.print();
}
//-->
</SCRIPT>

<body class="body5" onLoad="document.forms[0].searchval.focus();">


<h3>Employee Directory</h3>

<form method="POST" action="listingFrame.asp" target="EmployeeList"
name="form0">
<table border="0">

<tr>
<td>Search For:<input type="text" name="searchval" size="20"
onKeyUp="submitTheForm()"></td>
<td align='left' valign='center'>Search By:
<select name="Search_Field" size="1">
<option VALUE="Name">Name</option>
<option VALUE="Empnum">Empl. Number</option>
<option VALUE="Phone">Phone Extension</option>
<option VALUE="Mail">Mail</option>
<option VALUE="BusinessUnit">Organization</option>
<option VALUE="Department">Department/Area</option>
<option VALUE="Job">Job Title</option>
<option VALUE="SupervisorName">Manager</option>
<option VALUE="Phonetic">Phonetic Last Name</option>
</SELECT></td>
</tr></table>
<table border="0" width="100%">
<tr>
<td>
<input type="submit" value="Search" name="Submit">&nbsp;
<input type="button" value="Print" name="Print"
onclick="PrintEmployeeList();">&nbsp;
<input type="submit" value="Export" name="Submit">&nbsp;
<!--<input type="button" value="Main Numbers" name="Main"
onclick="MainNumbers();">&nbsp;-->
</td>
</tr>
</table>
<input type="hidden" name="appType" value="full">
<hr>
</form>

</body>
Click to expand...
Click to expand...
Click to expand...
 
N

NickHK

Glad you got it working, as it was something of a guess on my part.

NickHK

I applied your suggestion and have included the code change I made to
the "PostText" properties. I used the ampersand as the separator. It
worked perfectly. The code now retrieves the correct web information
based on the input box and selection box criteria. I hope this helps
others with similar web query questions. Thanks again.

Alan

.PostText = "searchval=" & emplnum & _
"&Search_Field=Empnum"

I don't really do web stuff so I'm not sure, but I doubt you are Posting
2
values in your query, only the second value, as the first would be over
written.
Depends what the server is expecting, but can you combine the PostText
into
a single value
.PostText = "searchval=" & emplnum &
"<SomeSeparator>Search_Field=Empnum"
Or may combine the whole lot in the URL you are connecting to.

NickHK

I am back with another question. I am querying a company web page for
the phone number of an employee based on the employee i.d number (code
below). The web site uses frames.

I am using the PostText property to load the employee number in to the
"searchval" input box and the Employee I.D. (empnum) as the selectable
"Search_Field" box. From other posts I have read, I am pointing to the
"listingFrame" frame in my URL using the required search variables.

The results return the first employee on the list (alphabetically). The
web form uses the "onkeyup" method and I am wondering if this is
interfering with the results.

I would appreciate any help. The web code for the searchframe and
listingframe are included in this thread.


Sub Employee_Phone()
Dim emplnum As String
Dim Thiscell As Range
Set Thiscell = ActiveCell
emplnum = Left(GetUserName, 6) 'function that provides the employee ID
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://MyCompany/apps/ihris/listingFrame.asp", _
Destination:=Thiscell)
.PostText = "searchval=" & emplnum
.PostText = "Search_Field=Empnum"
.Name = "Get Phone"
.FieldNames = True
.RowNumbers = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "2"
.WebPreFormattedTextToColumns = False
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
NickHK wrote:
Depends how you are refreshing your web query.
From code:
querystr=http://MyCompany/apps/empname/get_emp_name.asp?empnum=" &
range("IDNumber").value

On a WS:
Look into "parameter query" from the Excel help.

NickHK

Hello again,

I am reposting to my original request for some help. One of my
company's web developers helped me (I think) with the problem in my
original post. However, I have another question I am hoping
somebody
may be able to help me with.

The following link, placed in a web query, successfully retrieves
the
employee name based on the I.D. (830598). However, this value needs
to
change for each user that opens my spreadsheet. I have the
employee ID
stored in a variable elsewhere in my spreadsheet for each user that
opens the workbook. However, I don't know how to programatically
alter
this query to include that stored value in the query.

Any help would be greatly appreciated.

http://MyCompany/apps/empname/get_emp_name.asp?empnum=830598


(e-mail address removed) wrote:
Hi,

I am attempting to retrieve employee information from my
company's web
site (employee directory). The web page uses frames (I think). Working
directly from the web page, you enter data on the form, select
the
search field (eg. empl. name, ID, phone number, etc.) to return
the
results.

I am attempting to retrieve the employee name based on the
employee ID
which i already have stored as a variable in Excel.

I am getting better at coding in Excel, but this is new ground
for me
and any help would be appreciated. I would like to bring the
emloyee
name back to a cell within Excel.

I have pasted the code from the intranet "search frame" below.
Regardless of the search criteria, the search results are always listed
in a set order (e.g. name, id, phone, dept., etc.) Thanks in
advance
for any responses.

Alan


<html>
<head>
<title>Employee Directory</title>
<LINK rel="stylesheet" type="text/css" href="/css/mycompany.css">
</head>

<SCRIPT LANGUAGE="JavaScript">
<!--
var w2 = null;
function IHRISHelp(){
if (w2 != null)
w2.close();

w2=window.open("help.htm",
"Help","width=400,height=300,navbar=yes,resizable=yes");

}

function submitTheForm(){
if (document.form0.searchval.value.length > 0) {
document.form0.submit();
}
}

function MainNumbers(){
if (w2 != null)
w2.close();
w2=window.open("mainnumbers/MainNumbers.htm",

"MainNumbers","width=600,height=350,scrollbars=yes,menubar=yes,navbar=yes,re
sizable=yes");
}

function LeadershipTeamReport(){
if (w2 != null)
w2.close();
w2=window.open("LeadershipTeamReport/LeadershipTeamReport.xls",

"LeadershipTeamReport","width=600,height=350,scrollbars=yes,menubar=yes,navb
ar=yes,resizable=yes");
}

function PrintEmployeeList(){
parent.EmployeeList.focus();
parent.EmployeeList.print();
}
//-->
</SCRIPT>

<body class="body5"
onLoad="document.forms[0].searchval.focus();">


<h3>Employee Directory</h3>

<form method="POST" action="listingFrame.asp"
target="EmployeeList"
name="form0">
<table border="0">

<tr>
<td>Search For:<input type="text" name="searchval" size="20"
onKeyUp="submitTheForm()"></td>
<td align='left' valign='center'>Search By:
<select name="Search_Field" size="1">
<option VALUE="Name">Name</option>
<option VALUE="Empnum">Empl. Number</option>
<option VALUE="Phone">Phone Extension</option>
<option VALUE="Mail">Mail</option>
<option VALUE="BusinessUnit">Organization</option>
<option VALUE="Department">Department/Area</option>
<option VALUE="Job">Job Title</option>
<option VALUE="SupervisorName">Manager</option>
<option VALUE="Phonetic">Phonetic Last Name</option>
</SELECT></td>
</tr></table>
<table border="0" width="100%">
<tr>
<td>
<input type="submit" value="Search" name="Submit">&nbsp;
<input type="button" value="Print" name="Print"
onclick="PrintEmployeeList();">&nbsp;
<input type="submit" value="Export" name="Submit">&nbsp;
<!--<input type="button" value="Main Numbers" name="Main"
onclick="MainNumbers();">&nbsp;-->
</td>
</tr>
</table>
<input type="hidden" name="appType" value="full">
<hr>
</form>

</body>
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

run report on web page, import to excel 20
VBA and Internet Explorer 9
linking data from a web to access 6
Frontpage 2000 - Going to a new page from a value in a drop-down box 0
Click Web Page Image 2
The form name changes everytime we open the page 1
Pick data from web page (no query) 14
ASP/Html checkbox convert to true/false for MS Access backend 2

Top