Need Serious Help In Connection

[warrenquek]

Hi all, im currently doin a school project and im facing problems to
set constraints to the coding connection to excel..

Example:
i hav a spreadsheet call SUMMARY
1 of the column is called Status
There are 3 different Status (Closed, Open, Void)

i wan to display the rows where Status is Open

i try this but it cant work

Select * From [SUMMARY$] where Status = Open

Select * From [SUMMARY$] where [SUMMARY$P = Open]

anyone of you know what is the correct coding to only take information
where the Status is Open??

Thanks in advance to whoever can help..

 

[sevenhalo]

Quick couple of questions...

Is the code 'within' the excel application?
If not, are you trying to run SQL queries against excel?

 

[warrenquek]

Re: Reply

Oh ya..forget to mention wad language im using..im using visual basic in visual studio .NET to code to connect to excel..these codes are not in the excel..

 

[warrenquek]

by the way, i use oledbconnection to excel with extended 8.0..im currently using excel 2003 and visual studio 2003..

im creating a standalone program to allow user to get to the information they want within a few clicks..save time..cuz if i use vb.net to open the excel spreadsheet, it wil tk abt 10mins or so because the spreadsheet has too many data and it has quite a number of macro in it..

 

[sevenhalo]

As far as I know, excel doesn't have a direct sql interface. I can show you how to open excel applications in vb.net and find the information you're looking for; but I don't think there's a nice, convenient sqlConn, sqlCmd, sqlDataReader solution for this without scraping the file to a structured database and querying that (if you don't have an instance of SQL server running, look into http://www.mysql.com/. Not 'entirely' the same, I think only the Agent is missing, but they have tools to replace it).

It might take some time, so I'm not going to slap together an example for you unless you want it.

Edit: Ok, I have a feeling I know what you're trying to do when you're reading from excel. It "is" possible, but since excel spreadsheets aren't structured and indexed like databases; there's no real way to speed up the search without jimmy-rigging the spreadsheet with custom indexes. It's not the most efficient way to do things (once a day, you would need to schedule a process to reformat the excel sheet with your custom indexing, and if your normal queries take 10 minutes; it might take close to an hour before it's finished. Even after all that, it still may take a full minute or two for each request).

You could also export the excel sheet to XML and search that. XML is a little easier than excel to work with in VS 2003, but once again, it's not indexed and the search is line by line like it is in excel. You'd still run into long search times, but the plus side is; there's alot less element layers to dive through. Instead of working with a Application-->Workbook-->Worksheet-->Cell; it would just be a text document and VS has ALOT of really nice controls to stream it in.

Plus, if you decide to use a database later on; XML is a relatively clean dump into tables. Never hurts to plan ahead.