VBA programming - Data extraction from DBF file to excel?

I have a database - SampleDo.dbf and would like to extract only the field Date,DO_no,Qty and code to excel file with condition. (Condition will be the date, eg. month = 8 and year = 2010). Can you please help me with the VBA program to extract the data.

Thanks !

Update:

Stas s, Thanks for your prompt reply. Is this VBA program work from excel . (Note : I am extracting from DBF file and not access).

I run the Macro, and it give me compile error : user-defined type not defined on line :

"Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _

sqlstr As String, dbfile As String)"

Update 2:

Stas s, Thanks for your prompt reply. Is this VBA program work from excel . (Note : I am extracting from DBF file and not access).

I run the Macro, and it give me compile error : user-defined type not defined on line :

"Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _

sqlstr As String, dbfile As String)"

1 Answer

Relevance
  • 9 years ago

    Public Sub GetCn(ByRef dbcon As ADODB.Connection, ByRef dbrs As ADODB.Recordset, _

    sqlstr As String, dbfile As String, usernm As String, pword As String)

    Set dbcon = New ADODB.Connection

    dbcon.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbfile & ";", _

    usernm, pword

    Set dbrs = New ADODB.Recordset

    dbrs.Open sqlstr, dbcon

    End Sub

    Public Sub getrs()

    Dim adoconn As ADODB.Connection

    Dim adors As ADODB.Recordset

    Dim sql As String

    Dim filenm As String

    sql = "Select Date, DO_no, QTY from Table1 WHERE DatePart(""m"", Date) = 8 and DatePart(""yyyy"", Date) = 2010"

    filenm = "C:\Data\sampledb.mdb"

    Call GetCn(adoconn, adors, sql, filenm, "", "")

    Dim xlsht As Excel.Worksheet

    Set xlsht = Sheets("Sheet1")

    xlsht.Range("A1").CopyFromRecordset adors

    adors.Close

    adoconn.Close

    Set adors = Nothing

    Set adoconn = Nothing

    Set xlsht = nothing

    End Sub

Still have questions? Get your answers by asking now.