Excel: can only open file if using absolute path,

Question

I have some trouble to understande why I´m getting an exception. I have something like this:

string path = "file.xls";
if (File.Exists(path))
{
  Excel.Application xlApp = new Excel.Application();
  Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(path); //exception
  //...
}

Exception:

Unhandled Exception: System.Runtime.InteropServices.COMException: 'file.xls' could not be found

Well that´s why I´m checking with File.Exists, so I dont get this exception. So how does this work, File.Exists is true, but the file still cannot be found? If I´m using an absolute path, then it´s working. Why? I would like to use this without the absolute path, any ideas? Thank you

Edit: of course the file.xls is in the same folder as my .exe -> that´s why (as expected) File.Exists is returning true. Just wanted to make this clear ;)

Answer 1

This happens because there are two processes involved, each of which has their own Current Working Directory (CWD).

Your process (the one calling File.Exists()) has a CWD which happens to hold the file that you're using. Excel has a different CWD (probably the location of the Excel executable) which of course doesn't hold the file.

You can fix this by using:

path = System.IO.Path.GetFullPath(path);

before passing path to Workbooks.open(path)

It might be possible to change Excel's CWD by calling a macro using ExecuteExcel4Macro.

See here for some details: Set Current Directory in Excel.Application via .NET Office PIA

Answer 2

This is because Excel is another process than your .exe and the filename parameter in Workbook.Open has slightly different behavior than the filename parameter in File.Exists.

When you call File.Exists, the filename parameter may be either absolute or relative. If you use relative, it is relative to your .exe, so it will find the file placed in your .exe's folder.

When you create an Excel.Application object you get a new process with a separate working directory from your .exe. Note also that when you pass a relative filename path to the Workbook.Open function, it will not look for the file in Excel's working directory, but will instead use the default document folder for Office (typically "My Documents").

So, you should always use the absolute path in this scenario.

Answer 3

i think it is because xlApp.Workbooks.Open function only knows the absolute path,not likes File.Exists function

Answer 4

I found Matthew Watson's answer very helpful. It's also useful to set a relative path for Workbooks.Add() method. Here is my code for reference:

Excel.Application xlApp = new Excel.Application();

string path = "TestTemplete.xltx";
path = System.IO.Path.GetFullPath(path);
Workbook wb = xlApp.Workbooks.Add(path);