none
COMException: Cannot acces file.xls RRS feed

  • Question

  • Hi,
    I am building an application in C# to Read/Write an Excel workbook. The problem is that the application is able to access the workbook only when I open it manually. Otherwise, it gives me COMException that it cannot access the workbook. Heres my code:

    Code Snippet

    private void button2_Click_1(object sender, EventArgs e)

    {

    string connectionString = @"Provider=Microsoft.Jet.OleDb.4.0;Data Source=template.xls;Extended Properties=""Excel 8.0;HDR=YES;""";

    System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString);

    conn.Open();

    OleDbCommand cmd = new OleDbCommand();

    cmd.Connection = conn;

    int catCount = 1; //To store number of different categories

    int rowNumber = 0; //to store index of the last row read in a category block

    int temp = -1;

    for (int k = 0; k <= dataGridView1.Rows.Count - 2; k++)

    {

    if (dataGridView1.Rows[k].Cells[0].Value.ToString() != dataGridView1.Rows[k + 1].Cells[0].Value.ToString())

    {

    catCount++;

    }

    }

    Excel.Application excelApp = new Excel.ApplicationClass();

    string workbookPath = "C:\\Documents and Settings\\My Documents\\Visual Studio 2005\\Projects\\salesTemplate\\salesTemplate\\bin\\Debug\\template.xls";

    Excel.Workbook m_objBook = excelApp.Workbooks.Open(workbookPath,

    0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",

    true, false, 0, true, false, false);

     

    for (int k = 1; k <= catCount; k++)

    {

    //write Category

    if (dataGridView1.Rows[rowNumber].Cells[0].Value.ToString() == dataGridView1.Rows[rowNumber].Cells[1].Value.ToString())

    {

    cmd.CommandText = "INSERT INTO [Product_Pricing$C" + i + ":D" + i + "] VALUES ('" + dataGridView1.Rows[rowNumber].Cells[0].Value.ToString() + "','')";

    cmd.ExecuteNonQuery();

    i++;

    }

    //write Category and Subcategory

    if (dataGridView1.Rows[rowNumber].Cells[0].Value.ToString() != dataGridView1.Rows[rowNumber].Cells[1].Value.ToString())

    {

    cmd.CommandText = "INSERT INTO [Product_Pricing$C" + i + ":D" + i + "] VALUES ('" + dataGridView1.Rows[rowNumber].Cells[0].Value.ToString() + "','')";

    cmd.ExecuteNonQuery();

    i++;

    cmd.CommandText = "INSERT INTO [Product_Pricing$C" + i + ":D" + i + "] VALUES ('" + dataGridView1.Rows[rowNumber].Cells[1].Value.ToString() + "','')";

    cmd.ExecuteNonQuery();

    i++;

    }

    do

    {

    cmd.CommandText = "INSERT INTO [Product_Pricing$C" + i + ":D" + i + "] VALUES ('" + dataGridView1.Rows[rowNumber].Cells[2].Value.ToString() + "','" + dataGridView1.Rows[rowNumber].Cells[3].Value.ToString() + "')";

    cmd.ExecuteNonQuery();

    i++; //Excel region cell increment

    rowNumber++;

    temp++;

    if (temp == dataGridView1.Rows.Count - 1)

    {

    break;

    }

    }

    while (dataGridView1.Rows[temp].Cells[0].Value.ToString() == dataGridView1.Rows[temp + 1].Cells[0].Value.ToString());

    i++;

    }

    cmd.Dispose();

    conn.Close();

    conn.Dispose();

    excelApp.Save("template.xls");

    excelApp.Quit();

    }

     

    Thursday, June 7, 2007 4:13 PM