Connection string ADO 2.8 library and Excel 2007 version 12

what is the connection string for using the :
ActiveX Data Objects 2.8 library and ActiveX Data Objects Recordset 2.8 library

to access an excel 2007 Application.version 12.0 Macro enabled workbook?

I am running this under XP.

In expectation of your help: thank you

PS. I am using one worksheet as a data table.
PPS. I have it running happily using the ActiveX Data Objects 6.0 library under Vista but have found the target machine only has 2.8 and it will be problematic to use anything different.

Hi,

For 2007, something like this:

           strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ 
                        "Data Source=" & strSourceFile & ";" & _ 
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"

For future ref. see: http://www.connectionstrings.com/

Originally Posted by Colin Legg

Hi,

For 2007, something like this:

           strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _ 
                        "Data Source=" & strSourceFile & ";" & _ 
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"

Thanks Colin, that is what he is tried with 2.8. It gives rise to an error: cannot update. Database or object is read only. He has tried it with both the original file and a new test spreadseet.

Hi,

Please post the code, including the query and the line where you open the recordset?

        With ThisWorkbook
            Set mCn = New ADODB.Connection
            str = ThisWorkbook.Path & "\" & mstrLocalDatabase
            With mCn
''                .Provider = "Microsoft.Jet.OLEDB.4.0"
                .Provider = "Microsoft.ACE.OLEDB.12.0"
                .ConnectionString = "Data Source=" & str & ";Extended Properties=Excel 8.0;"
                .Open           'THIS IS WHERE THE ERROR OCCURS
            End With
        End With

Thank you

The Extended Properties in your code are not what Colin posted.