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
|
Option Explicit
Public Const Query As String = "SELECT CODE, LIBELLE, TYPE_ID, PRODUIT, FABRICANT, SERIE, CATEGORIE, RACK, ALIM, TENSION, COURANT, NBRE_VOIE, NBRE_EXT, NBRE_PAS, NUMBER_DI, NUMBER_DO, NUMBER_AI, NUMBER_AO, NB_EMPL, TYPE, TECHNO, RACCORD, NBRE_E, NBRE_S, MODULE, H_EMPL, POWER_DISSIPATION, DX, DY, DZ, POIDS, ACCESSOIR, OBSOLETE, SUBSTITUTION, LIBELLE_EN, CARTE, EMBASE, BORNIER FROM PLC WHERE (CODE Like [LookupString]) ORDER BY CODE ;"
Public db_FullName As String
Public Sub AfficherTable()
' D?claration des variables et constantes
Const db_Name As String = "DATA-PLC.accdb"
Const db_SubFolder As String = "\"
Dim mytable()
Dim db_Folder As String
'
db_Folder = ThisWorkbook.Path
db_FullName = db_Folder & db_SubFolder & db_Name
mytable = QueryAccess(db_FullName, Query)
With USF_Catalog_Mat
With USF_Catalog_Mat.List
.List = mytable
.ColumnCount = 38
.ColumnWidths = "200;600;90;50;100;100;100;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50;50;40;40;40;40;50;40;60;500;150;150;150,"
End With
.Label6.Caption = IList
.Show vbModeless
'.Show vbModal
End With
End Sub
Function QueryAccess(dbFullName As String, sqlQuery As String, Optional WithLabel As Boolean = True, Optional LookupString As String = "*") As Variant '
' Nécessite de référencer Microsoft DAO x.x ObjectLibrary
' Arguments
' dbFullName ' Chemin + nom du fichier
' SqlQuery ' Cha?ne de caract?re contenant la requ?te SQL
' [WithLabel] ' True ou Omis renvoie les ?tiquettes de colonnes
' [LookupString] ' Cha?ne pour recherche partielle
' Author : Philippe Tulliez www.magicoffice.be
' Version : 3.1
' Date : 11/01/2014 (11/08/2013)
' D?claration des variables
Dim db As DAO.Database, rs As DAO.Recordset
Dim mytable(), count As Long, Elem As Integer
Dim CurrentProgress As Double
Dim ProgressPercentage As Double
Dim BarWidth As Long
Progress.Hide
With Progress
.Hide
.Bar.Width = 0
.Show vbModeless
End With
' Affectation
sqlQuery = Replace(sqlQuery, "[LookupString]", Chr(34) & LookupString & "*" & Chr(34))
Set db = Workspaces(0).OpenDatabase(dbFullName, ReadOnly:=True)
Set rs = db.OpenRecordset(sqlQuery)
' Lecture des enregistrements de la requ?te
If Not rs.EOF Then
i = 1
While Not rs.EOF
ReDim Preserve mytable(rs.Fields.count, count)
If count = 0 And WithLabel Then
For Elem = 0 To rs.Fields.count - 1: mytable(Elem, count) = rs(Elem).SourceField: Next ' Label
count = count + 1: ReDim Preserve mytable(rs.Fields.count, count)
End If
For Elem = 0 To rs.Fields.count - 1
mytable(Elem, count) = IIf(IsNull(rs(Elem)), "", rs(Elem))
Next Elem
count = count + 1: rs.MoveNext
IList = rs.RecordCount 'Variable qui est utilis? pour USF_ACESS_MODIF_Ref
Jlist = rs.Fields.count 'Variable qui est utilis? pour USF_ACESS_MODIF_Ref
CurrentProgress = i / rs.RecordCount
BarWidth = Progress.Border.Width * CurrentProgress
ProgressPercentage = Round(CurrentProgress * 100, 0)
Progress.Bar.Width = BarWidth
Progress.Text.Caption = "Catalogue mat?riel charg? ?:" & ProgressPercentage & "%"
DoEvents
i = i + 1
Wend
QueryAccess = Application.WorksheetFunction.Transpose(mytable)
Progress.Hide
Else
QueryAccess = Array("")
End If
rs.Close: db.Close: Set rs = Nothing
End Function |
Partager