Skip 'Enable Macros' Dialogue box when Opening a File with VBA

[Caleeco]

Hello,

I am trying to import data from a workbook that is closed into my currently open workbook. I found some code online which I chopped up, However, I am having problems with the statement below:

    Application.EnableEvents = False
    Workbooks.Open FileName:=strFilepath & strFilename, ReadOnly:=True
    Application.EnableEvents = True

I still get the prompt to enable or disable macros. I want to be able to diable them and open the file as read-only. Then copy out data from the opened workbook.

Sub UpdateSRelData()


'------Variables-------
Dim strFilepath As String 'Filepath for the file that needs opening
Dim strFilename As String 'Filename
Dim wbKPI As Workbook 'Wb that is currently open
Dim wbSRR As Workbook 'wb to oen
strFilepath = "S:\<some path="" directory="">\"
strFilename = "S-Release Requests Sheet.xlsm"

'--------- Check if S-Release Requests Spreadsheet is already open
Dim Ret
Ret = IsWorkBookOpen(strFilepath & strFilename)

If Ret = True Then
    MsgBox "The Excel File " & strFilename & " is already opened by yourself or another user. Please close it and re-run this macro to obtain the latest data." & vbLf & "This Macro will now run on the read-only version of the available file.", vbCritical, "Error"
    'Exit Sub
End If

If Dir(strFilepath & strFilename) = "" Then
    MsgBox "The File named " & strFilename & " does not exist." & vbLf & "The P Parts List has not been updated.", vbCritical, "Error"
    Exit Sub
Else
    Application.EnableEvents = False
    Workbooks.Open FileName:=strFilepath & strFilename, ReadOnly:=True
    Application.EnableEvents = True
End If

Set wbSRR = Workbooks(strFilename)
Set wbKPI = Workbooks("Offload's Request for Material Summary KPIs.xlsm")
 
End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long
    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

Thanks in advance!
Calecco</some>

 

[RoryA]

Try this:

    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Workbooks.Open Filename:=strFilepath & strFilename, ReadOnly:=True
    Application.AutomationSecurity = msoAutomationSecurityByUI

 

[Caleeco]

Try this:

    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    Workbooks.Open Filename:=strFilepath & strFilename, ReadOnly:=True
    Application.AutomationSecurity = msoAutomationSecurityByUI

Hello Rory,

Thanks for the lightning quick response! Yes, that does just what I want

Out of interest, can I enable macros with this statement?

    Application.AutomationSecurity = msoAutomationSecurityForceEnable
    Workbooks.Open FileName:=strFilepath & strFilename, ReadOnly:=True
    Application.AutomationSecurity = msoAutomationSecurityByUI

 

[Caleeco]

Ah i think I found it, the microsoft support website states the following:

[TABLE="class: sbody-table table"]

<tbody>[TR="class: sbody-tr"]
[TD="class: sbody-td"]msoAutomationSecurityForceDisable[/TD]
[TD="class: sbody-td"]Disables the macros in all documents that are opened by previously enabled macros. This setting provides the highest level of security.[/TD]
[/TR]
[TR="class: sbody-tr"]
[TD="class: sbody-td"]msoAutomationSecurityByUI[/TD]
[TD="class: sbody-td"]Respects the Warn before opening a file that contains macros preference for macros in all documents opened by previously enabled macros.[/TD]
[/TR]
[TR="class: sbody-tr"]
[TD="class: sbody-td"]msoAutomationSecurityLow[/TD]
[TD="class: sbody-td"]Enables the macros in all document that are opened by previously enabled macros.[/TD]
[/TR]
</tbody>
[/TABLE]