Import plusieurs fichiers Excel dans DB Access

[7339simon]

Bonjour à tous,

Dans le cadre d'un projet d'importation de données Excel dans Access, j'aimerais pouvoir uploadé plusieurs fichier d'un seul coup.

Le code ci-dessous fonctionne pour importer un seul fichier mais vu mes connaissances limités en VBA je bloque sur l'upload de plusieurs fichiers, je ne sais pas où mettre la boucle.

Si certains d'entre vous s'y connaissent pouvez-vous me donner des indications svp.

Merci d'avance pour votre aide.

Option Compare Database
Public w As Object
Public s As Object
Public x As Integer

Sub abc()

Dim Filename As String
Dim MyArray As Variant
Dim i As Integer

Dim strCritere As String

On Error GoTo errorHandler
    
Filename = OpenFile()

DoCmd.SetWarnings False

With s
MyArray = Array(.Range("D3").Value, .Range("J4").Value, .Range("P4").Value, .Range("J3").Value, _
.Range("H10").Value, .Range("H8").Value, .Range("N8").Value, .Range("D16").Value, .Range("H16").Value, _
.Range("D12").Value, .Range("H12").Value, .Range("D26").Value, .Range("D28").Value, .Range("D30").Value, _
.Range("D20").Value, .Range("N10").Value, .Range("N12").Value, .Range("N14").Value, .Range("N16").Value, _
.Range("N18").Value, .Range("N20").Value, .Range("N34").Value, .Range("N36").Value, .Range("N38").Value, _
.Range("N40").Value, .Range("N42").Value, .Range("N44").Value, .Range("N46").Value, .Range("N48").Value, _
.Range("N50").Value, .Range("N52").Value, .Range("N54").Value, .Range("N56").Value, .Range("N58").Value, _
.Range("N60").Value, .Range("N62").Value, .Range("N64").Value, .Range("N66").Value, .Range("J69").Value, _
.Range("N68").Value, Now)
 
End With
 
cSQL = "insert into [Simon] ([LSP],[SHP ID],[TR ID],[CONTACT],[INCOTERM],[DEPARTURE]," & _
"[ARRIVAL],[DIRECT],[VIA],[CARRIER],[FLIGHT NBR/VESSEL],[GROSS WEIGHT],[CHARGEABLE WEIGHT]," & _
"[VOLUME],[INFORMATION],[COLLECTION DATE REQUIRED],[ESTIMATED DEPARTURE DATE]," & _
"[ESTIMATED ARRIVAL DATE],[ESTIMATED DELIVERY DATE],[LATEST CONFIRMATION DATE FOR BOOKING]," & _
"[LATEST CANCELLATION DATE WITHOUT FEES],[COLLECTION COSTS],[CUSTOMS CLEARANCE ORI]," & _
"[IMO SURCHARGE ORI],[AWB],[HANDLING ORI],[SECURITY],[ADMINISTRATION CHARGES],[FREIGHT]," & _
"[IMO SURCHARGE],[FSC],[IRC],[HANDLING DEST],[CUSTOMS CLEARANCE DEST]," & _
"[IMO SURCHARGE DEST],[RELEASE FEE],[DELIVERY COSTS],[OTHERS],[DUTIES & TAXES],[TOTAL],[DATE OF RECEPTION]) values ("


For i = 0 To UBound(MyArray)
    
   
cSQL = cSQL & Chr(34) & MyArray(i) & Chr(34) & " , "

    Next i
    
cSQL = Left(cSQL, Len(cSQL) - 2) & ");"

DoCmd.RunSQL cSQL

MsgBox ("Votre fichier a bien été uploadé dans la table Access!")

DoCmd.SetWarnings True

w.Close False

Exit Sub

errorHandler:

MsgBox ("An error occured")

Exit Sub

End Sub

Function OpenFile() As String

With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = True
    .Title = "Import Spot Quote"
    .InitialFileName = ""
    If .Show = 0 Then MsgBox "Please Select a file"
    For x = 1 To .SelectedItems.Count
    Set w = Workbooks.Open(.SelectedItems(x))
    Set s = w.Sheets(1)
   ' MySelectedFiles(I) = .SelectedItems(I)
    Next
End With

End Function