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.]
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.
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.