COMException: Cannot access filename.xls

[bullpit]

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://
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\\Mayank
Sharma\\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 + "" + 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 + "" + i + "] VALUES ('" +
dataGridView1.Rows[rowNumber].Cells[0].Value.ToString() + "','')";
cmd.ExecuteNonQuery();
i++;
cmd.CommandText = "INSERT INTO [Product_Pricing$C" +
i + "" + i + "] VALUES ('" +
dataGridView1.Rows[rowNumber].Cells[1].Value.ToString() + "','')";
cmd.ExecuteNonQuery();
i++;
}
do
{
cmd.CommandText = "INSERT INTO [Product_Pricing$C" +
i + "" + 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();

}

 

[Cindy M.]

Hi =?Utf-8?B?YnVsbHBpdA==?=,

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.

Excel.Workbook m_objBook =
excelApp.Workbooks.Open(workbookPath,
0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);

Mmmm. Compare the values you're passing to the Open method with the
information in the Help files. I'm surprised you're not getting compile
errors with C#. Always better to pass Type.Missing if you don't want to
specify a value for an argument, and if you let Excel default, it will do
it's best to make sure things work. Especially that last value, for
CorruptLoad, I'm thinking there might be a problem there.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[bullpit]

I really appreciate the time you give to these forums.

I was doing some testing and found out this: The file I am trying to access
is given to me by our sales department. It had a lot of records in it and I
had to use it as a blank template. So I deleted all the records and kept the
structure as it was. Now when I try to manipulate that template when it is
not opened manually by me, it gives that "Cannot access" error. I made
another excel file and copied the structure to that file and it works fine.
Can it be some security issue (may be the file is protected or something).
Also, I am in a process of building an application to manipulate that
existing template. Am I on the right track. I was trying to insert values
using OLEDB and then do some formating to it using Automation.
P.S. I would love to access the columns by field names in the following query:

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

Is it possible? Otherwise I would have insert blank values to some hidden
columns in between the range and they are a lot. The un-named range in this
query is just a small part of the whole range.

 

[Cindy M.]

I was doing some testing and found out this: The file I am trying to access

is given to me by our sales department. It had a lot of records in it and I
had to use it as a blank template. So I deleted all the records and kept the
structure as it was. Now when I try to manipulate that template when it is
not opened manually by me, it gives that "Cannot access" error. I made
another excel file and copied the structure to that file and it works fine.
Can it be some security issue (may be the file is protected or something).

I think it more likely that the binary file structures have been damaged. That's
why I encouraged you to test with passing the neutral System.Type.Missing for
arguments you don't want to set explicitly. Excel will then use its defaults
(the ones it uses when you work in the UI) and work around the problems.
However, if this should serve as a file template, it's certainly wiser to start
fresh!

Also, I am in a process of building an application to manipulate that
existing template. Am I on the right track. I was trying to insert values
using OLEDB and then do some formating to it using Automation.
P.S. I would love to access the columns by field names in the following query:

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

Is it possible? Otherwise I would have insert blank values to some hidden
columns in between the range and they are a lot. The un-named range in this
query is just a small part of the whole range.

Since the possibilities open to you depend very much on the version of Excel
you're targeting, I wouldn't want to say anything at this point without that
information. Certainly, the approach you outline is possible, but might not be
optimal.

I'm afraid I'm not really competent to answer your last question about the
syntax of the query. As a "Wordie" (albeit with a strong data background), I'm
already on shaky ground giving you any advice at all

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[bullpit]

Since the possibilities open to you depend very much on the version of Excel

you're targeting, I wouldn't want to say anything at this point without that
information. Certainly, the approach you outline is possible, but might not be
optimal.

To start with, I am using Excel 2002 SP3.

I'm afraid I'm not really competent to answer your last question about the
syntax of the query. As a "Wordie" (albeit with a strong data background), I'm
already on shaky ground giving you any advice at all

You advice has helped me a lot. About the syntax, is there any place I can
find documentation because the links you gave did not have anything and the
stuff I found didn't have anything either.

And I am still struglling to find why my application gives errors while
inserting some records but runs smoothly inserting others.

Thanks a lot....

 

[Cindy M.]

Hi =?Utf-8?B?YnVsbHBpdA==?=,

