Copy an Excel range to a recordset using ADO
Posted on 2012-04-10
I am trying to retrieve the contents of a range in an excel worksheet into a recordset using ADO in C#. I am using Excel 2007. This is what I have:
SQLquerySource = "SELECT * FROM [Sheet1$SourceRange]" //"SourceRange" is a named range
ADODB.Connection cn =new ADODB.Connection();
cn.Open(ExcelSourceConnection, null, null, 0); //"ExcelSourceConnection" identifies the source workbook
ADODB.Recordset rs = new ADODB.Recordset();
rs.Open(SQLquerySource, cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1); //at this statement I get a "COM Exception was unhandled" and its description says "The Microsoft Office Access database engine could not find the object 'Sheet1$SourceRange'. Make sure the object exists and that you spell its name and the path name correctly."
Of course, when I open the source workbook and check, I can see both the source sheet and the named range are valid, and I have spelled them correctly, so there must be more to it than that.