Fichiers dbf dans Excel avec SQL

Chaque jour, je dois faire un rapport pour le vendeur, nous avons 2 files dbf de witch je veux automatiquement faire le rapport dans Excel. Le rapport de 1 file dbf fonctionne parfaitement, mais je ne sais pas comment join 2 files dbf dans VBA.

Je dois suivre le script:

Option Explicit

Sub ReadDBF ()

Dim con As Object Dim rs As Object Dim DBFFolder As Ssortingng Dim FileName As Ssortingng Dim FileName1 As Ssortingng Dim sql As Ssortingng Dim myValues() As Ssortingng Dim i As Integer Dim j As Integer Application.ScreenUpdating = False DBFFolder = ThisWorkbook.Path & "\" FileName = "project1.dbf" FileName1 = "project2.dbf" On Error Resume Next Set con = CreateObject("ADODB.connection") If Err.Number <> 0 Then MsgBox "Connection was not created!", vbCritical, "Connection error" Exit Sub End If On Error GoTo 0 con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBFFolder & ";Extended Properties=dBASE IV;" sql = "SELECT project_id, COUNT(*) AS total, salesman, MAX(date) AS max_date, projectname FROM " & FileName & FileName1 & " where DateValue(datumtijd) = Date() and FileName.project_id = FileName1.project_id " & "group by project_id, salesman" On Error Resume Next Set rs = CreateObject("ADODB.recordset") If Err.Number <> 0 Then MsgBox "Connection was not created!", vbCritical, "Connection error" Exit Sub End If On Error GoTo 0 rs.CursorLocation = 3 rs.CursorType = 1 rs.Open sql, con ReDim myValues(rs.RecordCount, 20) i = 1 If Not (rs.EOF And rs.BOF) Then 'Go to the first record. rs.MoveFirst Do Until rs.EOF = True myValues(i, 1) = rs!project_id myValues(i, 2) = rs!salesman myValues(i, 3) = rs!Total myValues(i, 4) = rs!max_date myValues(i, 5) = rs!project rs.MoveNext i = i + 1 Loop Else rs.Close con.Close Set rs = Nothing Set con = Nothing Application.ScreenUpdating = True MsgBox "There are no records in the recordset!", vbCritical, "No Records" Exit Sub End If Sheet1.Activate For i = 1 To UBound(myValues) For j = 1 To 4 Cells(i + 1, j) = myValues(i, j) Next j Next i rs.Close con.Close Set rs = Nothing Set con = Nothing Columns("A:D").EntireColumn.AutoFit Application.ScreenUpdating = True MsgBox "The values were read from recordset successfully!", vbInformation, "Done"

End Sub

La requête ne fonctionne pas simplement en ajoutant les deux tables set comme source de source. Qu'est-ce que vous essayez d'get réellement? En outre, vous ne devriez jamais concaténer des strings pour créer une requête. Ils doivent toujours être paramétrés.

Maintenant, cela dit, votre syntaxe est simplement incorrecte. Vos variables sont appelées «FileName» et «FileName1», mais les TABLEAUX sous-jacents dont vous avez recours sont «Project1» et «Project2», respectivement. Vous devriez en apprendre davantage sur les alias pour simplifier les requêtes, et apprendre la syntaxe JOIN appropriée.

