Opening file from outlook - run time error 1004

[tiredofit]

My workbook called SheetsTest.xlsm contains three worksheets and has the following code:

Option Explicit

Private Sub Workbook_Open()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
  
    Set ws = Nothing

    Sheet1.Select
  
End Sub

I can't see anything wrong with it but got an error trying to open it in the following way:

Can someone please try:

1. Create a workbook with the above code and save it.
2. E-mail it to yourself via outlook.
3. Open Excel and open any other SAVED Excel file.
4. Open the VB editor.
5. Open the attached Excel file in step2 DIRECTLY FROM OUTLOOK.

You should see a message about Protected view:

PROTETCED VIEW Be careful .....

click Enable Editing.

Now because the VB editor is open, instead of a second message at the top, you should see a popup box, inviting you to Enable or Disable Macros.

Click Enable Macros.

I followed the above steps and got an error message:

Run-time error '1004' Method 'Select' of object '_Worksheet failed.

and it failed on this line of the code:

Sheet1.Select

Can someone explain why is that?

I googled and that error message is normally when you try to select something on a worksheet that is NOT the active worksheet.

I noticed there were two workbooks that were in the process of trying to be opened:

SheetsTest.xlsm (Read-Only) and SheetsTest.xlsm (Protected View).

Thanks

 

[DanteAmor]

Check the CodeName (Name) of the sheet, you should have one with the codename: Sheet1.

Or if you prefer to use the Name of the sheet:

Sheets("Sheet1").Select

 

[GWteB]

The run-time error can be explained from Excel's safe mode, at least because of the way this safe mode is implemented, i.e. the relationship between the settings of Macro Settings and the settings of Protected View in Excel's Trust Center. Safe mode can kick in (among other things) when you open a workbook which is attached to an e-mail. As mentioned, safe mode's behaviour is dependent on the settings in Trust Center.

Note that Excel recognizes the workbook as an e-mail attachment only because of the disk folder to which outlook is extracting the attachments. As a personal remark: opening an attachment directly from within your e-mail is never a good idea.

Although at the first warning you're enabling editing (and quits from Protected View at the same time), your current macro settings are responsible for the second warning. There are two possible scenarios on opening of a being unsafe considered workbook:
1. the VBE is not open;
2. the VBE is open (your scenario in this thread).

1. In case the VBE isn't open on opening of a macro enabled workbook, the second warning consists of a yellow bar with a security warning and just one button: "enable content". Pressing this button enables macros and any workbook open event handler present will be invoked. At the other hand, opening the VBE at this point has the effect of keeping macros disabled so the just opened workbook stays in design mode.

2. In case the VBE is open on opening of a macro enabled workbook, the second warning consists of a security notice dialog. Enabling macros at this point has NOT the effect of immediate quiting protected view and safe mode. When a Workbook_Open event procedure is running, Excel is keeping that workbook in safe mode. In safe mode all changes (both manual and programmatically) to the just in protected view opened Workbook are ignored / blocked and such attempts through VBA (eg. ThisWorkbook.Sheets(1).Select or ThisWorkbook.Sheets(1).Range("A1").Value = nn) will result in a Run-time error. Safe mode does not end until the Workbook_Open procedure has completely finished. Note that use of Userforms (regardless Modal / Modeless) during safe mode is useless.

 

[tiredofit]

Check the CodeName (Name) of the sheet, you should have one with the codename: Sheet1.

View attachment 51838

Or if you prefer to use the Name of the sheet:

Sheets("Sheet1").Select

I am using the worksheet's codename.

The run-time error can be explained from Excel's safe mode, at least because of the way this safe mode is implemented, i.e. the relationship between the settings of Macro Settings and the settings of Protected View in Excel's Trust Center. Safe mode can kick in (among other things) when you open a workbook which is attached to an e-mail. As mentioned, safe mode's behaviour is dependent on the settings in Trust Center.

Note that Excel recognizes the workbook as an e-mail attachment only because of the disk folder to which outlook is extracting the attachments. As a personal remark: opening an attachment directly from within your e-mail is never a good idea.

Although at the first warning you're enabling editing (and quits from Protected View at the same time), your current macro settings are responsible for the second warning. There are two possible scenarios on opening of a being unsafe considered workbook:
1. the VBE is not open;
2. the VBE is open (your scenario in this thread).

1. In case the VBE isn't open on opening of a macro enabled workbook, the second warning consists of a yellow bar with a security warning and just one button: "enable content". Pressing this button enables macros and any workbook open event handler present will be invoked. At the other hand, opening the VBE at this point has the effect of keeping macros disabled so the just opened workbook stays in design mode.

2. In case the VBE is open on opening of a macro enabled workbook, the second warning consists of a security notice dialog. Enabling macros at this point has NOT the effect of immediate quiting protected view and safe mode. When a Workbook_Open event procedure is running, Excel is keeping that workbook in safe mode. In safe mode all changes (both manual and programmatically) to the just in protected view opened Workbook are ignored / blocked and such attempts through VBA (eg. ThisWorkbook.Sheets(1).Select or ThisWorkbook.Sheets(1).Range("A1").Value = nn) will result in a Run-time error. Safe mode does not end until the Workbook_Open procedure has completely finished. Note that use of Userforms (regardless Modal / Modeless) during safe mode is useless.

