CODEWITHSUNDEEP
.netexcelvbafilesystemspia
Culondo
Culondo

Reputation: 11

Set Current Directory in Excel.Application via .NET Office PIA

I want to create an Excel spreadsheet from a .NET Application and then set the Excel.Application instance current directory to a custom folder. I want to do this so when the user clicks the save button in Excel, the Save as dialog is already located in the correct directory.

I know it's possible to change the current directory in Excel Instance with VBA.FileSystem.ChDir and doing it within the Excel.Application instance with VBA Code/Macro like this:

  1. Create an Excel Spreadsheet from C# .NET

    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Add();
excel.Visible = true;

  2. When Excel spreadsheet is open, hit ALT + F11. Then create and run following macro 

    Sub SetChdir()
   Call FileSystem.ChDir("C:\To\My\Custom\Directory\")
End Sub

  3. When clicking Save in Excel the Current Directory is set path set with ChDir

I can't figure out, or even if it's possible, how to create this behaviour from .NET. I tired messing around with Excel.DefaultFilePath which Excel uses on startup to set the Current Directory. But it has two problems:

Upvotes: 1

Views: 2634

Answers (1)

Torben Klein
Torben Klein

Reputation: 3136

There is another way.

excel.ExecuteExcel4Macro("DIRECTORY(\"C:\\To\\My\\Custom\\Directory\")");

Upvotes: 1

Related Questions