CODEWITHSUNDEEP
ms-accessexcelvba
user2497624
user2497624

Reputation: 159

Import from excel to access

I am having some troubles with a VBA code that should import excel data to my access database. When i run the code, i get a RunTime error "Runtime error 1004 Application-defined or object-defined error".

With wks
    'arrêter l'importation lorsque l'on rencontre une case vide
    While .Range(A & i).Value <> ""
    '(où pKeyCol représente la colonne et i la ligne)

        If (cnt = 10) Then
'pour éviter les messages lors de l'ajout des enregistrements
DoCmd.SetWarnings False
        End If

        'condition de remplissage de la table => eviter les doublons
        'si l'enregistrement existe déjà dans la table destination,
        'on passe à la ligne suivante sans l'importer
        If DCount("*", acTable, pKey & " LIKE '" & .Range(A & i).Value & "'") = 0 Then

            'requête SQL (ajouter autant de champs que nécessaire)
            cSQL = "INSERT INTO " & acTable & " ( [champ1], [champ2], [champ3], [champ5], [champ6], [champ11],[champ14], [champ15], [champ18],[champ20], [champ21], [champ22],[champ23], [champ24]) VALUES (" & Chr(34) & .Range("A" & i) & Chr(34) & "," & Chr(34) & .Range("F" & i) & Chr(34) & "," & Chr(34) & .Range("AI" & i) & Chr(34) & "," & Chr(34) & .Range("X" & i) & Chr(34) & ", " & Chr(34) & .Range("H" & i) & Chr(34) & "," & Chr(34) & .Range("K" & i) & Chr(34) & "," & Chr(34) & .Range("R" & i) & Chr(34) & "," & Chr(34) & .Range("S" & i) & Chr(34) & "," & Chr(34) & .Range("AC" & i) & Chr(34) & "," & Chr(34) & .Range("AD" & i) & Chr(34) & "," & Chr(34) & .Range("AE" & i) & Chr(34) & "," & Chr(34) & .Range("AF" & i) & Chr(34) & "," & Chr(34) & .Range("AG" & i) & Chr(34) & "," & Chr(34) & .Range("AH" & i) & Chr(34) & ");"
            'exemple avec les colonnes E et G

            'exécute la requète
            DoCmd.RunSQL cSQL

        End If

        'on incrémente la variable i pour passer à la ligne suivante
        i = i + 1
        cnt = cnt + 1
    Wend

End With

I think this is the section responsible. I think the problem might be because of champ in the cSQL = part . I have coded this partly in a french environment . So this might be the problem.

Upvotes: 0

Views: 343

Answers (1)

Andy G
Andy G

Reputation: 19367

Unless A is a variable then it should be:

.Range("A" & i)

which becomes .Range("A1") for example.

If i is initially 0 then this would create the error.

acTable is the name of a pre-existing Access enumeration so should not be used as a variable name (an identifier). It is a string so I would use strTable or sTable.

Upvotes: 2

Related Questions