Visual Basic Programming
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Programming Languages - More > Visual Basic Programming
Reload this Page

Need help searching and extracting data from excel

Discuss Need help searching and extracting data from excel in the Visual Basic Programming forum on Dev Shed.
Need help searching and extracting data from excel Visual Basic Programming forum discussing VB specific programming information. Quickly prototype and build applications with this robust and simple language.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here
  #1  
Old April 23rd, 2008, 02:01 PM
wbrown98 wbrown98 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2008
Posts: 2 wbrown98 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 17 m 45 sec
Reputation Power: 0
Need help searching and extracting data from excel
Howdy All,

I have an excel spreadsheet with multiple sheets and I am trying to search the spreadsheet by a string and display the contents of the entire row that contains the matching string. I also need to update the spreadsheet also. I would like to do this via a web interface. Here is what I have thus far.

The data is formatted like so

4566 Q5 pcbox1 pcbox1 pcbox2
<HTML>

<HEAD>

<TITLE>Satellite Command Database Tool</TITLE>

<SCRIPT LANGUAGE="VBScript">

<!-- Instruct non-IE browsers to skip over VBScript modules.



Option Explicit

Sub cmdSubmit_OnClick

' Check to see if the user entered anything.

If (Len(document.cmdsearch.cmd1.value) = 0) Then

MsgBox "You must enter a command number before submitting."

Exit Sub

End If



document.cmdsearch.submit

End Sub

-->

</SCRIPT>

</HEAD>

<BODY>

<H2>Command Database Lookup Utility</H2>


<FORM NAME="cmdsearch">

<TABLE>

<TR>

<TD>Enter Command Number</TD>

<TD><INPUT TYPE="Text" NAME="cmd1" SIZE="10">

<TR>

<TD><INPUT TYPE="Button" NAME="cmdSubmit" VALUE="Submit" ></TD>

<TD></TD>

</TR>

</TABLE>

</FORM>

</BODY>

</HTML>
Last edited by wbrown98 : April 23rd, 2008 at 02:08 PM. Reason: detailing data formatting
Reply With Quote
  #2  
Old April 23rd, 2008, 02:24 PM
medialint's Avatar
medialint medialint is offline
spirit duplicator
Click here for more information.
  
Join Date: Apr 2004
Location: \\Firecrate\
Posts: 12,299 medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)  Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 4 Months 3 Weeks 4 h 42 m 21 sec
Reputation Power: 2547
client side vbscript in HTML to search ...

Quote:
I also need to update the spreadsheet also.


... and update ...

Excel ...

on ... the ... server ... side?
__________________
medialint.com

"Energy has the opportunity to change the climate if it's done right." - Sen. John Ensign, R-Nev. (quoted out of context)
Reply With Quote
  #3  
Old April 23rd, 2008, 02:49 PM
wbrown98 wbrown98 is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Apr 2008
Posts: 2 wbrown98 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 17 m 45 sec
Reputation Power: 0
Quote:
Originally Posted by medialint
client side vbscript in HTML to search ...



... and update ...

Excel ...

on ... the ... server ... side?


Yes, the spreadsheet is on the server side. I would like to be able to update it from any pc on our intranet.
Reply With Quote
  #4  
Old April 23rd, 2008, 03:48 PM
zynder's Avatar
zynder zynder is offline
Not much of a contributor
Dev Shed Novice (500 - 999 posts)
  
Join Date: Aug 2006
Location: Hidden
Posts: 800 zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)  Folding Points: 140447 Folding Title: Super Ultimate Folder - Level 1Folding Points: 140447 Folding Title: Super Ultimate Folder - Level 1Folding Points: 140447 Folding Title: Super Ultimate Folder - Level 1Folding Points: 140447 Folding Title: Super Ultimate Folder - Level 1Folding Points: 140447 Folding Title: Super Ultimate Folder - Level 1Folding Points: 140447 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 1 Week 2 Days 22 h 6 m 32 sec
Reputation Power: 647
Send a message via Yahoo to zynder
Why Excel? Can't you use Access for this?
Reply With Quote
  #5  
Old April 23rd, 2008, 04:48 PM
medialint's Avatar
medialint medialint is offline
spirit duplicator
Click here for more information.
  
Join Date: Apr 2004
Location: \\Firecrate\
Posts: 12,299 medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)  Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 4 Months 3 Weeks 4 h 42 m 21 sec
Reputation Power: 2547
In any event I think you'll need a plan B. Even assuming all your users have direct file server access to the spreadsheet the probability of this breaking on the first day is about 100%. As far as getting it to work client side through a normal web server (regardless that it's intra or Inter) it's not going to work that way.
Reply With Quote
  #6  
