This is a question about working with Excel from .NET so if this is the wrong newsgroup, please let me know. Technologies are .NET Framework 1.1, C#, Excel 2003.

I have a Windows Forms app that is extracting information from about 450 similarly formatted spreadsheets. So I'm opening the spreadsheet, extracting the info into a dataset, closing the spreadsheet and moving on to the next one.

I always get an error when opening some spreadsheets. It's not the spreadsheets because it fails on different ones each time through. E.g. It will fail on the twelfth one the first time, then the twenty-third one the next time, and so on.

It's not a .NET error (so I can't trap it in a try-catch block). When it fails, a dialog appears with Microsoft Visual Basic as the title (I'm using C#) and the message is "File already exists". Note that I have Interactive set to false for the Excel application.

My theory is that I'm opening and closing spreadsheets too fast because when I put a Thread.Sleep(100) between the Close/Open statements, it works (although much slower than I'd like). I suspect that Excel is creating a temporary file based on the current time when the spreadsheet is opened and isn't deleting it fast enough when it is closed. So when the next spreadsheet is opened in the same "time frame" it tries to create a new temp file with the same name.

One final note: If I don't reference the Worksheets collection of the workbook, no error occurs. It fails only when I starting working with the sheets in the workbook (even if all I do is set a variable to workbook.Worksheets.Count).

Kyle,

it may seem obvious...
have you tried opening the files as read-only?

then he has no need to 'mark' the original


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"?B?S3lsZSBCYWxleQ==?="
wrote:

This is a question about working with Excel from .NET so if this is
the wrong newsgroup, please let me know. Technologies are .NET
Framework 1.1, C#, Excel 2003.

I have a Windows Forms app that is extracting information from about
450 similarly formatted spreadsheets. So I'm opening the spreadsheet,
extracting the info into a dataset, closing the spreadsheet and moving
on to the next one.

I always get an error when opening some spreadsheets. It's not the
spreadsheets because it fails on different ones each time through.
E.g. It will fail on the twelfth one the first time, then the
twenty-third one the next time, and so on.

