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
| Sub LoadDAO(ByVal sFic As String, ByVal sPhase As String, ByVal CustRange As String, _
ByVal FieldsRange As String, ByVal DataRange As String)
Dim dbEng As Object
Dim db As Object
Dim rec As Object
Dim recF As Object
Dim sSQL As String
Dim sSQLF As String
Dim sCust As String
Dim i As Integer
' ouverture du fichier par DAO
Set dbEng = CreateObject("DAO.DBEngine.36")
Set db = dbEng.Workspaces(0).OpenDatabase(sFic, _
False, _
False, _
"Excel 8.0;HDR=NO;")
' requête pour récupérer le nom du client
sSQL = "SELECT * FROM " & CustRange
Set rec = db.OpenRecordset(sSQL, 4)
sCust = rec.Fields(0)
' boucle sur toutes les colonnes
On Error Resume Next
sSQLF = "SELECT * FROM " & FieldsRange
Set recF = db.OpenRecordset(sSQLF, 4)
For i = 0 To recF.Fields.Count - 1
If recF.Fields(i) & "" <> "" Then
sSQL = "SELECT * FROM " & DataRange & " WHERE F" & i + 1 & " <> 0 And F" & i + 1 & " & '' <> ''"
Set rec = db.OpenRecordset(sSQL, 4)
Do While Not (rec.EOF)
AddRecord sCust, rec.Fields(0), recF.Fields(i), rec.Fields(i), sPhase
rec.MoveNext
Loop
Set rec = Nothing
End If
Next i
Set recF = Nothing
Set dbEng = Nothing
Set db = Nothing
End Sub |
Partager