dbf files in Excel with SQL
<p>Every day i need to make a report for salesman, we have 2 dbf files from witch i automatic want to make the report in Excel. Report from 1 dbf file works perfect, but i don't know how to join 2 dbf files in VBA.</p>

<p>I have to following script:</p>

<p>Option Explicit</p>

<p>Sub ReadDBF()</p>

<pre><code>Dim con         As Object
Dim rs          As Object
Dim DBFFolder   As String
Dim FileName    As String
Dim FileName1    As String
Dim sql         As String
Dim myValues()  As String
Dim i           As Integer
Dim j           As Integer

Application.ScreenUpdating = False

DBFFolder = ThisWorkbook.Path &amp; "\"
FileName = "project1.dbf"
FileName1 = "project2.dbf"

On Error Resume Next

Set con = CreateObject("ADODB.connection")
If Err.Number &lt;&gt; 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=" &amp; DBFFolder &amp; ";Extended Properties=dBASE IV;"

sql = "SELECT project_id, COUNT(*) AS total, salesman, MAX(date) AS max_date, projectname FROM " &amp; FileName &amp; FileName1 &amp; " where DateValue(datumtijd) = Date() and FileName.project_id = FileName1.project_id " &amp; "group by project_id, salesman"


On Error Resume Next
Set rs = CreateObject("ADODB.recordset")
If Err.Number &lt;&gt; 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"
</code></pre>

<p>End Sub</p>