diiN__________ diiN__________ - 4 years ago 85
C# Question

Running macros through code on server: Wrong path to excel file?

In my application I open a .xlsm file with a macro (copies data to .xlsm file) and a .xlsx file that executes that macro. Let's say my files are called

FileWithMacro.xlsm
and
FileThatExecutesMacro.xlsx
. I need this application on a server but have developed it locally. To debug the application, I put my files to
C:\Users\myUser\Documents\
.

I'm opening the
FileWithMacro.xlsm
like this:

Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open(serverPath + "FileWithMacro.xlsm");
Excel._Worksheet worksheet = workbook.Sheets["FileThatExecutesMacro"];


After that, I call the method that runs the macro:

RunMacro(serverPath + "FileThatExecutesMacro.xlsx");


My method looks like this:

private static void RunMacro(string source)
{
Excel.Application excel = new Excel.Application();
Excel.Workbook workbook = excel.Workbooks.Open(source);
Excel._Worksheet worksheet = workbook.Sheets[1];
worksheet.Activate();

try
{
excelThatRunsMacro.Run("FileWithMacro.xlsm!MyMacro");
workbook.Save();
excelThatRunsMacro.Quit();
}
// and so on
}


Locally the macros work without any problems. When I change all the paths and copy the output of my code to the server (\\myServer\someUrl...), I'm getting the following exception:


'C:\Users\myServerUser\Documents\FileWithMacro.xlsm' could not be found.


Although I changed all my paths to the correct server location (and I am really sure that there is no local path in my code and in the macro anymore). I even deleted my
bin
and
obj
folders and rebuilt the project but that didn't change anything. I even created a new project and built it without using local paths but I got the same error. What am I doing wrong? Is
excel.Run
by default searching the macro in the
Documents
folder?

Or is the problem because of the line
Windows("FileWithMacro.xlsm").Activate
in my macro? When running the macro manually, it works fine. It just doesn't work when running it through the code.

Answer Source

I couldn't find out why I got the exception. However, I've found a workaround. Before executing the macro, I copy the FileWithMacro.xlsm to the location in the error message. When I then used the code like I've had it, I've got a message that the FileWithMacro.xlsm is already in use. To fix this, I just had to pass the Excel.Application to my method and use this instance instead of creating a new one:

private static void RunMacro(Excel.Application excel, string source)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download