Excel 2007 , OleDB Select command: [Table1] access in stead of [Sheet1$}

Posted 2011-04-23 17:36
by Ravi R
in the Excel Forum

Can we get data from table feature of Excel 2007 / 2010?

Ex.
Select * from Table1 or Table 6,
instead of select * from [Sheet1$]

Reply 2011-11-29 12:18
by Sofía M
in the Excel Forum

An example for Excel 2007

You should create in your own Excel File in:
Formulas -> Name manager (I don't know exactly that name in english), selecting your table, and bind a name for that, for example, myTable.

In your application, you select again the sheet:
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [sheet$]", objConn);

OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();

But here you can select a table:
objAdapter1.Fill(objDataset1, "myTable");

Reply 2011-11-29 12:24
by Sofía M
in the Excel Forum

An example for Excel 2007:

Firstly, you should bind a name to your table in the excel file. (Formulas->name manager)
Then in your application write something like this:

select the same sheet.....

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + sList + "$]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();

but here you select your own table.....
objAdapter1.Fill(objDataset1, "miTabla");

Reply 2011-11-29 12:24
by Sofía M
in the Excel Forum

An example for Excel 2007:

Firstly, you should bind a name to your table in the excel file. (Formulas->name manager)
Then in your application write something like this:

select the same sheet.....

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [sheet$]", objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataSet objDataset1 = new DataSet();

but here you select your own table.....
objAdapter1.Fill(objDataset1, "miTabla");

Reply 2011-11-29 12:25
by Sofía M
in the Excel Forum

An example for Excel 2007:

Firstly, you should bind a name to your table in the excel file. (Formulas->name manager)
Then in your application write something like this:

select the same sheet.....

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [sheet$]", objConn)
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect
DataSet objDataset1 = new DataSet()

but here you select your own table.....
objAdapter1.Fill(objDataset1, "miTabla")

Reply 2011-11-29 12:26
by Sofía M
in the Excel Forum

An example for Excel 2007:

Firstly, you should bind a name to your table in the excel file. (Formulas->name manager)
Then in your application write something like this:

select the same sheet.....

OleDbCommand objCmdSelect = new OleDbCommand(SELECT * FROM [sheet$], objConn)
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect
DataSet objDataset1 = new DataSet()

but here you select your own table.....
objAdapter1.Fill(objDataset1, miTabla)

Reply 2011-11-29 12:26
by Sofía M
in the Excel Forum

An example for Excel 2007:

Firstly, you should bind a name to your table in the excel file. (Formulas->name manager)
Then in your application write something like this:

select the same sheet.....

OleDbCommand objCmdSelect = new OleDbCommand(SELECT * FROM [sheet$], objConn)
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter()
objAdapter1.SelectCommand = objCmdSelect
DataSet objDataset1 = new DataSet()

but here you select your own table.....
objAdapter1.Fill(objDataset1, miTabla)

Reply 2011-11-29 12:27
by Sofía M
in the Excel Forum

An example for Excel 2007:

Firstly, you should bind a name to your table in the excel file. (Formulas->name manager)
Then in your application write something like this:

select the same sheet.....
SELECT * FROM [sheet$], objConn

but here you select your own table.....
objAdapter1.Fill(objDataset1, miTabla)

Reply 2011-11-29 12:28
by Sofía M
in the Excel Forum

An example for Excel 2007:

Firstly, you should bind a name to your table in the excel file. (Formulas-name manager)
Then in your application write something like this:

select the same sheet.....
SELECT * FROM [sheet$], objConn

but here you select your own table.....
objAdapter1.Fill(objDataset1, miTabla)