SQL on excel sheet.

[shivamfet]

I have some raw data in excel sheet with the first row is header row..now how should i apply sql on it...i am using excel 2003..if i use data->table option it asks for row and column input..and i am unable to understand that...plz help...

 

[Sektor]

Change "Sheet1" to actual name.
Add reference:
Tools -> References -> Microsoft ActiveX Data Objects 2.8 (or 2.6) library.

[COLOR="Blue"]Option[/COLOR] [COLOR="Blue"]Explicit[/COLOR]
[COLOR="Blue"]Option[/COLOR] [COLOR="Blue"]Compare[/COLOR] [COLOR="Blue"]Text[/COLOR]

[COLOR="Blue"]Sub[/COLOR] GetData()

    [COLOR="Blue"]Dim[/COLOR] cn [COLOR="Blue"]As[/COLOR] ADODB.Connection
    [COLOR="Blue"]Dim[/COLOR] rs [COLOR="Blue"]As[/COLOR] ADODB.Recordset

    [COLOR="Blue"]Dim[/COLOR] sSQL [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] sDatabaseFile [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]String[/COLOR]

    sDatabaseFile = ThisWorkbook.FullName

    [COLOR="Blue"]Set[/COLOR] cn = [COLOR="Blue"]New[/COLOR] ADODB.Connection
    
    [COLOR="Blue"]With[/COLOR] cn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                            "Data Source=" & sDatabaseFile & ";" & _
                            "Extended Properties=""Excel 8.0 Xml;HDR=Yes;IMEX=1;"";"
        .CursorLocation = adUseClient
        .Mode = adModeRead
        .Open
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
    sSQL = "SELECT * FROM [Sheet1$];"
    
    ' Setup record set.
    [COLOR="Blue"]Set[/COLOR] rs = [COLOR="Blue"]New[/COLOR] ADODB.Recordset

    [COLOR="Blue"]With[/COLOR] rs
        .Source = sSQL
        .ActiveConnection = cn
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open Options:=adCmdText
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
    [COLOR="Blue"]With[/COLOR] Sheets("Sheet2")
        .Range("A1").CopyFromRecordset rs
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
      
    rs.Close
    cn.Close
    [COLOR="Blue"]Set[/COLOR] rs = [COLOR="Blue"]Nothing[/COLOR]
    [COLOR="Blue"]Set[/COLOR] cn = [COLOR="Blue"]Nothing[/COLOR]

[COLOR="Blue"]Exit[/COLOR] [COLOR="Blue"]Sub[/COLOR]