ImportMixedTypes from excel

[David F]

Hi All,

I am trying to import a column from excel 8.0 using DAO with vb5. The problem I am having is that the column I am trying to import has a mixture of numerals and text.

As far as I can tell I have the excel initialization settings of 'ImportMixedTypes' set to text, and 'TypeGuessRows' set to 8 and have also tried 2 and 1.

This is what happens...

12345.001
23456.001L
34567.001

It will read 12345.001 and 23456.001L but returns null for 34567.001

Any ideas?

Cheers,
David

[michelle]

hello david,

I had the same problem with MS-Access. Was a field filled in or not!

Maybe the next Vb functions will help.
Pls inform me the result.

IsNummeric
IsDate
IsEmpty
IsNull

Nice regards,

Michelle.

[Buzby]

Having done a lot of messing around trying to import from Excel I would advise you copy the spreadsheet, paste it into Access, and then work on it.. believe me it's easier in the long run.

According to Microsoft ('cos I phoned and asked when I had problems) - Excel looks down the column the specified number of rows (as set by TypeGuessRows) to work out what type of column it is. If it finds more Text cells than Numeric cells, the column becomes a text field and any numbers in the cells, get this, become NULL !! If it finds more numeric cells than text cells, the column becomes a numeric field and any text in the cells become NULL also.
The closest I got to a solution before I gave up was to create a new row at the top of the spreadsheet with each cell 'masked' appropriately; eg AAAAA for a text field and 9999 for a numeric field (make sure you use cell formatting to FORCE them to be a number field etc), and then set the TypeGuessRows to 1.
However, this still didn't work 100% of the time so after about 2 weeks of work trying to get information of a mixed type out of sodding Excel I copied the lot to Access and found it much easier.. you have been warned...

If you want further information let me know.



------------------
Mark "Buzby" Beeton
VB Developer
[email protected]




[This message has been edited by Buzby (edited 02-03-2000).]

[David F]

The cells have data in them (I wish it was something that simple, I have done that more than once... ), the jet engine for importing excel documents has the setting 'ImportMixedTypes' and according to the help file when this is set to 'text' it is meant to convert all cells in the column to text if there is more than one type present.

However this has not worked properly as it worked for one change of type but then returned null for the third.

When I entered a text field in the first cell and set the 'TypeGuessRows' to 1 it returns null for the numeric cells, setting the formatting of the column to text had no effect either.

I set TypeGuessRows to 200 and ordered the column, to see if it would perform one change of data type like before, but I can not get it to repeat the behaviour it was performing before, it is returning null for the first instance of the new type.

I am thinking to run a macro to append a character to the data and then remove it in vb or see if I can have a different report produced without these mixed data types.

The excel sheet is automatically produced by another program and will need to be imported daily by the user of my program, so I am stuck with using excel.

Thanks for the help..

UPDATE:

I gave up on trying with the excel file and was able to obtain a text file version - much easier!

[This message has been edited by David F (edited 02-03-2000).]

[Buzby]

You see this;

"according to the help file when this is set to 'text' it is meant to convert all cells in the column to text if there is more than one type present. "

This is a lie from Microsoft! It will NOT convert anything that is flagged as a number in a text column - it will turn them into NULL.
Very helpful. Thanks Microsoft.

You're better off using a text file. Believe me. I've pulled my hair out over this very problem for ages!!



------------------
Mark "Buzby" Beeton
VB Developer
[email protected]