En énumérant les tables l'une après l'autre sans aucune virgule, cela risque de l'échouer. Voici une syntaxe plus précise et formatée pour la lisibilité. Ensuite, j'ai modifié votre requête d'origine pour faire correspondre le context. Vous devriez également toujours qualifier les noms de champs du tableau respectif afin que d'autres tentent d'aider à savoir d'où proviennent les choses. Dans l'exemple ci-dessous, j'ai seulement GUESSED à quelle table a quelles colonnes en utilisant l'alias "P1". et "P2". respectivement. Vous devrez probablement les changer. En outre, comme "Date" peut être interprété comme un mot réservé, je l'ai enroulé dans [], mais il faudra peut-être changer pour cocher les caractères (à côté du numéro 1) “

 select P1.Project_ID, COUNT(*) as Total, P1.SalesMan, MAX( P2.[Date] ) as Max_Date, P1.ProjectName from Project1 P1 JOIN Project2 P2 on P1.Project_ID = P2.Project_ID where DateValue( P2.datumtijd ) = date() group by P1.Project_ID, P1.SalesMan P1.Project_ID, select P1.Project_ID, COUNT(*) as Total, P1.SalesMan, MAX( P2.[Date] ) as Max_Date, P1.ProjectName from Project1 P1 JOIN Project2 P2 on P1.Project_ID = P2.Project_ID where DateValue( P2.datumtijd ) = date() group by P1.Project_ID, P1.SalesMan COUNT (*) en tant que Total, select P1.Project_ID, COUNT(*) as Total, P1.SalesMan, MAX( P2.[Date] ) as Max_Date, P1.ProjectName from Project1 P1 JOIN Project2 P2 on P1.Project_ID = P2.Project_ID where DateValue( P2.datumtijd ) = date() group by P1.Project_ID, P1.SalesMan P1.SalesMan, select P1.Project_ID, COUNT(*) as Total, P1.SalesMan, MAX( P2.[Date] ) as Max_Date, P1.ProjectName from Project1 P1 JOIN Project2 P2 on P1.Project_ID = P2.Project_ID where DateValue( P2.datumtijd ) = date() group by P1.Project_ID, P1.SalesMan MAX (P2. [Date]) comme Max_Date, select P1.Project_ID, COUNT(*) as Total, P1.SalesMan, MAX( P2.[Date] ) as Max_Date, P1.ProjectName from Project1 P1 JOIN Project2 P2 on P1.Project_ID = P2.Project_ID where DateValue( P2.datumtijd ) = date() group by P1.Project_ID, P1.SalesMan P1.ProjectName select P1.Project_ID, COUNT(*) as Total, P1.SalesMan, MAX( P2.[Date] ) as Max_Date, P1.ProjectName from Project1 P1 JOIN Project2 P2 on P1.Project_ID = P2.Project_ID where DateValue( P2.datumtijd ) = date() group by P1.Project_ID, P1.SalesMan Projet1 P1 select P1.Project_ID, COUNT(*) as Total, P1.SalesMan, MAX( P2.[Date] ) as Max_Date, P1.ProjectName from Project1 P1 JOIN Project2 P2 on P1.Project_ID = P2.Project_ID where DateValue( P2.datumtijd ) = date() group by P1.Project_ID, P1.SalesMan JOIN Project2 P2 select P1.Project_ID, COUNT(*) as Total, P1.SalesMan, MAX( P2.[Date] ) as Max_Date, P1.ProjectName from Project1 P1 JOIN Project2 P2 on P1.Project_ID = P2.Project_ID where DateValue( P2.datumtijd ) = date() group by P1.Project_ID, P1.SalesMan sur P1.Project_ID = P2.Project_ID select P1.Project_ID, COUNT(*) as Total, P1.SalesMan, MAX( P2.[Date] ) as Max_Date, P1.ProjectName from Project1 P1 JOIN Project2 P2 on P1.Project_ID = P2.Project_ID where DateValue( P2.datumtijd ) = date() group by P1.Project_ID, P1.SalesMan DateValue (P2.datumtijd) = date () select P1.Project_ID, COUNT(*) as Total, P1.SalesMan, MAX( P2.[Date] ) as Max_Date, P1.ProjectName from Project1 P1 JOIN Project2 P2 on P1.Project_ID = P2.Project_ID where DateValue( P2.datumtijd ) = date() group by P1.Project_ID, P1.SalesMan P1.Project_ID, select P1.Project_ID, COUNT(*) as Total, P1.SalesMan, MAX( P2.[Date] ) as Max_Date, P1.ProjectName from Project1 P1 JOIN Project2 P2 on P1.Project_ID = P2.Project_ID where DateValue( P2.datumtijd ) = date() group by P1.Project_ID, P1.SalesMan

La clause JOIN identifie la relation ENTRE les deux tables sur les colonnes respectives. La clause WHERE est un critère supplémentaire que vous searchz.

 sql = "SELECT project_id, COUNT(*) AS total, salesman, " & _ "MAX(date) AS max_date, projectname " & _ " FROM " & FileName & " P1 " & _ " JOIN " & FileName1 & " P2 ON P1.Project_ID = P2.Project_ID" & _ " where DateValue(datumtijd) = Date() " & _ " group by project_id, salesman" sql = "SELECT project_id, COUNT (*) AS total, vendeur," & _ sql = "SELECT project_id, COUNT(*) AS total, salesman, " & _ "MAX(date) AS max_date, projectname " & _ " FROM " & FileName & " P1 " & _ " JOIN " & FileName1 & " P2 ON P1.Project_ID = P2.Project_ID" & _ " where DateValue(datumtijd) = Date() " & _ " group by project_id, salesman" "MAX (date) AS max_date, projectname" & _ sql = "SELECT project_id, COUNT(*) AS total, salesman, " & _ "MAX(date) AS max_date, projectname " & _ " FROM " & FileName & " P1 " & _ " JOIN " & FileName1 & " P2 ON P1.Project_ID = P2.Project_ID" & _ " where DateValue(datumtijd) = Date() " & _ " group by project_id, salesman" "FROM" & FileName & "P1" & _ sql = "SELECT project_id, COUNT(*) AS total, salesman, " & _ "MAX(date) AS max_date, projectname " & _ " FROM " & FileName & " P1 " & _ " JOIN " & FileName1 & " P2 ON P1.Project_ID = P2.Project_ID" & _ " where DateValue(datumtijd) = Date() " & _ " group by project_id, salesman" "JOIN" & FileName1 & "P2 ON P1.Project_ID = P2.Project_ID" & _ sql = "SELECT project_id, COUNT(*) AS total, salesman, " & _ "MAX(date) AS max_date, projectname " & _ " FROM " & FileName & " P1 " & _ " JOIN " & FileName1 & " P2 ON P1.Project_ID = P2.Project_ID" & _ " where DateValue(datumtijd) = Date() " & _ " group by project_id, salesman" "où DateValue (datumtijd) = Date ()" & _ sql = "SELECT project_id, COUNT(*) AS total, salesman, " & _ "MAX(date) AS max_date, projectname " & _ " FROM " & FileName & " P1 " & _ " JOIN " & FileName1 & " P2 ON P1.Project_ID = P2.Project_ID" & _ " where DateValue(datumtijd) = Date() " & _ " group by project_id, salesman"