How to avoid the error activate of object _workbook failed

Question

1.00/5 (1 vote)

See more:

Hi all,

I have been doing some logic on the server and will download a excel report with all the data and the excel file will be having some macros in it for displaying some charts.

I tried to download the report from desktop application by calling sevrer and the report download and works fine.
Now i am trying to do the same logic from web application similaraly by calliung server.the excel download and it has data but on openeing the excel if macros are enabled it gives me the error as:

Run-time error '1004;

method 'Activate' of object '_Workbook' failed.

please suggest how to avoid this eror..Tried the following code

What I have tried:

 ' Public Sub Workbook_Activate()
'ThisWorkbook.Activate
'End Sub
  'ThisWorkbook.Activate
   'For Each lobjWorkSheet In ThisWorkbook.Worksheets
       ' If lobjWorkSheet.Name = "Summary" Then
        
            
           ' Worksheets("Summary").Activate
           
        '
           ' If ThisWorkbook.Worksheets.Count = 2 Then
               'If ThisWorkbook.Worksheets.Item(1).Name = "ReportTemplate" Then
             '       Exit Sub
                'End If
           ' End If
            
            'If ActiveSheet.ChartObjects.Count = 0 Then
               ' WriteSummarySheet
            'End If
            
            'Exit For
       ' End If
 '   Next
'End Sub

Private Sub Workbook_Open()
 MsgBox ("Worksheet has been oprned")
    On Error Resume Next
    ThisWorkbook.Activate
    On Error GoTo 0

    MsgBox ("after actiavte")

    For Each lobjWorkSheet In ThisWorkbook.Worksheets
        If lobjWorkSheet.Name = "Summary" Then
        
            
            Worksheets("Summary").Activate
           
        
            If ThisWorkbook.Worksheets.Count = 2 Then
                If ThisWorkbook.Worksheets.Item(1).Name = "ReportTemplate" Then
                    Exit Sub
                End If
            End If
          
            If ActiveSheet.ChartObjects.Count = 0 Then
                WriteSummarySheet
            End If
            
            Exit For
        End If
   Next
        
End Sub

Posted 12-Dec-18 21:58pm

Palesh1990

Updated 14-Jan-19 23:32pm

Add a Solution

Comments

Palesh1990 19-Feb-19 7:39am

i have fixed the issue by removing the Open event and handling the same code in the activate event

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2019-01-14T23:32:00

The best way to avoid such of errors is to write code in context!

Never use Select or Activate method, unless you're sure what you doing! Usage of these methods might be the reason of several troubles, such of application hangs, long time of macro execution, etc, and even application crash, because Select/Activate method call fires up several events, for example: screen refreshing, cell calculating and many others.
Based on MSDN documentation (Selecting and Activating Cells | Microsoft Docs[^]):

Quote:
The macro recorder will often create a macro that uses the Select method and the Selection property. The following Sub procedure was created using the macro recorder, and it shows how Select and Selection work together.

VB
Sub Macro1() 
    Sheets("Sheet1").Select 
    Range("A1").Select 
    ActiveCell.FormulaR1C1 = "Name" 
    Range("B1").Select 
    ActiveCell.FormulaR1C1 = "Address" 
    Range("A1:B1").Select 
    Selection.Font.Bold = True 
End Sub
The following example performs the same task without activating or selecting the worksheet or cells.

VB
Sub Labels() 
    With Worksheets("Sheet1") 
        .Range("A1") = "Name" 
        .Range("B1") = "Address" 
        .Range("A1:B1").Font.Bold = True 
    End With 
End Sub

But it does not avoid you from errors! Why? Imagine, you have 2 workbooks opened already. In each of them, there's a "Sheet1". Where the changes will be made? It depends on what workbook is currently active! So, how to improve above code to be sure that macro will be ecxecuted in correct (desired) workbook? Simple, use variable!

VB

Sub Labels() 
    Dim wbk As Workbook, wsh As Worksheet

    Set wbk = ThisWorkbook 'or Application.Workbooks("ShortName.xlsx") - if you want to refer to the workbook differ than workbook in which macro is executed
    Set wsh = wbk.Worksheets("Sheet1") 'or wbk.Worksheets(1)
    With wsh 
        .Range("A1") = "Name" 
        .Range("B1") = "Address" 
        .Range("A1:B1").Font.Bold = True 
    End With
    Set wsh = Nothing
    Set wbk = Nothing 
End Sub

For further information, please see:
Language reference for Visual Basic for Applications (VBA) | Microsoft Docs[^]
Excel VBA Performance Coding Best Practices - Microsoft 365 Blog[^]