Create Access Table from Excel VBA

Vishesh's picture

Using the following piece of code you can create an Access table specifying the fields and types of your choice. You don't need to have Access on your system.

Sub CreateTable()
    'Add Reference to Microsoft ActiveX Data Objects 2.x Library
    Dim strConnectString        As String
    Dim objConnection           As ADODB.Connection
    Dim strDbPath               As String
 
    'Set database name and DB connection string--------
    strDbPath = "C:\Users\NC\Vishesh\DB\Hierarchy.mdb"
    '==================================================
    
    strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath & ";"
 
    'Connect Database; insert a new table
    Set objConnection = New ADODB.Connection
    With objConnection
        .Open strConnectString
        .Execute "CREATE TABLE MyTable ([EmpName] text(50) WITH Compression, " & _
                 "[Address1] text(150) WITH Compression, " & _
                 "[Address2] text(150) WITH Compression, " & _
                 "[City] text(50) WITH Compression, " & _
                 "[State] text(2) WITH Compression, " & _
                 "[PIN] text(6) WITH Compression, " & _
                 "[SIN] decimal(6))"
    End With
 
    Set objConnection = Nothing
 
End Sub 

call function string dat ato Excel import to xyz file i

This function calles a QT server http://127.0.0.1:16239/req?GetHistory(QQQQ,0,1/12/2006,1D,YES)
and gives data as in he example bellow, would you tell me how to import it in excel xyz file please?
Example: QT Server HTTP API
http://127.0.0.1:16239/req?GetHistory(QQQQ,0,1/12/2006,1D,YES)
Returns:
OK
03/10/1999,51.0625,51.15625,50.28125,51.0625,5232200
03/11/1999,51.4375,51.73435,50.3125,51.3125,9688600
03/12/1999,51.125,51.15625,49.65625,50.0625,8743600
03/15/1999,50.4375,51.5625,49.90625,51.50,6369000
03/16/1999,51.71875,52.15625,51.15625,51.9375,4905800
03/17/1999,51.9375,52.00,51.40625,51.5625,3965000
...
01/10/2006,42.64,42.92,42.55,42.88,84420400
01/11/2006,43.01,43.31,42.87,43.21,100777600
01/12/2006,43.15,43.29,42.85,43.00,82429100

Thanks

Russ