ADO in Excel

[shlobodon]

I am looking to retrieve rows from an excel file, format the columns of these rows using SQL, then paste the rows into another excel file. I have been trying to use ADO to do this. Is ADO just for access databases, or would it be beneficial for what I am doing? If it would be beneficial, what should the provider be? If not beneficial, what is the best way to proceed?

Any input is appreciated!

 

[V_Malkoti]

Yes you can use ADO in MS-Excel to read other MS-Excel files. Here is a sample code:

Sub querySheetData()
    Dim path As String
    Dim cn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    
    path = "C:\TestingResults\testfile.xlsx"
    
    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & path & ";Extended Properties=Excel 12.0 Xml;"
        .Open
    End With
    
    strQuery = "SELECT * FROM [Sheet1$] where testCol = 2"
    Set rst = cn.Execute(strQuery)

    ThisWorkbook.Worksheets("Cols").Range("B7").CopyFromRecordset rst

End Sub

Note: Set library reference (Tools->Reference in VBA window) to "Microsoft ActiveX Data Objects" library.

 

[shlobodon]

Thank you! Your coding worked right away. Can you suggest any good resources on how you built the ConnectionString? I am new to programming. There seem to be many parameters associated with the connectionstring.

Thanks again!

 

[V_Malkoti]

You are welcome.

You can find format of connection strings with good deal of information at https://www.connectionstrings.com/