1

I'm trying to run an Excel Macro called Sheet1.Workbook_Open from a .NET web system.

Below is my current code.

xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Open("C:\Testing\TempFile.xlsm")
xlWorkSheet = xlWorkBook.Worksheets("TestSpreadsheet")

Dim MacroName As String = "Sheet1.Workbook_Open"

xlApp.Run(MacroName)

Dim FileDate As String = SysdateTextBox.Text
FileDate = FileDate.Replace(" ", "")
FileDate = FileDate.Replace("/", "")
FileDate = FileDate.Replace(":", "")

FileName = "C:\Testing\File" & FileDate & ".xlsm"

xlWorkBook.SaveAs(FileName)
xlWorkBook.Close(True)
xlApp.Quit()

However, it fails on the line xlApp.Run(MacroName) with the error;

Exception from HRESULT: 0x800A01A8 

System.Runtime.InteropServices.COMException: Exception from HRESULT: 0x800A01A8

Why is this happening, and how can I fix it? This error doesn't happen when the project is hosted on my local machine - only when it is on the server.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Dr R Dizzle
  • 274
  • 2
  • 4
  • 20
  • Is Microsoft Office and Excel installed on the server? If it is, is it the same version as what you have on your local machine? – Tim Dec 16 '14 at 10:16
  • Excel is installed on the server, and it is the same version. – Dr R Dizzle Dec 16 '14 at 10:17
  • What account is used to run the code? On your machine it might be you as local admin and on the server? Have you checked event logs? Maybe there you could find some more information. – Daniel Dušek Dec 16 '14 at 10:34
  • @dee A custom account called ASPNET that I set up specifically to have required access runs the code and opens Excel. Before the code I provided, the system adds data to some cells of a template file and saves it as the TempFile, so I am fairly confident that it isn't a permissions issue. I have no idea where I meant find any event logs. – Dr R Dizzle Dec 16 '14 at 10:41
  • I do not know what server you use, but ... http://en.wikipedia.org/wiki/Event_Viewer – Daniel Dušek Dec 16 '14 at 10:44
  • @dee All the Event Viewer logs are telling me is that the application was stopped at certain times by the error I am receiving. – Dr R Dizzle Dec 16 '14 at 10:47
  • Have you tryid to use the macro-name like this: ```Application.Run "'TempFile.xlsm'!MyMacroName"```? Because ```COM Exception 0x800A01A8``` you got has description “You are trying to access an object that does not exist“. So maybe the macro can't be found? – Daniel Dušek Dec 16 '14 at 10:50
  • @Dee Is the syntax you provided the correct way to do that, or is that jus a pseudo-code example? – Dr R Dizzle Dec 16 '14 at 10:53
  • 1
    http://stackoverflow.com/questions/2136768/using-application-run-in-excel-vba-when-workbook-name-contains-spaces ... you probably do not need the single quotes, but try to specify the name of the workbook, like this ```Application.Run "TempFile.xlsm!MyMacroName"``` – Daniel Dušek Dec 16 '14 at 10:55
  • @Dee It doesn't work. This only works in VBA, not .NET - .NET requires that calls be enclosed in brackets, and when I do that, the error I keep getting is still there. – Dr R Dizzle Dec 16 '14 at 11:01
  • `Workbook_Open` is an event procedure and it runs automatically as soon as you open to workbook. you don't need to call it explicitly. –  Dec 16 '14 at 11:01
  • @vba4all The workbook is only being opened by .NET, not an actual human. Will this still work? – Dr R Dizzle Dec 16 '14 at 11:04
  • @DrRDizzle still, it gets opened because you are using the `Workbooks.Open()` method. [Look it up if you are unsure](http://msdn.microsoft.com/en-gb/library/microsoft.office.interop.excel.workbooks.open.aspx) but I am like 99% sure it runs the `Workbook_Open()` automatically –  Dec 16 '14 at 11:07
  • @vba4all I tried changing the name of the procedure in my spreadsheet and implicitly calling it to ensure it runs, and that fails with the same error message. – Dr R Dizzle Dec 16 '14 at 11:10
  • @vba4all Also, unless I'm doing it wrong (and I dont think I am), the Workbook_Open() sub is not running on workbook open, despite the fact that Macro settigs has Enable All Macros ticked. – Dr R Dizzle Dec 16 '14 at 11:14
  • 1
    dude, `Workbook_Open` is a `Private` event procedure. It runs as soon as the workbooks opens. You're getting an error because it is `Private` and you can't call private macros through `Application.Run`. –  Dec 16 '14 at 11:18
  • @vba4all The macro looks like this in the VBA editor of the spreadsheet. `Public Sub Workbook_Open() ...some code.... End Sub`. I assumed that would make it accessible to be called from .NET. – Dr R Dizzle Dec 16 '14 at 11:42

2 Answers2

3

I'm afraid that's not the answer you'd like to hear, but the correct answer is... you don't.

Microsoft explicitly states that Office automation is not supported from unattended code, which includes web applications:

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

If you need to interact with Excel files, the correct solution is to use a .NET library that supports the Excel file format. Unfortunately, that means that you won't be able to use your existing legacy Excel VBA code.

Community
  • 1
  • 1
Heinzi
  • 167,459
  • 57
  • 363
  • 519
0

Define new public procedure (add new module):

Public Sub MyMacro()
    'do something
End Sub

then call it this way:

Dim sMacroName As String = "TempFile.xlsm!MyMacro"
xlApp.Run(sMacroName)

More at:

http://forums.asp.net/t/1366665.aspx?How+to+run+a+Macro+of+an+Excel+file+opened+on+web+server

http://www.nullskull.com/articles/create_macro_at_runtime_in_dotnet.asp

http://support.microsoft.com/kb/306682

http://www.siddharthrout.com/2012/03/20/calling-excel-macros-programmatically-in-vb-net/

I hope it helped.

Maciej Los
  • 8,468
  • 1
  • 20
  • 35