I am using Excel 2002 SP3.

Mmm, then you don't have much choice. With that version it is possible to
save/generate a workbook as XML, but the XML is rather basic. If it's just
data with some cell formatting though (no pictures, charts, etc), it would
be worth looking into.

About the syntax, is there any place I can
find documentation

The folks in excel.programming couldn't help you? That surprises me,
because I'm pretty sure they do some OLE DB stuff... Just don't mention C#
to them. Talk as if you're using classic VB

Another resource would be the data.oledb newsgroup. Been a long time since
I've been there, but there was usually someone around with a basic grasp
of Excel + ADO

And I am still struglling to find why my application gives errors while
inserting some records but runs smoothly inserting others.

Well, whenever this kind of thing happened to me, it was usually a
datatype mismatch of some kind.

What I might do, looking back at your code, is to create variables to hold
each part of the string. Concatenating it all in the call makes it
difficult to trace what might be "odd". It also strikes me as odd that the
query string apparently ends in a comma?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[bullpit]

What I might do, looking back at your code, is to create variables to hold

each part of the string. Concatenating it all in the call makes it
difficult to trace what might be "odd". It also strikes me as odd that the
query string apparently ends in a comma?

I have posted in the groups you suggested but not in all otherwise I will
get a reply about duplicate post. About that odd comma ",": it is not the
comma I am inserting but a blank value that follows it. The way I am hanlding
the range exapansion from code, it does not allow me to use some fields in
the unnamed range and leave rest. I have to supply values of all the fields
in the range, so I insert a blank one if I dont have any value for it. That
is why I have started looking into Named range but with it, I am having a
problem that it gives an error of "Cannot expand named range" after I insert
1 record. You mentioned in one or you replies that I can make a range
dynamic. Can you shed some more light on it?

 

[Cindy M.]

Hi =?Utf-8?B?YnVsbHBpdA==?=,

You mentioned in one or you replies that I can make a range
dynamic. Can you shed some more light on it?

You really need to discuss this with someone who specializes
in Excel. Really. The folks in the Excel groups can tell you
what the object model supports, and what to use best, as far
as automisation goes.

Please note that "dynamic range" when discussing automation
does not necessarily mean the range will expand dynamically
when you plug information in using OLE DB.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun
17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[bullpit]

You really need to discuss this with someone who specializes

in Excel. Really. The folks in the Excel groups can tell you
what the object model supports, and what to use best, as far
as automisation goes.

Yes, I understand, and am waiting for replies to my posts in those groups.

Please note that "dynamic range" when discussing automation
does not necessarily mean the range will expand dynamically
when you plug information in using OLE DB.

I found that out the hard way. Really, no one out there who has a solution
for this or even an explaination whether it can be done or not. The Microsoft
article for working with ADO and excel is informative but does not exapand on
this particular error.

 

[bullpit]

And this requirement to expand a named range is fairly common but no one has
a solution. I don't understand that when I can manipulate an unnamed range by
using counters in my code, why isn't there a solution for a named range.

 

[Cindy M.]

Hi =?Utf-8?B?YnVsbHBpdA==?=,

And this requirement to expand a named range is fairly common but no one has
a solution. I don't understand that when I can manipulate an unnamed range by
using counters in my code, why isn't there a solution for a named range.

Assuming we're still discussing OLE DB (and not automation): because a named
range is defined as a specific set of cells. Internally (using automation or
the UI), this can be changed; but not externally (at the "database" level,
where OLE DB works).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[bullpit]

Assuming we're still discussing OLE DB (and not automation): because a named
range is defined as a specific set of cells. Internally (using automation or
the UI), this can be changed; but not externally (at the "database" level,
where OLE DB works).

Yes, we are still discussing OLE DB. Understood that we cannot control the
named range from outside.
Even in this unnamed range, I am refering to a specific set of cells, but I
can still control it.

INSERT INTO [Product_Pricing$C" + i + ":E" + i + "] VALUES('Part1',
'Description','1')";

And now I am beginnig to believe that there is no solution for my problem. I
never knew working with an excel file would be such a pain.

I was hoping that the driver treats a named range similar to how it treats
an excel sheet (like a table) and keeps expanding automatically.