CODEWITHSUNDEEP
c#excelvbavsto
Jim
Jim

Reputation: 2828

VSTO SaveAs file locked

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);

Upvotes: 1

Views: 646

Answers (1)

Maarten van Stam
Maarten van Stam

Reputation: 1899

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

Upvotes: 3

Related Questions