Thanks for your detailed explanation.

What I've found is the error is NOT consistent, ie it doesn't happen EVERY time. Is that what you expect?

Thanks

 

[GWteB]

Thanks for your detailed explanation.

You are welcome, but I feel compelled to add some additional notes, as my explanation appears to contain some outdated information. I must have been caught up in time as I discovered that the implementation of the Protected View (PV) has changed in recent years.

Where previously the use of the Me keyword within the ThisWorkbook module led to run-time errors in PV, regardless of whether the VBE was opened or not, this now appears to be possible (this means that previously PV was terminated at a later stage than I mentioned in my previous post). In addition, unlike before, a semi-ordinary workbook window is now opened simultaneously with the PV window; semi-ordinary because the ribbon cannot be used until PV has ended.

Also my comment that the use of userforms during PV is of no use appears to be outdated. Nevertheless, it is recommended to wait with the initialization of your application until PV has ended. This can be accomplished by making the Workbook Activate event handler responsible for this. This event takes place when PV has ended. Of course this should only be done once, but it is not difficult to achieve. All the Workbook Open event handler should do is a check on whether PV is active or not and raise a flag if PV is active.

What I've found is the error is NOT consistent, ie it doesn't happen EVERY time. Is that what you expect?

I wouldn't expect that. I just did some tests with having the VBE open and with the TC settings as per images below.

I did tests:
- without having a workbook open;
- with having a new (not saved; Book1) workbook open;
- with having a (saved) XLSX workbook open;
- with having a (saved) XLSM workbook open;
- and with having multiple workbooks open at the same time (combinations of the above).

With an open VBE window VBA errors consistently on the worksheet select part. I was unable to reproduce any deviant or inconsistent behavior herein.
If you mean that there is no consistency between the situation with open VBE (usually only during the build of an application based on macros) and one without (common during normal use), I can only agree.

 

[tiredofit]

You are welcome, but I feel compelled to add some additional notes, as my explanation appears to contain some outdated information. I must have been caught up in time as I discovered that the implementation of the Protected View (PV) has changed in recent years.

Where previously the use of the Me keyword within the ThisWorkbook module led to run-time errors in PV, regardless of whether the VBE was opened or not, this now appears to be possible (this means that previously PV was terminated at a later stage than I mentioned in my previous post). In addition, unlike before, a semi-ordinary workbook window is now opened simultaneously with the PV window; semi-ordinary because the ribbon cannot be used until PV has ended.

Also my comment that the use of userforms during PV is of no use appears to be outdated. Nevertheless, it is recommended to wait with the initialization of your application until PV has ended. This can be accomplished by making the Workbook Activate event handler responsible for this. This event takes place when PV has ended. Of course this should only be done once, but it is not difficult to achieve. All the Workbook Open event handler should do is a check on whether PV is active or not and raise a flag if PV is active.


I wouldn't expect that. I just did some tests with having the VBE open and with the TC settings as per images below.

View attachment 51895View attachment 51896

I did tests:
- without having a workbook open;
- with having a new (not saved; Book1) workbook open;
- with having a (saved) XLSX workbook open;
- with having a (saved) XLSM workbook open;
- and with having multiple workbooks open at the same time (combinations of the above).

With an open VBE window VBA errors consistently on the worksheet select part. I was unable to reproduce any deviant or inconsistent behavior herein.
If you mean that there is no consistency between the situation with open VBE (usually only during the build of an application based on macros) and one without (common during normal use), I can only agree.

Very grateful you've gone to such lengths to test this.

I noticed my Marco Settings were the same as yours but as for the Protected View, I had two other options that you didn't:

Always open untrusted Text -Based files (.csv, .dif and .sylk) in protected view

Always open untrusted Database files (.dbf) in protected view

(though both were unchecked when I did my testing).

Maybe that caused me to have NOT consistent errors?

I did some further investigation myself and noticed this problem occurred (occasionally) when I opened the workbook via Windows Explorer (as opposed to directly from Outlook).

 

[GWteB]

Ok, thanks for the follow-up.

Maybe that caused me to have NOT consistent errors?

That seems unlikely since your attachment is an Excel workbook and not a Text-based file or a Database file.

As mentioned, we can circumvent potential issues by checking whether PV is active or not.
The code I use runs in the Workbook Open event handler and attempts to set a reference to the Application.ActiveWindow object. If the result is Nothing then PV is activated and a class level (custom) boolean property is set to True, so when PV has ended the required initialization code can be invoked without limitations.

 

[tiredofit]

Ok, thanks for the follow-up.


That seems unlikely since your attachment is an Excel workbook and not a Text-based file or a Database file.

As mentioned, we can circumvent potential issues by checking whether PV is active or not.
The code I use runs in the Workbook Open event handler and attempts to set a reference to the Application.ActiveWindow object. If the result is Nothing then PV is activated and a class level (custom) boolean property is set to True, so when PV has ended the required initialization code can be invoked without limitations.

Thanks again.

The bottom line: don't open Excel files directly from outlook!