I am writing a application for a the local watershed association. It is a native plant guide for them to distribute. Anyway, my goal is to make it so they can update the program without having to recompile. My solution for this is to use an excel file that they can store all the data, since all of folks there use excel on a daily basis.

The problem that I've run into is the question of how I tell my program to search the excel file and display data from specific cells, without using ADO.

I would appreciate any help I can get.

Why don't you want to use ADO??

Anyway, you *can* "script" Excel by loading the application in via CreateObject. But that's much harder than using ADO.

I was hoping to avoid requiring people to have ActiveX on their systems to run the program. But given that I am still fairly knew and have yet to explore ADO, my assumption could be wrong about that. Please tell me if I am.

So how would do that with CreatObject and, for the sake of options, how would I do it with ADO?

Well just so you know, excel.exe is an activex exe. That is why you can automate it via other programs...

Time to use your friends (yahoo, google, ask, answers, bing) to search for the following tutorials..

vb6 ado tutorial (then see http://www.connectionstrings.com for the DSN Less connection string you will need)
vb6 automating excel tutorial



Good Luck

The code is never going to work on any operating system other than Windows, and *ALL* windows machines automatically have ActiveX.

Also, all Windows machines will have ADO installed, but only people who have Office installed will have Excel. So scripting Excel is much much more limiting than using ADO.

CAUTION: ADO can only read full sheets or pre-defined ranges from an Excel file. You don't have the ability to work with individual cells as you can with Excel.

Am I right to understand, then, that ADO might not be a good choice for reading an excel file that might have data added to it over time?

Also, another question, I don't have MS Office so I was wondering if it is possible to use Open Office for testing purposes?


My goal is to have an excel file that is provided with program, that contains the data, in this case information on plants native to Maryland, and can have data added to it and there by add to the data displayed by the program. In short my plan is to have the excel file serve as means for the organization that I am writing this for to update the program's library of plant information without having to recompile the program.

The organization I'm writing this program for is the local watershed association and the people who work and volunteer there, with the exception of myself, have zero knowledge of programing so I am trying to write the electronic plant guide in such a way that any person can update the information contained within it.

The best way I could think of for doing this was to use an excel file to store the information and tell the program how to, dynamically, search through and read the information stored inside the xls file. The advantage of this given the people involved, is that they work with excel all the time, including the volunteers that help them around the office. Also, given that I am currently volunteering there due to lack of employment, I want to make sure whatever I give them they can update themselves without needing any programing knowledge what so ever so that they can continue updating and distributing the electronic plant guide long after I have moved on.

So there you have it. A pretty detailed explanation of my specific goals, in case it has any baring on the approaches available to me.

Thanks guys, I feel like I'm gaining a lot of valuable knowledge from all of this.

I don't think that applies, at all.

What I mainly meant by that is that some people will use a single Excel sheet and chop it up into pieces, putting essentially many sub-tables into a single sheet. They might even have a graph in the middle of the sheet, etc., etc.

*MOST* simple excel sheets are going to work just fine.

If the entire sheet is a single unified table, perhaps with a list of column names in the first row, it will work just fine.

A couple of minor problems to look out for:

If a column contains mixed data--especially numbers and text or dates and text--and if the first 8 rows contain *only* numbers (or only dates) then when you get to a row that has non-numeric (or non-date) data, it will *not* be readable. And easy solution to this is to create a dummy first row in the table (maybe pretend it is a sub-heading) that has text in it. Then the entire column will be seen as text (even the numbers/dates). But that's okay, your VB code can easily test the text using ISDATE() or ISNUMERIC() and convert the data as needed.

There's a 90% chance you'll never see the above problem, but it's the most annoying one I know of. [This all happens because the OLEDB JET driver that you will use with your ADODB.Connection object scans only the first 8 rows in attempting to decide the type of a column. There are some very complex ways to make it scan *all* rows, but my "sub-header" trick is much much simpler.]

Absolutely no knowledge of Open Office, but if it can produce and XLS file that Access can read, surely that same XLS file will be readable via ADO.

I would *strongly* suspect that your project will be successful, going this route. The things I've warned about are the oddities, not the normal things you will encounter.

Caution about how you open the spreadsheet. Indeed pay attention to http://www.connectionstrings.com/excel for what to do. I see they mention the IMEX=1 solution to the mixed column problem I mentioned. I have had mixed success with that. But with any luck you won't need to address the problem, anyway.

Notice the "" to produce the need single " marks in the resultant string.

And if the first row does *NOT* have column titles, then you will need to do HDR=No and then the column names are autogenerated. But you might just as well use column numbers, then.