'Purpose: to import a specific sheet and range of a workbook
'Usage: ImportSpreadsheet
"tblExcel","c:\temp\Book1.xls","Sheet1","C5
7"
'True in the options means the top row are field headers
Public Sub ImportSpreadsheet(strTable As String, strFileName As String, _
strWorkSheet As String, strRange As String)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
strTable, strFileName, True, strWorkSheet & "!" & strRange
End Sub
======================================================
Since Excel is not a database, using "mixed" data types in a column causes a
problem when Access has to "guess" which one to use. Access examines the
first 15 rows (or so) and makes a guess. If you want to "force" Access to
guess a text data type then make the first entry in that column a text
value. The reason you get errors is that the first 15 rows are all numeric
and then the text data shows up after Access has already guessed the data
type to be Numeric.
The way to make a text value in your data of numbers is to include a
character!
e.g. 123A
========================================================
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.
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:
Jet (MS Access UI): 'Text' data type
DDL: VARCHAR(255)
ADO: adWChar ('a null-terminated Unicode character string')
Note that this is distinct from:
Jet (MS Access UI): 'Memo' data type
DDL: N/A
ADO: adLongVarWChar ('a long null-terminated Unicode string value')
ImportMixedTypes=Text will curtail text at 255 characters as 'Memo' is
cast as 'Text'. For a column to be recognized as 'Memo', majority type
must be detected, meaning the majority of rows detected must contain
255 or more characters.
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 to get Jet to detect whether a 'mixed
types' situation exists or use it to 'trick' Jet into detecting a
certaint data type as being the majority type. In the event of a
'mixed types' situation being detected, use ImportMixedTypes to tell
Jet to either use the majority type or coerce all values as 'Text'
(max 255 characters).
