Export specific data/columns from access into excel

[Beebz]

Hey guys, I having trouble extracting specific data from access into my excel spreadsheet. I know more SQL than VBA and I can't understand why a simple SQL statement will not get it to work.

Here's my current code

Private Sub AccessDB()
    Const strDb As String = "C:\Users\Beebz\Documents\Rates.mdb"
    Const strQry As String = "SELECT * from RateTable"
    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
 
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDb & ";"
    Set rs = New ADODB.Recordset
    Worksheets("Test").Range("A:AS").ClearContents
 
    With rs
        Set .ActiveConnection = cn
        .Open strQry
    End With
    
    With Sheets("Test")
        For i = 1 To rs.Fields.Count
            .Cells(1, i).Value = rs.Fields(i - 1).Name
            '.Cells(2, i).Value = rs.Fields(i - 1).Name
        Next i
            .Range("A2").CopyFromRecordset rs
    End With
    'Worksheets("Test").Range("A1").CopyFromRecordset rs
 
    rs.Close: cn.Close
    Set rs = Nothing: Set cn = Nothing
End Sub

Now, this code displays all of the data and column names. I don't need the first 3 columns from the access that is exporting into excel. So I created a SQL statement that only selects the columns I want.

Const strQry As String = "SELECT 20003, 20004, 20006, 20007 from RateTable"

The column names are numbers. When I use that statement, it only displays the column names repeated down one column to the total rows from access. I don't get why it doesn't work.


Also, the data in the table are numbers. There are inputs that are zero. I want to only display the data from access that are not zero. So any data that contains zero, don't display.
Here's a sample statement I've tried with SELECT * but all data shows.

SELECT * from RateTable WHERE 20003 <> 0.00

 

[xenou]

Hi,


Try putting your table names in brackets to signal that these are identifiers:

Const strQry As String = "SELECT [20003], [20004], [20006], [20007] from RateTable"

SELECT * from RateTable WHERE [20003] <> 0.00

And avoid using numbers as table names The best table names should start with a letter and have only letters, digits, and underscore characters (i.e., for maximum compatibility across database platforms, which all have different rules for table naming).

 

[Beebz]

Hi,


Try putting your table names in brackets to signal that these are identifiers:

Const strQry As String = "SELECT [20003], [20004], [20006], [20007] from RateTable"

SELECT * from RateTable WHERE [20003] <> 0.00

And avoid using numbers as table names The best table names should start with a letter and have only letters, digits, and underscore characters (i.e., for maximum compatibility across database platforms, which all have different rules for table naming).

Thank you very much. It worked out