1

I am trying to SaveAs() a sheet to csv file and immediately after load that csv file into string, using C# VSTO. The issue is that unless I Close() the workbook I can't access newly created csv file. The error is related to "file locked" issue. If I close the workbook or copy it to blank workbook then I always able to access that file, however these are not ideal solutions. Any ideas, on how to avoid file lock? 

   Excel.Worksheet sheet = workbook.Sheets[sheetName];
   sheet.Activate();
   sheet.Unprotect();
   sheet.SaveAs(fileName, Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);

   workbook.Close();  <-- Don't want that happen

  // Get the Input CSV.
   string contents = File.ReadAllText(InputCsvFileName);
Jim
  • 2,760
  • 8
  • 42
  • 66
  • You want to read a file locked by another process. Try this solution. http://stackoverflow.com/questions/3560651/whats-the-least-invasive-way-to-read-a-locked-file-in-c-sharp-perhaps-in-unsaf – Jules Jan 21 '16 at 23:44
  • I can't see what this question has to do with VBA? Why the VBA tag? If VBA is not involved, please EDIT the question and remove the tag... – Cindy Meister Jan 22 '16 at 17:48
  • @CindyMeister because the same thing happening with VBA as well. – Jim Jan 22 '16 at 18:16

1 Answers1

3

The reason for this @Jim is that with the SaveAs method you are no longer in the 'old' -source- Worksheet but in the file you -targeted- in the SaveAs. Keep in mind that the changes made -after- opening the 'source' workbook are not persisted in the 'source' but are persisted in the 'target' (SaveAs) workbook.

So if you started working in WorkbookA.xlsm, activated SheetA, Saved SheetA as SheetA.csv you are now in SheetA.csv in Excel.

(Note: If you do this manually or by running VBA manually this has a strange side effect that if your source contained two sheets the .csv also appears to have two sheets but after re-opening the .csv only the saved sheet was persisted)

From this you can say that if you plan to continue to work in the -source- the meta steps are:

  • Open Source
  • Make changes
  • Save Source (optional - only if you want the changes in the source)
  • SaveAs .csv (you are now in the .csv)
  • Close .csv
  • Re-open Source
  • Continue to work on Source
  • Open .csv file to do whatever you planned to do
Maarten van Stam
  • 1,901
  • 1
  • 11
  • 16
  • Thank you for the explanation. So basically the process I am following is correct: copy sheet to blank workbook then saveas and close it then I can access that file. – Jim Jan 28 '16 at 11:16