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
| Option Explicit
Option Base 1
Option Compare Text
Dim cheminfichier As Variant
Dim nomfichier As String
Private Declare PtrSafe Function QueryPerformanceCounter Lib "Kernel32" (X As Currency) As Boolean
Private Declare PtrSafe Function QueryPerformanceFrequency Lib "Kernel32" (X As Currency) As Boolean
Sub importdonnee()
Dim f1 As Worksheet
Dim f2 As Worksheet
Dim a() As Variant, b() As Variant, d()
Dim Debut As Currency, Fin As Currency, Freq As Currency
Dim tmpstr() As String
Dim mondico1 As Object
Dim ligne As Long
Dim k As Long
Dim lastline As Long
Dim lastrow As Long
Dim lastline2 As Long
Dim temp As String
Dim aa(), bb(), i As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
QueryPerformanceCounter Debut
cheminfichier = Application.GetOpenFilename("Fichiers Excels (*.xlsx), *.xlsx")
If cheminfichier = False Then
Exit Sub
End If
Workbooks.Open cheminfichier
tmpstr = Split(cheminfichier, "\")
nomfichier = tmpstr(UBound(tmpstr))
Set f1 = Workbooks(nomfichier).Sheets("NOUVEAU_CV")
Set f2 = ThisWorkbook.Sheets("Cvtheque")
lastline = ThisWorkbook.Sheets("Cvtheque").Cells(Rows.Count, "A").End(xlUp).Row
a = f2.Range("A1:AC" & lastline).Value
lastrow = Workbooks(nomfichier).Sheets("NOUVEAU_CV").Cells(Rows.Count, "A").End(xlUp).Row
b = f1.Range("A1:AC" & lastrow).Value
Set mondico1 = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(a)
temp = a(i, 1)
mondico1(temp) = ""
Next i
ligne = 1
Dim c
ReDim c(1 To Application.Max(UBound(a), UBound(b)), 1 To UBound(a, 2))
For i = 2 To UBound(b)
temp = b(i, 1)
If Not mondico1.Exists(temp) Then
For k = 1 To UBound(b, 2): c(ligne, k) = b(i, k): Next k
ligne = ligne + 1
End If
Next
lastline2 = lastline + 1
ThisWorkbook.Sheets("Cvtheque").Range("A" & lastline2).Resize(UBound(c, 1), UBound(c, 2)) = c
QueryPerformanceCounter Fin
QueryPerformanceFrequency Freq
MsgBox "Durée de la procédure = " & Format(((Fin - Debut) / Freq), "0.00") & " s"
Set mondico1 = Nothing
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic
End Sub |
Partager