Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Retrieve data from company intranet page
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 129
Default Retrieve data from company intranet page
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
<titleEmployee 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,scrol lbars=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();"


<h3Employee Directory</h3

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

<tr
<tdSearch 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 129
Default web Query with variable or cell value
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_em...?empnum=830598


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
<titleEmployee 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,scrol lbars=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();"


<h3Employee Directory</h3

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

<tr
<tdSearch 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default web Query with variable or cell value
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

wrote in message
oups.com...
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_em...?empnum=830598


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
<titleEmployee 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,scrol lbars=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();"


<h3Employee Directory</h3

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

<tr
<tdSearch 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 129
Default web Query with variable or cell value
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.

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

wrote in message
oups.com...
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_em...?empnum=830598


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
<titleEmployee 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,scrol lbars=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();"


<h3Employee Directory</h3

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

<tr
<tdSearch 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 129
Default web Query with variable or cell value
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

wrote in message
oups.com...
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_em...?empnum=830598


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
<titleEmployee 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,scrol lbars=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();"


<h3Employee Directory</h3

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

<tr
<tdSearch 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 4,391
Default web Query with variable or cell value
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 & "<SomeSeparatorSearch_Field=Empnum"
Or may combine the whole lot in the URL you are connecting to.

NickHK

wrote in message
ps.com...
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

wrote in message
oups.com...
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_em...?empnum=830598


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
<titleEmployee 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,scrol lbars=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();"


<h3Employee Directory</h3

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

<tr
<tdSearch 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




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 129
Default web Query with variable or cell value
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 a

<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

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


NickHK wrote:
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 & "<SomeSeparatorSearch_Field=Empnum"
Or may combine the whole lot in the URL you are connecting to.

NickHK

wrote in message
ps.com...
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

wrote in message
oups.com...
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_em...?empnum=830598


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
<titleEmployee 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,scrol lbars=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();"


<h3Employee Directory</h3

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

<tr
<tdSearch 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



Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 129
Default web Query with variable or cell value
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"


NickHK wrote:
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 & "<SomeSeparatorSearch_Field=Empnum"
Or may combine the whole lot in the URL you are connecting to.

NickHK

wrote in message
ps.com...
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

wrote in message
oups.com...
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_em...?empnum=830598


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
<titleEmployee 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,scrol lbars=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();"


<h3Employee Directory</h3

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

<tr
<tdSearch 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



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 415
Default web Query with variable or cell value
Glad you got it working, as it was something of a guess on my part.

NickHK


groups.com...
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"


NickHK wrote:
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 &
"<SomeSeparatorSearch_Field=Empnum"
Or may combine the whole lot in the URL you are connecting to.

NickHK

wrote in message
ps.com...
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

wrote in message
oups.com...
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_em...?empnum=830598


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
<titleEmployee 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,scrol lbars=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();"


<h3Employee Directory</h3

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

<tr
<tdSearch 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





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing data over intranet creativeops Excel Discussion (Misc queries) 0 May 22nd 06 11:43 PM
need macro to page break between company names automatically Ms Magers Excel Programming 2 May 4th 06 04:50 PM
pull data for a company with data in diff cells multiple wrkshts kcoachbiggs Excel Worksheet Functions 0 March 8th 06 09:24 PM
Design Q: XL XP, 97 and multiple users submitting data over intranet Keith R[_3_] Excel Programming 0 November 5th 03 09:44 PM
How to get data from Access Database in Intranet Bill Li Excel Programming 1 July 10th 03 07:04 AM


All times are GMT +1. The time now is 10:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement