Import Excel Data via ADO

  • Thread starter Thread starter Ben
  • Start date Start date
B

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
 
Set the Excel cell to Text. Also are you importing the spreadsheet as tab
delimited or comma delimited?
 
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
 
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
 
Back
Top