It's not a .NET error (so I can't trap it in a try-catch block). When
it fails, a dialog appears with Microsoft Visual Basic as the title
(I'm using C#) and the message is "File already exists". Note that I
have Interactive set to false for the Excel application.

My theory is that I'm opening and closing spreadsheets too fast
because when I put a Thread.Sleep(100) between the Close/Open
statements, it works (although much slower than I'd like). I suspect
that Excel is creating a temporary file based on the current time when
the spreadsheet is opened and isn't deleting it fast enough when it is
closed. So when the next spreadsheet is opened in the same "time
frame" it tries to create a new temp file with the same name.

One final note: If I don't reference the Worksheets collection of the
workbook, no error occurs. It fails only when I starting working with
the sheets in the workbook (even if all I do is set a variable to
workbook.Worksheets.Count).

Sorry, should have specified. I'm opening the files read-only.

Other parameters:
- UpdateLinks: false
- WriteResPassword: false
- IgnoreReadOnlyRecommended: true
- Editable: false
- Notify: false
- AddToMru: false
- Local: false

All other parameters are set to System.Reflection.Missing.Value

i'm sure you've disabled:

Application
.enableevents = false


BUT what are your settings for autorecover?
(it's played havoc on some of my apps)

.autorecover.enabled=false


either disable application.autorecover
or set the workbooks property after opening

Workbook.enableautorecover = false

else have a look at any other workbook properties that just might
slow you down...

sorry, that's it from me :-(



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"?B?S3lsZSBCYWxleQ==?="
wrote:

Sorry, should have specified. I'm opening the files read-only.

Other parameters:
- UpdateLinks: false
- WriteResPassword: false
- IgnoreReadOnlyRecommended: true
- Editable: false
- Notify: false
- AddToMru: false
- Local: false

All other parameters are set to System.Reflection.Missing.Value

Good suggestions. I hadn't disabled events or auto-recover but it didn't help. Try some other options but they other made no difference or cause COM exceptions. Even tried setting ReadOnly to true but alas.

Good suggestions. I hadn't disabled events or autorecover but doing so didn't help. Tried some other options in the Open event and even tried setting ReadOnly to false but they other didn't work or caused COM exceptions.

including (some) doevents might work wonders.
(dont know the c equivalent, I'm just a simple VBA hack)


it will give either excel enough ticks to to complete calculations,
and you should allow some ticks to the OS to finish it's filehandling.

(dont know what your data entails :)

also make sure your temp directory is cleaned regularly....
no joke :(

depending on the contents of files (embedded ole objects/pictures) excel
can dump an awfull amount of data in temp... and certainly when developing
(and crashing).. it tends to leave 'some of these' files behind...


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"?B?S3lsZSBCYWxleQ==?="
wrote:

Good suggestions. I hadn't disabled events or autorecover but doing so
didn't help. Tried some other options in the Open event and even tried
setting ReadOnly to false but they other didn't work or caused COM
exceptions.

Kyle,

Just another thought. It is possible to query information from an Excel
file directly using ADO. That way you can avoid opening the workbook in
Excel. The ADO query will return a recordset. Depending upon the structure
of the data in your worksheets this may be another possibility. It might
even be a little faster.

Here are some Microsoft KB articles that may be of interest:

http://support.microsoft.com/default.aspx?kbid=306572

http://support.microsoft.com/default.aspx?kbid=316934

http://support.microsoft.com/default.aspx?kbid=311731

http://support.microsoft.com/default.aspx?kbid=278973

http://support.microsoft.com/default.aspx?kbid=190195

http://support.microsoft.com/default.aspx?kbid=195951

Troy


"Kyle Baley" wrote in message
...
This is a question about working with Excel from .NET so if this is the
wrong newsgroup, please let me know. Technologies are .NET Framework 1.1,
C#, Excel 2003.

I have a Windows Forms app that is extracting information from about 450
similarly formatted spreadsheets. So I'm opening the spreadsheet, extracting
the info into a dataset, closing the spreadsheet and moving on to the next
one.

I always get an error when opening some spreadsheets. It's not the
spreadsheets because it fails on different ones each time through. E.g. It
will fail on the twelfth one the first time, then the twenty-third one the
next time, and so on.

It's not a .NET error (so I can't trap it in a try-catch block). When it
fails, a dialog appears with Microsoft Visual Basic as the title (I'm using
C#) and the message is "File already exists". Note that I have Interactive
set to false for the Excel application.

My theory is that I'm opening and closing spreadsheets too fast because
when I put a Thread.Sleep(100) between the Close/Open statements, it works
(although much slower than I'd like). I suspect that Excel is creating a
temporary file based on the current time when the spreadsheet is opened and
isn't deleting it fast enough when it is closed. So when the next
spreadsheet is opened in the same "time frame" it tries to create a new temp
file with the same name.

One final note: If I don't reference the Worksheets collection of the
workbook, no error occurs. It fails only when I starting working with the
sheets in the workbook (even if all I do is set a variable to
workbook.Worksheets.Count).

Hi Kyle,

This is a question about working with Excel from .NET so if this is the wrong newsgroup, please let me know. Technologies are .NET Framework 1.1,
C#, Excel 2003.

Most of the Excel/.Net experts hang out in Public.VSNet.VSTools.Office, so you might get better advice there. My initial reaction was "Garbage
collection". After finishing with each sheet, you might need to ReleaseCOMObject on it, then (maybe) do a GC.Collect too. But that's only a *wild*
guess.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.ie

Oh bother, I forgot that. Thanks, Troy

Interestingly, ReleaseCOMObject didn't do any good but GC.Collect fixed it. I took out the Thread.Sleep call and processing time dropped 40%. I think I'm going to switch to ADO.NET to get the info I need but this will come in handy when I start the more complicated stuff. Thanks.

Not sure if it's been mentioned here but this article helped out, too:
http://blogs.officezealot.com/whitec...s/2004_02.html