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 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100
|
Option Explicit
Public MatriceRegions() As Variant, MatriceDep() As Variant, MatriceVilles() As Variant, MatriceCodes() As Variant
Sub LancerLaBoiteDeDialogue()
Dim IndexRegions As Integer
Application.ScreenUpdating = False
ChargerLesMatrices
Application.ScreenUpdating = True
With UserForm1
.ComboBoxRegions.Clear
.ComboBoxDep.Clear
.ComboBoxVilles.Clear
.ComboBoxCodes.Clear
For IndexRegions = LBound(MatriceRegions) To UBound(MatriceRegions)
.ComboBoxRegions.AddItem MatriceRegions(IndexRegions)
Next IndexRegions
.ComboBoxRegions.ListIndex = -1
.Show
End With
End Sub
Sub ChargerLesMatrices()
Dim J As Long, DerniereLigne As Long
Dim Chemin As String
Dim xlApp As Excel.Application 'Application Excel pour ouvrir un fichier XLS
Dim xlWb As Excel.Workbook 'Classeur Excel
Dim xlWs As Excel.Worksheet 'Feuille du classeur
Dim AireRegion As Excel.Range, AireDep As Excel.Range, AireVilles As Excel.Range, AireCodes As Excel.Range
On Error GoTo Fin:
Chemin = ActiveDocument.Path & "\Base de données.xlsx"
Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open(Chemin) 'Ouverture du fichier
Set xlWs = xlWb.Worksheets(1) 'Utilisation de la première feuille
With xlWs
Set AireRegion = .ListObjects("TableRegions").DataBodyRange
ReDim MatriceRegions(AireRegion.Count - 1)
For J = 1 To AireRegion.Count
MatriceRegions(J - 1) = AireRegion(J).Value
Next J
Set AireDep = .ListObjects("TableDep").ListColumns(1).DataBodyRange
ReDim MatriceDep(AireDep.Count - 1, 1)
For J = 1 To AireDep.Count
MatriceDep(J - 1, 0) = AireDep(J).Value
MatriceDep(J - 1, 1) = AireDep(J).Offset(0, 1).Value
Next J
Set AireVilles = .ListObjects("TableVilles").ListColumns(1).DataBodyRange
ReDim MatriceVilles(AireVilles.Count - 1, 1)
For J = 1 To AireVilles.Count
MatriceVilles(J - 1, 0) = AireVilles(J).Value
MatriceVilles(J - 1, 1) = AireVilles(J).Offset(0, 1).Value
Next J
Set AireCodes = .ListObjects("TableDesCodes").ListColumns(1).DataBodyRange
ReDim MatriceCodes(AireCodes.Count - 1, 3)
For J = 1 To AireCodes.Count
MatriceCodes(J - 1, 0) = AireCodes(J).Value
MatriceCodes(J - 1, 1) = AireCodes(J).Offset(0, 1).Value
MatriceCodes(J - 1, 2) = AireCodes(J).Offset(0, 2).Value
MatriceCodes(J - 1, 3) = AireCodes(J).Offset(0, 3).Value
Next J
End With
GoTo Fin:
Fin:
xlWb.Close savechanges:=False
xlApp.Quit
Set AireRegion = Nothing
Set AireDep = Nothing
Set AireVilles = Nothing
Set AireCodes = Nothing
Set xlWs = Nothing
Set xlWb = Nothing
Set xlApp = Nothing
End Sub |
Partager