Unable to import or link to an Excel file

  • Thread starter Thread starter Herbert Chan
  • Start date Start date
H

Herbert Chan

Hello,

I have an Excel file of data and I want to link it from Access. The Excel
table just has a header row and then below are record rows. There are no
blank rows within the data table.

I've tried and tried and I was only able to link up to midway of the table.
I have a lot of other similar Excel files and I did not encounter any
problems linking to those files. I've tried to import the file, and I was
still only able to import up to midway in the table (same result as linking
to it). I've tried defining the whole database table a name within Excel
and then tried to link/import the named range of the Excel file, but still
without success.

I've tried exporting the Excel file to a dbf and then importing it. I could
indeed get access to all records in the table. Some fields, however, became
blank, while there are actually contents for those fields in the Excel file.

I am using Access 97 and the file is an Excel 97 file. (I have Access 2000
and have used it to link to the same file. It gets access to all the rows.
But I want to use the computer that has Access 97 to do all the time
consuming queries.)

What should I do??

Herbert
 
Herbert,

Not sure what the problem is, but it sounds like it's mare of an Excel file
problem than an Access one. Have you tried copying all the data from the
Excel file to a new one and importing that one?

HTH,
Nikos
 
Herbert Chan said:
I have an Excel file of data and I want to link it from Access. The Excel
table just has a header row and then below are record rows. There are no
blank rows within the data table.

I've tried and tried and I was only able to link up to midway of the table.
Click to expand...

Check your registry settings. The relevant registry keys (for Jet 4.0)
are in:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/

The ImportMixedTypes registry key is always read. You can test this by
changing the key to ImportMixedTypes=OneDayWhen and trying to use the
ISAM: you get the error, ' Invalid setting in Excel key of the Engines
section of the Windows Registry.' The only valid values are:

ImportMixedTypes=Text
ImportMixedTypes=Majority Type

Data type is determined column by column.

For ImportMixedTypes='Majority Type' means a certain number of rows
(more on this later) in each column are scanned and the data types are
counted. Both a cell's value and format are used to determine data
type. The majority data type (i.e. the one with the most rows) decides
the overall data type for the entire column. There's a bias in favor
of numeric in the event of a tie. Rows from any minority data types
found that can't be cast as the majority data type will be returned
with a null value.

For ImportMixedTypes=Text, the data type for the whole column will be
adWChar ('a null-terminated Unicode character string' i.e. Windows'
REG_SZ).

But how many rows are scanned for each column before is decided that
mixed types exist? There is a second registry Key, TypeGuessRows. This
can be a value from 0-16 (decimal). A value from 1 to 16 inclusive is
the number of rows to scan. A value of zero means all rows will be
scanned.

In summary, use TypeGuessRows for Jet to detect whether a mixed types
situation exists and ImportMixedTypes to tell Jet what to do in the
event of a mixed types situation. So try:

TypeGuessRows=0
ImportMixedTypes=Text

--
 
I am using Access 97 and the file is an Excel 97 file. (I have Access
2000 and have used it to link to the same file. It gets access to all
the rows. But I want to use the computer that has Access 97 to do all
the time consuming queries.)

What should I do??

Herbert
Click to expand...

It could be that in the Excel97 sheet you have not the same cell format
for the whole length of a column.
I had it once that a column showing numbers actually changed from numeric
in the top cells to right alined text (numbers) in the cells below that.
This is difficult to discover as the difference is not visible at first
sight.

It also causes an export to .dbf to stop putting numbers in the
applicable field from that point of change.

Cheerio!

Dodo
 
Back
Top