2

I have written a function "TestFunction" in an excel file, the function has line "Application.DisplayAlerts = False" in it, problem is if i manually run this function it doesn't thorw any error, error "Method 'Display Alerts' of object '_Application Failed' error" throws when I call the function from middle tier(C#) to execute it.

I dont know what to check here.

Please somebody reply.

Thanks in advance.

Community
  • 1
  • 1
user3842389
  • 45
  • 1
  • 8
  • Most likely the solution is to bind excel application first to an object. As if called from C#, the call `Application.DisplayAlerts` doesn't refer to anything. I'm not quite sure how to do this in C# but in VBA you do this by doing something like `Dim XL as Object` and after `Set XL = createObject(Class:="Excel.Application")` and then use `XL.DisplayAlerts = False` .. – Han Soalone Jul 30 '14 at 11:39

1 Answers1

0

My guess is that this is somehow related to MS Internet Explorer (full article here). In your question you don't specify anything about how or from where you're calling your macro, so I can't be sure. However, I've seen instances like this where the code is referring to the MSIE Application object and not the Excel one. Even if it's not MSIE, it could still be another object that has its own Application defined so it conflicts with Excel's Application object, so the following should still apply.

If I'm right, then you need to add the following line of code to your macro before the line of code that changes the DisplayAlerts property:

On Error Resume Next

You can also add the following line of code after the line of code that changes the DisplayAlerts property:

On Error GoTo 0

So basically, something like this:

On Error Resume Next 'This line is required.
Application.DisplayAlerts = False
On Error GoTo 0      'This line is optional to revert the error handling to default behaviour.

By adding these lines of code to your macro, you suppress the error message and the macro continues to run. Note that you cannot change the DisplayAlerts property in this case.

djikay
  • 10,450
  • 8
  • 41
  • 52
  • Thanks for the reply. I dont understand your answer, if "Note that you cannot change the DisplayAlerts property in this case." is the case then why I`ve to add "Application.DisplayAlerts = False". – user3842389 Jul 30 '14 at 11:25
  • @user3842389: The reason you cannot change the `DisplayAlerts` property is because you don't have access to Excel's `Application` object -- the name has been "hijacked" by MSIE or whatever. You can either have the `DisplayAlerts` line with disabled error reporting or you can remove it altogether. If you want to execute your script from Excel sometimes (and not always through C#), then it's worth leaving the line in, up to you. My suggestion should fix the issue you're having though. Am I right that MSIE is involved here? – djikay Jul 30 '14 at 11:28
  • Yeah MSIE is involved, but even if i avoid using MSIE,as i know other ways to check, then also it threw same error. – user3842389 Jul 30 '14 at 11:36
  • @user3842389: It's hard for me to know exactly what your code is doing in all cases where you call it, I even had to make a (correct, as it turned out) guess about MSIE being involved. C# has its own `Application` object in `System.Windows` (and also `System.Windows.Forms`), so I'm assuming that's the object your code is looking at and not the `Excel.Application` object. – djikay Jul 30 '14 at 11:47
  • @user3842389: This article might help: [How to: Access Office Interop Objects by Using Visual C# 2010 Features](http://msdn.microsoft.com/en-gb/library/dd264733(v=vs.110).aspx). Also, this SO question: [Get instance of Excel application with C# by Handle](http://stackoverflow.com/questions/1118735/get-instance-of-excel-application-with-c-sharp-by-handle) might enable you to access the `Excel.Application` object from your C# code. – djikay Jul 30 '14 at 11:51