My code is starting to stadle a few diffrent groups, so I am not sure
if my problem is with the ADO.NET or the interop methods I am using to
define the Named range.
When I used this sample code below in an Excel file i created by hand
and gave a named range it worked. Now that I create the file with
interop and define the named region programaticly this code gives me an
exception. "{"Syntax error in INSERT INTO statement."}"
I have confirmed by that I naming the Region MyTable, so I have no
ideal this is not working.
string test ="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\Working\\OutPut.xls;Extended Properties=\"Excel 8.0;\"";
System.Data.OleDb.OleDbConnection objConn = new
System.Data.OleDb.OleDbConnection(test);
objConn.Open();
// Add two records to the table named 'MyTable'.
System.Data.OleDb.OleDbCommand objCmd = new
System.Data.OleDb.OleDbCommand();
objCmd.Connection = objConn;
for (i = 0; i < dsAudit.Tables[0].Rows.Count; i++)
{
dr = dsAudit.Tables[0].Rows[i];
objCmd.CommandText = "Insert into MyTable (EventName,
ComponentName, Status, Affiliation, LastActivityTS, Count)" +
" values ('" + dr[0] + "', '" + dr[1] + "', '" +
dr[2] + "', '" + dr[3] + "', '" + dr[4] + "', '" + dr[5] + "' )";
objCmd.ExecuteNonQuery();
}
// Close the connection.
objConn.Close();
In case your intrested here is how I am defining the named region.
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));
m_objBook.Names.Add("MyTable",@"=Sheet1!R1C1:R1C6" , true, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
Thanks,