With VB6 I am opening an Excel file to query the contents using ADO. I am
then using the contents of the worksheet to create a format file that is used
for a bulk insert into SQL Server. We designed the program this way so that
users can use a spreadsheet to import data in any format they want and
process the data based on values set in the spreadsheet. This keeps us from
having to design new tables and format files for new record layouts which
come down pretty often. Everything works fine usually. There is just one
perculiarity that is happening with the Excel file. Sometimes, the first
cell in the last row of the worksheet returns a blank, even though there is
definately data in the cell - a number that I am converting to a long format.
The conversion fails because the program sees an empty string ("").

I have tried retyping the data in the cell when this happens. ALSO -
Changing the cell format (to general, text, numeric) and Copying and pasting
the entire worksheet.

Usually, to get it to work, I export the entire worksheet to a text file and
then re-import the file and then my program will read that last cell.

The program is also reading all of the other cells on the last row. It is
just the first cell of the last row that it has problems with and it happens
on maybe half the files we create for the program. The number of rows in the
worksheet is different depending on how many columns are in a particular
layout.

Can anyone tell me why this is happening and what I can do to prevent it?

Thanks,

Ron

Ron wrote:

Sometimes, the first
cell in the last row of the worksheet returns a blank, even though
there is definately data in the cell - a number that I am
converting to a long format. The conversion fails because
the program sees an empty string ("").

Usually when a cell containing data is read as null when reading via
Jet, the cause is a mixed data types situation e.g. the data type for
the column has been determined by Jet as 'numeric' and any cells
containing 'text' values are nulled. For details, see:

http://www.dicks-blog.com/archives/2...ed-data-types/
Jamie.

--