Format data in excel using a query table from an ODBC connection

[50ld13r]

Hi,

I have created an ODBC connection through excel using the code below. I am a beginner at this so therefore used a macro to create this. The main problem i have is that i cannot change the output data format. For example the column a.transaction_amt is displayed as a two digit number but i cannot add them together to create totals or change its format to currency. Although when i select to change the format it shows as currency but the displayed as the original 2 digit number.

Any Help would be greatly appreciated

    Sheets("Data").Select
    Columns("A:I").Select
    Selection.Delete Shift:=xlToLeft
    
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=my_DSN;UID=my_UID;;DATABASE=my_database; AUTHENTICATION=;AUTHENTICATONPA" _
        ), Array("RAMETER=;")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT a.col_1,a.col_2,a.col_3,a.col_4,a.transaction_amt,a.col_6" _
        , _
        ", a.col_7, a.col_8, a.col_9" & Chr(13) & "" & Chr(10) _
        & "FROM my_space.my_table_name a WHERE a.col_4 = 'N'" _
        , _
         "" & Chr(13) & "" & Chr(10) & "ORDER BY a.col_3, a.col_1" _
        )
        .Name = "Payment_History"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
    Columns("E:E").Select
    Selection.NumberFormat = "$#,##0.00"