Excel file (xls) opened as database

[Raggart]

I'm having problems reading certain values from an excel sheet when it is opened as a database. I am using DAO and I open the file with OpenDatabase. (connection string: "Excel 8.0;")

However, when I read values from a certain column, I get null, even when there is a value. At first I thought I had the bad index for the column, but it's not it. For some dark, unknown reason, VB doesn't treat whatever's in this column as data if there's a colon in the cell. For example:

3
4
4, 5, 7
6

The third cell (4, 5, 7) would be skipped and my program would see Null instead of "4, 5, 7" in there. All other cells are okay. I tried changing the text in a cell to plain text (e.g.: "testing"), but that didn't do the trick. I also tried changing the cell format to "text" instead of "standard", but that didn't work either. I tried saving the file as a DBase IV file and open it that way, but I can't seem to open in with DAO (without using an ODBC, which I can't at the moment). The problem affects only one particular column, all others (even some containing similar data) are read perfectly well.

So now I'm fresh out of options. If anyone has any idea about this, please let me know!!!

In the meanwhile, I'll check Microsoft's site to see if this a known problem or just a problem with the way I'm trying to handle the data.

Thanks,

Raggart

[Raggart]

I resolved the problem. Well, rather, Microsoft did. Their article (Q194124) on the subject describes a perfect workaround. I have tried it and it works with my data. Sorry for any inconvenience this thread might have caused.



Raggart (who can resume work after one full day of frustration)