1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72
| Option Compare Database
Option Explicit
Function ImportXL()
Dim xlPath As String
Dim wsName As String
Dim startRow As Integer
Dim pKeyCol As String
Dim acTable As String
Dim pKey As String
xlPath = "C:\Users\loiseaua.EUOTIS\Desktop\micro.xlsx"
wsName = "Feuil1"
startRow = 2
pKeyCol = "A"
acTable = "tblexportarticle"
pKey = "Codearticle"
On Error GoTo erreur
Dim app As Excel.Application
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Set app = New Excel.Application
Set wkb = app.Workbooks.Open(xlPath)
Set wks = wkb.Worksheets(wsName)
Dim i As Integer, cSQL As String
i = startRow
DoCmd.SetWarnings False
With wks
While .Range(pKeyCol & i).Value <> ""
If DCount("*", acTable, pKey & " LIKE '" & .Range(pKeyCol & i).Value & "'") = 0 Then
cSQL = "tblexportarticle " & acTable & " ( Codearticle, Désignation ) VALUES (" & Chr(34) & .Range("A" & i) & Chr(34) & ", " & Chr(34) & .Range("B" & i) & Chr(34) & ");"
DoCmd.RunSQL cSQL
End If
i = i + 1
Wend
End With
DoCmd.SetWarnings True
Set wks = Nothing
Set wkb = Nothing
Set app = Nothing
MsgBox "Import du fichier Excel réussi.", vbInformation + vbOKOnly, "Opération terminée..."
ImportXL = True
Exit Function
erreur:
MsgBox "Erreur: " & Err.Number & vbCrLf & Err.Description, vbOKOnly + vbInformation
ImportXL = False
End Function |
Partager