CODEWITHSUNDEEP
databaseexcelms-access
AKKAweb
AKKAweb

Reputation: 3807

Access Database - Importing Data From Excel Sheet Does Not Work Correctly Even With IMEX=1

I am creating an ACCESS Application where one of the steps is to import data from an Excel Spreadsheet into DB. Everything works great, however, if there are different types of values (ie. string, numbers), ACCESS only imports one type.

  1. The Excel Spreadsheet has a column with the following data

enter image description here:

When I use the following ACCESS code, only the numbers in this case gets extracted from the sheet. The other ones like CCH is not extracted. If lets say the first cell has CCH as its value, only these would get extracted, the numbers would not:

Dim ConnectString As String
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & FilePath & ";" & "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
objConnection.Open ConnectString
objRecordset.Open "Select * FROM [Sheet1$]", objConnection, adOpenStatic, adLockOptimistic

Do Until objRecordset.EOF

        Code = objRecordset.Fields.ITEM(0)

        ' ===== More code Here ==== '
Loop

How do I go about fixing this issue?

Thanks for your help

Upvotes: 1

Views: 460

Answers (1)

Fionnuala
Fionnuala

Reputation: 91366

Why not TransferSpreadsheet ?

 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, _
      "SheetX", "C:\docs\XL.xlsx", True, "Sheet3$"

Otherwise, you may need to tamper with the registry.

Upvotes: 2

Related Questions