3

I have a process that builds an Excel report, then opens it for the user. The problem is if someone leaves the file open, it stays locked and nobody else can build the report until the first person exits the excel file.

Is there a way to open an Excel file without locking it, using either Process.Start or Microsoft's Interop.Excel library?

I use the Interop library to build the file each time the report is run, and save it as a static file name in a shared network folder where this application is run from

using Excel = Microsoft.Office.Interop.Excel;

...

xlsBook.SaveAs(newFileName, Excel.XlFileFormat.xlWorkbookNormal);

And open the file using Process.Start

Process.Start(newFileName);
Rachel
  • 130,264
  • 66
  • 304
  • 490
  • You can have a shared workbook, but it may not suit: http://office.microsoft.com/en-ie/excel-help/use-a-shared-workbook-to-collaborate-HP010096833.aspx#BMshareworkbook – Fionnuala Dec 10 '12 at 14:57
  • @Remou Thanks, but each person running the report overwrites the file with a new copy containing new data, so I don't think sharing will work. – Rachel Dec 10 '12 at 14:58
  • You can open excel files readonly which I assume doesn't lock it but I don't know. I'm not sure if there are command line equivalents that will do this. Another method that springs to mind is to have a "helper" excel file that has macros in it to do magic, either in terms of opening the file you want read only or it uses the file that you have generated as a datasource rather than s the main report file. And of course depending on what you are updating and how you could just use excel's built in data functionality to get datasets from the DB to put into your sheet. – Chris Dec 10 '12 at 15:02

2 Answers2

8

You can try to open the file in read-only mode:

var app = new Microsoft.Office.Interop.Excel.Application();
var workbook = app.Workbooks.Open(filename, ReadOnly: true);

Or you can try to save it in shared mode:

workbook.SaveAs(filename, AccessMode: XlSaveAsAccessMode.xlShared);
Oliver
  • 43,366
  • 8
  • 94
  • 151
  • Thanks, opening the file with `ReadOnly` works, although I had to set `app.Visible = true` to show Excel – Rachel Dec 11 '12 at 13:14
3

If the end user only has to read the file instead of also modifying it, you could create a shadow copy and then open that copy.

Simply copy the original file to a temporary location and open it from there. The original file remains untouched and can thus be opened by others.

Roy Dictus
  • 32,551
  • 8
  • 60
  • 76
  • This file gets created daily at a minimum with different data each day, and I didn't want to have a huge temp folder containing copies of the report for every time it was run – Rachel Dec 10 '12 at 14:57
  • 1
    Temporary files are stored locally (at least they should be), so you can make sure you auto-delete any old copies at the appropriate time. – Roy Dictus Dec 10 '12 at 14:58
  • You can avoid creating a full copy by creating a [hard link](http://stackoverflow.com/questions/3387690/how-to-create-a-hardlink-in-c) instead. – Scott Miller Dec 10 '12 at 14:59
  • @ScottMiller: Would a hard link help? Even if it gets round the problem the original is not able to be opened read/write surely if you then get two people trying to write to it then it is just going to go horribly wrong... – Chris Dec 10 '12 at 15:04
  • 1
    @RoyDictus Thanks, I copied the file to the user's local temp folder (`Path.GetTempPath()`) and opened it from there. I forgot I was modifying an XBAP which has access to the file system instead of a regular asp.net web app. Accepting your answer unless someone can tell me of a way to use `Process.Start` or the Interop library to open a file without locking it :) – Rachel Dec 10 '12 at 15:07
  • 1
    @ScottMiller: Hard links won't help. They only work on the same drive and also they point to the same file -> If you open the file exclusive, trying to open it through the hard link will fail. – Oliver Dec 10 '12 at 15:09