PDA

View Full Version : Solved: Search Excel File and populate Web page



austenr
05-14-2007, 12:28 PM
Hi,

I have an excel file that resides on a server. I need to be able to have a search box on the web page that the user can enter the search criteria. The matching entry should be located in column A of the excel file and the information returned in columns D, F, G and H to the web page. Anyone know how to go about this without CGI, PHP, etc. Thanks

malik641
05-14-2007, 07:02 PM
I don't know much about this subject...but what about VB Script? Is there a way you can implement that? I'm sure it would be pretty easy after that.

austenr
05-14-2007, 08:31 PM
Sure I could use that. Problem is, I don't know how to get started.

malik641
05-15-2007, 07:37 PM
Ok, here's what I found.

Here's how to open an excel file and read some values. I'm not sure how to specify sheets, but I believe it only uses Sheet1...though I could be wrong. In any case, check this out:

<html>
<head>
</head>
<body>

<script type="text/vbscript">
Dim objExcel
Dim objWorkbook

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\VBScript\TEST.xls")

intRow = 1

Do Until objExcel.Cells(intRow,1).Value = ""
document.write("A" & intRow & ": " & objExcel.Cells(intRow, 1).Value)
document.write("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;")
document.write("B" & intRow & ": " & objExcel.Cells(intRow, 2).Value)
document.write("<br>")
intRow = intRow + 1
Loop

objExcel.Quit
</script>

</body>
</html>
And that code was a combination of the following sites (and what I know previously about HTML...which isn't much!):
http://www.cruto.com/resources/vbscript/vbscript-examples/
*with the one above...particularly: http://www.cruto.com/resources/vbscript/vbscript-examples/office/excel/
http://www.w3schools.com/html/default.asp

I still don't know how you would implement this for multiple users. But I do believe getting the file from the server shouldn't be too hard..just change "C:\" to your server's address "\\ServerName\Directory\TEST.xls" or whatever.

I've provided the example txt file and html file and xls file that I was playing with.

I hope this helps a little!

austenr
05-16-2007, 08:17 AM
Thanks Joseph. I was wondering to make this a lot easier, could this be redone as such:

1. Have a button a web page that opens an excel file
2. Have an input box asking for the user to enter a value to search for in column A.
3. Populate a list box with the values in columns B and C when the search item is found. And, an error message if the search criteria is not found.

I am going to work on what you sent me to see if I can get it to work. In the meantime, the above would create a good work around until I can get it up and running.

malik641
05-16-2007, 08:56 AM
I would just have a Criteria Text box on the web page and a button to perform the search. Then have the "found" values placed in a table.

I guess the way it would/could work (with only working with HTML) is to either have a new page pop-up with the found values or to have the page reload with the value of the search textbox and use it in the beginning of the procedure.

...not sure how to do that though.....(finding the value in the text box and placing code for the button to refresh the page).

austenr
05-16-2007, 09:51 AM
OK, I've come up with this work around temporally with a small glitch. On the attached WB I want the information to populate the User Form before it is shown. However, I can't seem to get the values to populate.

The user enter the search value and if found, it should populate the form with the following values:

Textbox1 = column D
TextBox2 = column H
TextBox3 = column I

malik641
05-16-2007, 10:37 AM
Hang on I'll be right back with your example of how I would do it.

malik641
05-16-2007, 12:25 PM
Ok. Here's a working example of how I would go about this.

I tried to comment as much as possible. But I've got to get back to work now!!

Anyway, check it out :thumb

austenr
05-16-2007, 01:22 PM
Works like a charm. Thanks Joseph.

malik641
05-16-2007, 01:30 PM
No problem austenr :)

Note that some of the code is unnecessary. I will post back later with a revised version :thumb

pravin
10-09-2011, 01:55 AM
Ok. Here's a working example of how I would go about this.

I tried to comment as much as possible. But I've got to get back to work now!!

Anyway, check it out :thumb

Sorry, where is the working example?
I have similar requirement, but want search result to be displayed on web page instead of opening an excel for the user.