Import access data to excel based on criteria

Asked

Viewed 1,608 times

0

I have a table in access and I can already pull the data from it to an excel spreadsheet (I can do this using the code below), but I would like to be able to pull only some data (based on criteria, type an advanced excel filter, only performed between excel and access). Pointing out that the table in access has type 15 columns and my filter would only be with 5 columns.

Code that I already have and that works well (the problem is that pulls the entire access table):

Sub BuscaDoAccess()

    Dim MDB As New Connection
    Dim RS As New Recordset
    Dim FD As ADODB.Field
    Dim SQL As String

    MDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data 
    Source=C:\Users\Clebson\Desktop\BD.mdb"

    SQL = "select * from HistoricoGeral"
    COL = 2

    RS.Open SQL, MDB

    If Not RS.EOF Then
        For Each FD In RS.Fields
            Sheets("Planilha1").Cells(1, COL).Value = FD.Name
            COL = COL + 1
        Next FD

        COL = 2
        ThisWorkbook.Sheets("Planilha1").Cells(2, COL).CopyFromRecordset RS
    End If

    RS.Close
    MDB.Close

    Set MDB = Nothing
    Set RS = Nothing
    Set FD = Nothing

End Sub
  • Try to put some condition in your select. Select * from Historicogeral WHERE 'Name of a column' = 'some data in it' This site can help you, http://www.macoratti.net/12/10/c_mxls1.htm

  • Oops. For a criteria set manually already it was. Would you know how to do for more criteria? and I will have 5 columns for such filter, more.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.