CODEWITHSUNDEEP
vb.netexcelvba
Dr R Dizzle
Dr R Dizzle

Reputation: 272

How do I run an Excel Macro from a .Net web system?

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.

Upvotes: 1

Views: 10796

Answers (2)

Heinzi
Heinzi

Reputation: 172478

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.

Upvotes: 3

Maciej Los
Maciej Los

Reputation: 8591

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.

Upvotes: 0

Related Questions