CODEWITHSUNDEEP
excelado
Jeffrey Kramer
Jeffrey Kramer

Reputation: 1345

Select Data From Closed Excel Sheet Using ADO

I have a workbook called "SomeFile.xlsx" I need to connect to this sheet and extract a range of data from a worksheet. I am trying to connect using ADO with no success. I have the following code:

Sub ExtractData()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim PathName As String
Dim PathRange As String

    PathRange = "SELECT * FROM [AddConvert$D4:D10];"
    PathName = "C:\path\myFile.xlsx"
    Set cnn = New ADODB.Connection
    With cnn
        .Provider = "Microsoft.ACE.OLEDB.12.0 "
        .ConnectionString = "Data Source=" & PathName & ";Extended Properties=Excel 12.0;"
        .CursorLocation = adUseClient
        .Open
    End With  'Works fine up to here
    Set rs = cnn.Execute(PathRange)  'Dies here

End Sub

Everything works as far as I can tell. I put a watch on cnn and a break on the last line. cnn looks good. HOwever, it keeps saying it can't find the AddConvert worksheet and get the data. I don't understand why it can't find it or what is happening.

Upvotes: 2

Views: 649

Answers (1)

Andy G
Andy G

Reputation: 19367

You should use Excel 12.0 for .xlsx files. Example:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
    Extended Properties="Excel 12.0 Xml;HDR=YES";

from connectionstrings

Upvotes: 2

Related Questions