Is it possible to use Javascript to pull data from a cell within an Excel spreadsheet when the notes form is displayed via a browser ? IE 5.5 or above.
Is it possible to use Javascript to pull data from a cell within an Excel spreadsheet when the notes form is displayed via a browser ? IE 5.5 or above.
For clarification, the term ?user? refers to the browser user. The term ?customer? refers to the notes designer's customer who wants the button to do the automation instead of depending on the user to copy/paste the correct email address from the Excel spreadsheet to the appropriate field on the main form.
Currently, a browser user displays the Excel spreadsheet via a button that opens another browser window on top of the main browser window. This secondary window is a document that contains an attached Excel spreadsheet. The user then dble clicks on the Excel spreadsheet to display the data. The user looks for specific data in the Excel spreadsheet ? usually a server name or empid. They then copy/paste the associated email address from another cell on the same row as the server name or empid in the Excel spreadsheet to a field on the form which is displayed in the primary browser window. The user then closes Excel which automatically closes the secondary browser window.
I need a button that a browser user will click on that will open the excel spreadsheet and prompt the user for the empid or the server name. It will then search for the associated email address in the excel spreadsheet and copy/paste this information into a field on the primary form displayed in the primary browser window.
The Excel spreadsheet cannot be an imbedded attachment on the main form that is displayed in the primary window of the browser. The customer needs to update the Excel spreadsheet within the notes client whenever needed. The customer will not be provided designer capabilities to detach and reatttach Excel spreadsheets on a form. They are only provided the capability to update the Excel spreadsheet via a document. The Excel spreadsheet is maintained in the notes client by the notes designer's customer and the document resides in a view the customer can access via the notes client. The url to display this document is what is used when clicking on the button on the main form in the primary window to display the Excel spreadsheet in the secondary window via the browser.
If Javascript cannot do this, what can? The environment is R5.0.9. The Excel spreadsheet can be rather large. The data in this spreadsheet is not available on any other source. No one wants to take responsibility for putting this data in another database type resource, nor do they want to maintain it in a different DB resource.
Software/Hardware used:
Software/Hardware used:
ASKED:
January 11, 2005 9:28 AM
UPDATED:
January 13, 2005 11:00 AM
Answer Wiki:
Generally speaking, you can use Javascript to populate data from one window to another. In the terms of a window opened from another window, it's fairly easy by understanding the proper references. The first window is refered to as the window.opener.document and the second window is strickly the window.document. So to populate you would use the syntax - assume field is called EmailTX:
// first get the value of the source
vEmail = document.forms[0].EmailTX.value;
// pass the value to the opener
window.opener.document.forms[0].Email.value = vEmail
Now as far as taking from the Excel component, I'm not that familar with the Excel object in the browser. I would researching into utilizing named ranges in the Excel file for referencing the fields I would pull from. Then using Javascript, you can reference the element and pass it as shown above. Here's the systex to reference an named element:
// assume the element is named EmailTXCell
// get handle to element
vEmail = document.getElementById('EmailTXCell').value;
// pass the value to the opener
window.opener.document.forms[0].Email.value = vEmail
Hope this helps!
John
To see all answers submitted to the Answer Wiki: View Answer History.
If the server is a MS Windows machine you could always send the empid from the browser as a CGI ?OpenForm&empid=1234 then retrieve the correct e-mail address from the spreadsheet using the Excel ODBC from the WebQueryClose agent.
If the server is a MS Windows machine you could always send the empid from the browser as a CGI ?OpenForm&empid=1234 then retrieve the correct e-mail address from the spreadsheet using the Excel ODBC from the WebQueryClose agent, and re-post.
I think you should be able to do this using VBScript…