I asked my cat about this overnight, he suggested I do this and look at the
schema:
var table = connect.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new
object[] { null, null, null, "TABLE" });
Breaking in the debugger I could see one row in the table, and inside was
the suspicious value Foo$.
By using sheet name Foo$ in the code from yesterday ... the Excel sheet
rows are retrieved okay. No samples I found in my hours of searching
suggested or use this naming.
*GK*
On 28 April 2016 at 01:18, Davy Jones <[email protected]> wrote:
> Hey again
>
> Just looked at the code here, don't specify a query in the constructor
> pass it to the property after the connection is open.
>
> Hth
> Davy
>
> Sent from my iPhone
>
> On 27 Apr 2016, at 14:20, Greg Keogh <[email protected]> wrote:
>
> Folks, maybe 10 years ago I easily read the rows out of an XLS file using
> OleDb and a reader. Today I tried to read an XLSX file the same way. First
> I discovered I had to install AccessDatabaseEngine_x64.exe to be able to
> register a provider to open the file. My test file has a single sheet named
> Foo with a few rows of numbers. All the code combinations I've tried like
> the one below die with:
>
> *System.Data.OleDb.OleDbException : The Microsoft Access database engine
> could not find the object 'Foo'. Make sure the object exists and that you
> spell its name and the path name correctly. If 'Foo' is not a local object,
> check your network connection or contact the server administrator.*
>
> So I guess the sheet is acting like a table, but it's not found and I'm
> flummoxed. Any ideas? Anyone done this sort of thing?
>
> *Greg K*
>
> string excelConnect = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={
> filename};Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
> using (var connect = new OleDbConnection(excelConnect))
> {
> connect.Open();
> using (var command = new OleDbCommand("SELECT * FROM [Foo]", connect))
> {
> using (var adapter = new OleDbDataAdapter(command))
> {
> var ds = new DataSet();
> adapter.Fill(ds);
> return new SchemaDef();
> }
> }
> }
>
>
