Loading Excel into Access

[sqlmann]

Tried to load excel data into access database using access modules.
Tried the follwing steps to load
Step 1: OPen excel using ADODB provider _working fine without any issue
Step 2 : Store the value into recordset -Like "Select * from [PDF file$](Excl sheet name)

Step 3: Read each record and update it into Acces database

Problem is :

In the Excel sheet, one column has String value and some numeric value like 'P3','p4' and 3 ok ?. This program inserts only string value and if the value is numeric it inserts as null into database.

Pls advise.. how to solve this?

 

[dhookom]

Share your code.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Hi,

The NUMBER 3 and the CHARACTER 3 are to totally different values in a computer.

Why would you have TEXT & NUMBERS in the same column in Excel? I know its possible, but do you expect to do arithmetic on these numbers? Your NUMBERS ought to be converted to TEXT in Excel.

Run some code like this to convert...

Sub Num2Chr()
'select any cell in the column you want to convert
'number to character
  Dim r As Range
  Set r = Selection
  With r.Parent.UsedRange
    For Each r In .Range(.Cells(.Row, r.Column), .Cells(.Row + .Rows.Count - 1, r.Column))
      With r
        If IsNumeric(.Value) Then .Value = "'" & .Value
      End With
    Next
  End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Skip, just to know, doesn't the @ FormatNumber suffice ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

PHV,

I doubt that just formatting the cells changes anything.

As a rule, format only changes the DISPLAY of values. Dates, leading and trialing zeros, dashes, in formats really change nothing in the underlying value, which is what the query is accessing.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]