VBA macro codes do not work to open "Excel Macro-Enabled Workbook (*.xlsm) file

Question

I am trying to open one "Excel Macro-Enabled Workbook (.xlsm) file from another "Excel Macro-Enabled Workbook (.xlsm). I am using the following code to perform the task.

Private Sub Workbook_Open()
    If Not isBookOpen("REFERENCE_FILE.xlsm") Then
        tplateWorkBook = ActiveWorkbook.Name
        windowIndex = Application.ActiveWindow.Index
        refFile = ActiveWorkbook.Path & "\REFERENCE_FILE.XLSM"
        If Not Dir(refFile, vbDirectory) = vbNullString Then
            Workbooks.Open refFile
            Application.ActiveWindow.Visible = False
            Application.Workbooks(tplateWorkBook).Activate
            Application.Windows(windowIndex).Visible = True
        Else
            MsgBox "Reference file is missing, LOV will not work." & Chr(13) & "Place a copy of updated Reference File on the same folder as your templates." & Chr(13), vbCritical + vbOKOnly, "Reference File Missing"
        End If
    End If
End Sub

Function isBookOpen(wbName As String) As Boolean
    Dim wb As Workbook
    On Error Resume Next
    Set wb = Workbooks(wbName)
    isBookOpen = Not (Err.Number > 0)
    Exit Function
End Function

This code is work fine for windows 10 Pro, version 21H2 and OS build 19044.1526. However above this OS build (like 19044.2604) this does not work.

Was the file sent thru email by chance? If so, you may have to unblock it. — braX, Mar 16 '23 at 06:08
I am afraid it is not a matter of OS version. As suggested before, it looks being a matter related to the workbook to be open. As a security issue, Windows blocks files downloaded from internet or saved. You can check that simple double clicking on the workbook icon, choose `Properties` and tick the `Unblock` check box to the bottom right side. Then, try opening it. — FaneDuru, Mar 16 '23 at 07:40
If the issue is related to files being received by email (or internet downloads) you might want to read: https://stackoverflow.com/q/25889742/7446760 — CLR, Mar 16 '23 at 08:39
@FaneDuru would you please help me to find the Properties of workbook icon. — Reja, Mar 16 '23 at 10:25
Go on the workbook path and you will see its name, having in front the specific Excel icon... Right click on this one. But I am not sure that this is the single problem. My supplied code does this automatically... Please, try the updated answer, which temporarily decrease the application level of security and raise it again after opening the workbook. I also asked if you are able to manually open the file in discussion... — FaneDuru, Mar 16 '23 at 10:36
_this does not work_ ? In what way, do you get an error message ? If so what is it _exactly_ ? — CDP1802, Mar 16 '23 at 11:00

score 1 · Answer 1 · edited Mar 21 '23 at 11:50

1

You can try:

'Function Check File Is Open
Function IsFileOpen(cur_filename As String) As Boolean
  Dim file_nr As Integer  
  On Error GoTo Opened     
  If (Len(Dir(cur_filename)) > 0) Then
    file_nr = FreeFile
    Open cur_filename For Input Lock Read As #file_nr
    Close #file_nr
  End If
Exit Function

Opened:
  IsFileOpen = True
End Function
'Open Excel File By Full Path
Sub openExcelFile(ByVal sFullPath As String)
    Dim wb As Workbook
    Set wb = Workbooks.Open(sFullPath)
End Sub

And Using it:

Private Sub Workbook_Open()
    Dim sFullPath="C:\abc.xlsm"
    'If file not open
    If IsFileOpen(sFullPath) = False Then
        openExcelFile sFullPath
    End If
End Sub

edited Mar 21 '23 at 11:50

Adrian Mole

49,934
160
51
83

answered Mar 16 '23 at 06:24

Hoang Long UOB

21
5

This code working on OS Build 19044.2604 – Hoang Long UOB Mar 16 '23 at 06:28
Your `IsFileOpen` code checks if the file is *locked*, not if the file is open. Potentially more useful, but you should note the difference. Also, the file opening mechanism is fundamentally the same, so is unlikely to solve the issue the OP is seeing. – CLR Mar 16 '23 at 08:31
I'm not sure @Reja can solve the issue by my code, but i had tested and it working well. – Hoang Long UOB Mar 16 '23 at 09:30
Thanks for your prompt workaround. However, I have tried but still I can not open the file. Microsoft excel quit when I select "enable this content" from the option of security warning. – Reja Mar 16 '23 at 10:01
1

Have you tried File -> Options -> Trust Center->Trust Center Settings-> External Content -> Enable All Data Connections-> OK? – Hoang Long UOB Mar 16 '23 at 10:17

FaneDuru · Answer 2 · 2023-03-16T10:32:11.400

Please, copy the next sub near the existing code (or in a standard module) and adapt your code to call it:

Sub UnblockFile(ByVal sFile As String)
    Dim oFSO  As Object
    Const ForWriting As Long = 2
   
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    oFSO.OpenTextFile(sFile & ":Zone.Identifier:$DATA", ForWriting, True).Write ""
End Sub

And call it in your code in the next way:

Private Sub Workbook_Open()
    Dim tplateWorkBook As String, windowIndex As Long, refFile As String, securityLevel As Long
    
    If Not isBookOpen("REFERENCE_FILE.xlsm") Then
        tplateWorkBook = ActiveWorkbook.name
        refFile = ActiveWorkbook.Path & "\REFERENCE_FILE.XLSM"
        windowIndex = Application.ActiveWindow.Index
        If Not Dir(refFile, vbDirectory) = vbNullString Then
            UnblockFile refFile
            
            securityLevel = Application.AutomationSecurity 'memorize the existing secuity level
            Application.AutomationSecurity = msoAutomationSecurityLow 'make it low
              Workbooks.Open refFile
            Application.AutomationSecurity = securityLevel
            
            Application.ActiveWindow.Visible = False
            Application.Workbooks(tplateWorkBook).Activate
            Application.Windows(windowIndex).Visible = True
        Else
            MsgBox "Reference file is missing, LOV will not work." & Chr(13) & "Place a copy of updated Reference File on the same folder as your templates." & Chr(13), vbCritical + vbOKOnly, "Reference File Missing"
        End If
    End If
End Sub

Thanks for your prompt workaround. However, I have tried but still I can not open the file. Microsoft excel quit when I select "enable this content" from the option of security warning. — Reja, Mar 16 '23 at 10:02
@Reja What "security warning"? You didn't say anything about a warning... Anyhow, was this warning raised before using the sub I supplied, or only now using the updated code? Try place the folder where the workbook exists in Trusted Locations. If it (manually) works, I can supply a piece of code doing that, but it is a little complicate. You can also decrease the security level, follow by restoring it after opening, but I do not use this way because of the fact that your workbook may be corrupted and the code be interrupted before reaching the restauration line. I will adapt the code to do it. — FaneDuru, Mar 16 '23 at 10:29
@Reja Please, try the updated code. One more clarification question: Are you able to **manually** open the workbook in discussion? — FaneDuru, Mar 16 '23 at 10:33

VBA macro codes do not work to open "Excel Macro-Enabled Workbook (*.xlsm) file

2 Answers2