I've been successfully starting Excel and opening a workbook from C# without problems for several weeks. Today, one of our developers made some changes to the Excel workbook we automatically open by adding a some VBA code to the workbook. Specifically, he added a function in a Module called Module1, with the following code:
Function numdays_inmonth(mydate As Variant)
    tmpdate = Year(mydate) & "-" & (Month(mydate) + 1) & "-" & 1
    tmpdate = DateAdd("d", -1, tmpdate)
    numdays_inmonth = Day(tmpdate)
End Function

One of the cells in the workbook uses this function. If I open the file by double clicking on it from explorer, it opens and runs perfectly fine. If I use the following code to open it, I get the "Can't find project or library"

 

ExcelDataForAccount excelData = new ExcelDataForAccount();
m_excelByAccountID.Add(a.AcctID, excelData);
excelData.Application = new Microsoft.Office.Interop.Excel.Application();
excelData.Application.Visible = true;
excelData.Application.WorkbookBeforeClose += new Microsoft.Office.Interop.Excel.AppEvents_WorkbookBeforeCloseEventHandler(Application_WorkbookBeforeClose);
string destinationFile = GetGreeksSheetForAccountFileName(a);
string destinationDoc = System.IO.Path.Combine(GetLocalFileCachePath(), destinationFile);

System.IO.

File.Copy(m_configuration.TemplateFileLocation, destinationDoc, true);
excelData.WorkbookFilename = destinationFile;
MSExcel.Workbook w = null;

 

try
{

w = excelData.Application.Workbooks.Open(destinationDoc,

false, false,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,true,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);

excelData.Workbook = w;
excelData.Account = a;

// Using a cast here to avoid compiler warning regarding the Excel.Worksheet class inherits from multiple interfaces.
// _Worksheet, which has a method Activate, and WorkbookEvents_Event which has an event called Activate
((MSExcel._Worksheet)(excelData.MainWorksheet)).Activate();

What could be causing this? I have tried changing the function to this:
Function numdays_inmonth(mydate As Variant)
    Dim tmpdate as Date
    tmpdate = Year(mydate) & "-" & (Month(mydate) + 1) & "-" & 1
    tmpdate = DateAdd("d", -1, tmpdate)
    numdays_inmonth = Day(tmpdate)
End Function

which will allow me to continue though that function, but then I get the same error over and over in various parts of the VBA code. Usually, adding Dim statements fixes the problem, but I hit a line in the VBA where a call to the Str function fails and nothing but commenting out the line of code in VBA seems to help

Any ideas? I'd like to know the cause as much as a workaround, because I would hate it to happen when we are in production. This Excel file is changed often and not always tested against the applicaiton which launches it.

Technically, your question is off-topic in the VSTO forum as you're not using VSTO, but automating the Excel application. (VSTO runs in-process with an application, as an add-in or file customization.) The forum's Please Read First message contains links to venues for non-VSTO, Office-related questions. No matter where you post, it's important to always mention the version of the Office application involved.

You also don't clearly explain where/when you see the error. Whether a message is popping up when you open the workbook? An error message in the cell where the formula is used? Or...?

In any case, it appears the problem is VBA-related. From basic VBA experience, it sounds like the VBA project in the Excel file is unable to locate a Reference (as in Tools/References). It's difficult to say why this might be, especially if there are no references to "non-standard" libraries (Excel's, Office's, VBA and MSForms type libraries are "standard"), which VBA should be able to locate with no difficulties. When the workbook is opened with the problem, Alt+F11 (open the VBA editor), Tools/References and see what is listed as a "missing" project in the list.

I do notice that the file path to the Excel workbook appears to be variable. This could have something to do with the problem. Did your test opening the workbook manually include opening it from such a location? If not, you could be looking at a permissions issue in Windows, or perhaps VBA for some reason isn't able to access the locations of the type libraries it's looking for?

---

Cindy Meister, VSTO/Word MVP

I looked at the links to the non-VSTO groups, but I didn't see anything that is supposed to cover Excel automation. Could you point me to the right place?

To follow up, I'm using Office 2007 and the error that pops up is in the VBA editor which opens up when the cell is calculated. The error keeps popping up, so I can't even Alt+F11 to check the references. As I pointed out in my first post, I seem to be able to get rid of most of the errors by adding Dim statements.

The problem doesn't seem to be related to the path either, because I tested just opening the file in the location that the program opens it up from by running Excel and then explicitly opening the file.

My main question, is what the difference is between opening Excel via the Start menu and starting Excel using Automation using the code I show above?

In the Please Read First message, scroll down until you find "Excel". I'd then ask in the XTreme forum.

I'm not following exactly what happens, but if you can click Debug in the error message you're seeing that should get you into the VBA Editor, with the macro paused. From there, you should be able to select the procedure and apply "Comment" formatting to it so that it will stop executing. That should let you get into Tools/References.

I'm not familiar enough with all the ins-and-outs of Excel's Open method that I could begin to guess why opening the workbook using automation would be different from opening it as a user.

---

Cindy Meister, VSTO/Word MVP