Excel References problem in C# code

Question

I have a spreadsheet that is using the WORKDAY() function in Excel (which I believe lives in the Analysis Toolpak library/package/whatever you call it in Excel). When I open this spreadsheet manually and call this function it behaves fine.

I have a macro that references the cell that the WORKDAY() function is used in:

Dim startDate as Date
startDate = Cells(1,1)

When I run the macro after opening the spreadsheet manually this code executes fine. The problem is when I try and open the spreadsheet and run the macro from my C# code. It will then fail when it tries to get the value for startDate. When I look at that cell I see a #VALUE! reference error. It seems like it is not loading the correct references when I open the spreadsheet from my C# code.

Does anyone know how to resolve this issue?

Could it be a problem with macros being disabled in general when opening through C#? Can you confirm that you can run any macro at all? — Eric J.
– Eric J., Commented Sep 15, 2011 at 14:18
The macro runs. It stops halfway through the macro on the line that tries to set the date. — Dismissile
– Dismissile, Commented Sep 15, 2011 at 14:28
If you open Excel via automation it doesn't automatically loads add-ins: you need to do that in your code. support.microsoft.com/kb/213489 — Tim Williams
– Tim Williams, Commented Sep 15, 2011 at 18:03

John Alexiou · Accepted Answer · 2011-09-15 18:13:20Z

3

What I had to do is force the loading of the .xla add-in using:

app.Workbooks.Open(file_name)

where app is an instance of Microsoft.Office.Interop.Excel.Application if it already open then nothing happens, otherwise it loads the analysis addin. To find the path for the add-in it depends on the version of Office

Office 11: C:\Program Files\Microsoft Office\OFFICE11\Library\Analysis\ANALYS32.XLL
Office 12: C:\Program Files (x86)\Microsoft Office\Office12\Library\Analysis\ANALYS32.XLL

answered Sep 15, 2011 at 18:13

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel References problem in C# code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related