Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > VB How-To
|
VB How-To Ask your "How do I do this with VB?" questions in this forum.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB How-To section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 2nd, 2010, 06:56 PM
Registered User
 
Join Date: Jun 2010
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
Default How do I read data from excel?

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.
 
Old June 2nd, 2010, 09:37 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old June 2nd, 2010, 09:52 PM
Registered User
 
Join Date: Jun 2010
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
Default

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?
 
Old June 3rd, 2010, 01:35 AM
Authorized User
 
Join Date: Mar 2009
Posts: 49
Thanks: 0
Thanked 8 Times in 8 Posts
Default

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 Following User Says Thank You to vb5prgrmr For This Useful Post:
Karegador (June 3rd, 2010)
 
Old June 3rd, 2010, 03:28 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old June 3rd, 2010, 03:55 PM
Registered User
 
Join Date: Jun 2010
Posts: 4
Thanks: 2
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Old Pedant View Post

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.

Last edited by Karegador; June 3rd, 2010 at 03:57 PM..
 
Old June 3rd, 2010, 09:16 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Quote:
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?
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.]

Quote:
Also, another question, I don't have MS Office so I was wondering if it is possible to use Open Office for testing purposes?
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.

Code:
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fullPathToXlsFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
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.
The Following User Says Thank You to Old Pedant For This Useful Post:
Karegador (June 4th, 2010)





Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I read data from excel? Karegador Excel VBA 1 June 3rd, 2010 04:17 AM
How to read data from an excel file diegoblin Beginning VB 6 6 August 31st, 2009 11:04 PM
Read data from Ms Excel 2007 senpark15 C# 2005 3 June 21st, 2008 02:35 AM
how to read data from sybase to excel thillaiarasu SQL Language 2 April 20th, 2007 01:34 AM
Read Data from Excel File kiwibey ASP.NET 2.0 Professional 1 December 5th, 2006 11:27 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.