Import Excel Data via ADO

[Ben]

I'm importing data from an Excel spreadsheet in to Access using ADO.
Everything appears to work apart from when a cell in Excel contains a
comma-separated list (e.g. 1,3,5,6,7). If the cell only contains one number
the value is imported but if the cell contains a list then the ADO recordset
value is Null (as determined by a MsgBox). The Excel spreadsheet data
format for the cell is set to General.

Any suggestions?

Ben

 

[GVaught]

Set the Excel cell to Text. Also are you importing the spreadsheet as tab
delimited or comma delimited?

 

[Ben]

Thanks for that. I did try that but it didn't work. I found that I need to
insert a ' infront of the numbers/list for it to work.

I am importing by setting up a ADO connection to the Excel file and
selecting the cell range from the worksheet. I am then looping through each
row in the worksheet using rs.fields("<column_name>"). The cell in question
contains a comma delimited list which now has an Excel data type of text.

Regards,
Ben

 

[Arni Laugdal]

Ben,
By default ADO scans first 8 rows to guess the data type
for each column in your Excel. Specifying MAXSCANROWS=0
in the Extended Properties of the connection string
should force ADO to scan all rows before choosing a data
type. MAXSCANROWS can also be set to fixed value (between
0-16) in the registry by changing the TypeGuessRows value.

Dim cnnExcel As New ADODB.Connection
With cnnExcel
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties").Value = _
"Excel 8.0; HDR=yes; IMEX=1;"
.Open "C:\Test.xls"
End With

Sometime is this not enough and then must one sett
following key in the registy to 0:
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Exce
l/TypeGuessRows

Enjoy,
Arni Laugdal,
Office 2000 Master Instructor
www.til.is/msaccess