Results 1 to 3 of 3
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import value from Excel to Access (2000 sr 1)

    I have this macro it import value from excel sheet and put the realted value in the access table.
    Have only a problem....;-)
    The value in:
    .Fields("DATA") = Range("O" & r).Value
    is a text format and in this column is present variuos date.
    The filed DATA in acces is Date Format
    Alll is ok if the date is a rela date for example 12/01/2005 but if the date is 00/00/0000 i have error: not type comapible....
    How to solve this problem to maitain the date format 00/00/000?
    See for exaple the image

    Sub ADO_ANAGRAFICA()
    Dim num As Integer
    Sheets("ANAGRAFE").Select
    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, n As Long
    Set cn = New ADODB.Connection
    ' **** Substitute the correct path ************
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2
    '**********************************************
    Set rs = New ADODB.Recordset
    rs.Open "ANAGRAFICA", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rs.Index = "MATRICOLA"
    r = 5 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    With rs
    If Not rs.BOF Then
    rs.MoveFirst
    End If
    rs.Seek Array(Range("A" & r)), adSeekFirstEQ
    If rs.EOF = True Then
    .AddNew ' create a new record
    'add values to each field in the record
    .Fields("MATRICOLA") = Range("A" & r).Value
    .Fields("FILIALE") = Range("B" & r).Value
    .Fields("NOMINATIVO") = Range("C" & r).Value
    .Fields("DATA_NASCITA") = Range("D" & r).Value
    .Fields("DATA_ASSUNZ") = Range("E" & r).Value
    .Fields("CALEND") = Range("F" & r).Value
    .Fields("FILIALE1") = Range("G" & r).Value
    .Fields("UBICAZIONE") = Range("H" & r).Value
    .Fields("COD_UFFICIO") = Range("I" & r).Value
    .Fields("DESCR_UFFICIO") = Range("J" & r).Value
    .Fields("QUALIF") = Range("K" & r).Value
    .Fields("DESCR_QUALIF") = Range("L" & r).Value
    .Fields("MANSIONE") = Range("M" & r).Value
    .Fields("DESCR_MANSIONE") = Range("N" & r).Value
    .Fields("DATA") = Range("O" & r).Value
    .Fields("CATEGORIA") = Range("P" & r).Value
    .Fields("DESCR_CATEG") = Range("Q" & r).Value
    .Fields("SPORT_TIMBR") = Range("R" & r).Value
    .Fields("TIMBRATURE") = Range("S" & r).Value
    .Fields("DESCR_TIMBR") = Range("T" & r).Value
    .Fields("ORARIO_LAV") = Range("U" & r).Value
    .Fields("ANZIANIT_FERIE") = Range("V" & r).Value
    .Fields("PART_TIME") = Range("W" & r).Value
    .Fields("RIP_COMP") = Range("X" & r).Value
    .Fields("DIR_SIND") = Range("Y" & r).Value
    .Fields("GRUPPO") = Range("Z" & r).Value
    .Update ' stores the new record
    End If
    End With
    r = r + 1 ' next row
    Loop

    Debug.Print n & " records added."

    rs.Close
    rs.Open "SELECT Count(MATRICOLA) As Cnt FROM ANAGRAFICA", cn, adOpenKeyset, adLockOptimistic, adCmdText
    Range("F2") = rs!cnt

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import value from Excel to Access (2000 sr 1)

    Dates in Access start at 01/01/100, so 00/00/0000 is not a valid date. You could change 00/00/0000 to a blank (null) value, or to 01/01/100 (or any date you like):

    If Range("O" & r).Value = "00/00/0000" Then
    .Fields("DATA") = Null
    Else
    .Fields("DATA") = Range("O" & r).Value
    End If

    If you prefer to set a date, change

    .Fields("DATA") = Null

    to

    .Fields("DATA") = DateValue("01/01/100")

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import value from Excel to Access (2000 sr 1)

    NATURALLY....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •