Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ron
 
Posts: n/a
Default Using Jet to read excel file returns blank for last cell - sometim

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
  #2   Report Post  
onedaywhen
 
Posts: n/a
Default

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.

--

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cannot open a file that Excel says is open Plodhia Excel Discussion (Misc queries) 2 December 7th 04 02:43 AM
Excel File Appears Blank Tony C. Excel Discussion (Misc queries) 2 December 7th 04 01:59 AM
conditional formatting blank cell TREK5200 Excel Discussion (Misc queries) 1 December 6th 04 03:23 AM
Network access to file - read only property AL Excel Discussion (Misc queries) 1 December 2nd 04 02:22 AM
No Smart Tag help: just a blank "MS Excel Help" window - Excel 2003 Ian Ripsher Excel Discussion (Misc queries) 0 November 26th 04 09:43 PM


All times are GMT +1. The time now is 10:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"