Old April 23rd, 2008, 05:45 PM
Frank20 Frank20 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2006
Posts: 248 Frank20 User rank is Sergeant (500 - 2000 Reputation Level)Frank20 User rank is Sergeant (500 - 2000 Reputation Level)Frank20 User rank is Sergeant (500 - 2000 Reputation Level)Frank20 User rank is Sergeant (500 - 2000 Reputation Level)Frank20 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 2 Days 9 h 24 m 7 sec
Reputation Power: 9
Yes, it can be put together with not much work. My objection would be the number of users making queries to the .xls file. A modern database is designed to be available to a lot of users making almost-simultaneous queries against it. In comparable ways Excel isn't there yet.

Is there a reason why the spreadsheet per se is time-sensitive, or calculation intensive, enough that it can't otherwise be scheduled at regular intervals to automatically sync up with a database?
Last edited by Frank20 : April 23rd, 2008 at 05:52 PM.
Reply With Quote
  #7  
Old April 23rd, 2008, 05:51 PM
medialint's Avatar
medialint medialint is offline
spirit duplicator
Click here for more information.
  
Join Date: Apr 2004
Location: \\Firecrate\
Posts: 12,299 medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)  Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 4 Months 3 Weeks 4 h 42 m 21 sec
Reputation Power: 2547
Quote:
Originally Posted by Frank20
Yes, it can be put together with not much work.


Accessing a server side .xls with client side VB script in a browser ... !?
Reply With Quote
  #8  
Old April 23rd, 2008, 05:52 PM
Frank20 Frank20 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2006
Posts: 248 Frank20 User rank is Sergeant (500 - 2000 Reputation Level)Frank20 User rank is Sergeant (500 - 2000 Reputation Level)Frank20 User rank is Sergeant (500 - 2000 Reputation Level)Frank20 User rank is Sergeant (500 - 2000 Reputation Level)Frank20 User rank is Sergeant (500 - 2000 Reputation Level) 
Time spent in forums: 2 Days 9 h 24 m 7 sec
Reputation Power: 9
Quote:
Originally Posted by medialint
Accessing a server side .xls with client side VB script in a browser ... !?


Yes. But it's done through ASP. Perl also has a heavyweight VBA object-model capacity.
Last edited by Frank20 : April 23rd, 2008 at 06:04 PM.
Reply With Quote
  #9  
Old April 24th, 2008, 01:51 PM
zynder's Avatar
zynder zynder is offline
Not much of a contributor
Dev Shed Novice (500 - 999 posts)
  
Join Date: Aug 2006
Location: Hidden
Posts: 800 zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)zynder User rank is Brigadier General (60000 - 70000 Reputation Level)  Folding Points: 140447 Folding Title: Super Ultimate Folder - Level 1Folding Points: 140447 Folding Title: Super Ultimate Folder - Level 1Folding Points: 140447 Folding Title: Super Ultimate Folder - Level 1Folding Points: 140447 Folding Title: Super Ultimate Folder - Level 1Folding Points: 140447 Folding Title: Super Ultimate Folder - Level 1Folding Points: 140447 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 1 Week 2 Days 22 h 6 m 32 sec
Reputation Power: 647
Send a message via Yahoo to zynder
This is PITA. Think of another approach that doesn't involve excel on it.
Reply With Quote
  #10  
Old April 24th, 2008, 03:21 PM
medialint's Avatar
medialint medialint is offline
spirit duplicator
Click here for more information.
  
Join Date: Apr 2004
Location: \\Firecrate\
Posts: 12,299 medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)medialint User rank is General 23rd Grade (Above 100000 Reputation Level)  Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1Folding Points: 232541 Folding Title: Super Ultimate Folder - Level 1
Time spent in forums: 4 Months 3 Weeks 4 h 42 m 21 sec
Reputation Power: 2547
Quote:
Originally Posted by Frank20
Yes. But it's done through ASP.


In other words ... server side ;-)

Quote:
This is PITA. Think of another approach that doesn't involve excel on it.


I tend to agree ... even if you get server side code to maintain a spreadsheet it's going to be inefficient. Excel is not a database and shouldn't be treated as such (row 65,537 anyone ...?) it would be much more practical to maintain the data in a real database and export data to excel on demand or scheduled ...
Comments on this post
zynder agrees!
Last edited by medialint : April 24th, 2008 at 03:25 PM.
Reply With Quote
Reply

Viewing: Dev Shed Forums > Programming Languages - More > Visual Basic Programming > Need help searching and extracting data from excel

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump