CODEWITHSUNDEEP
excelvba
csmith222
csmith222

Reputation: 29

Workbooks.open hangs

I have a macro that will open another workbook from a network location, compare some values in a range, copy/paste any that are different, and then close the file. I use variables to open the file, because the appropriate filename is based on the current date. I also set Application.ScreenUpdating = False, and Application.EnableEvents = False

for some reason, the code has begun to hang on the worksheets.open line and I can't even CTRL+Break to get out of it. I have to manually close Excel and sometimes it give me an error message, complaining about there not being "enough memory to complete this action".

I can put a stop in the code and confirmed the variables are supplying the correct string, which equates to:

"\Clarkbg01\public\PRODUCTION MEETING\PROD MEETING 3-21-18.xlsm"

I can paste this into Windows Explorer and it will open right up with no issues. I can manually select the file from Explorer and it will open with no issues. I can paste the following line into the immediate window and it will hang...

workbooks.Open("\\Clarkbg01\public\PRODUCTION MEETING\PROD MEETING 3-21-18.xlsm")

This happens even if I open a blank sheet and execute that line from the immediate window.

from my macro, stepping through the code goes without a hitch. I can verify all the variables are correct, but when it steps across workbooks.open, it hangs.

I have other macros that open workbooks, do much more complicated routines, then close them with zero issues, but I'm really stuck on why this one is giving me so many problems.

Any ideas?

Here is the code: 

'This will open the most recent meeting file and copy over the latest for jobs flagged with offsets

Dim Path As String
Path = ThisWorkbook.Path
'Debug.Print Path

Dim FileDate As String
FileDate = ThisWorkbook.Sheets("MEETING").Range("3:3").Find("PREVIOUS NOTES").Offset(-1, 0).Text
'Debug.Print FileDate

Dim FileName As String
FileName = "PROD MEETING " & FileDate & ".xlsm"
Debug.Print "Looking up Offsets from: " & FileName

Dim TargetFile As String
TargetFile = Path & "\" & FileName
Debug.Print TargetFile


Application.ScreenUpdating = False
Application.EnableEvents = False

'The old way I was opening it...
'Workbooks.Open FileName:=Path & "\" & FileName, UpdateLinks:=False ', ReadOnly:=True

'The most recent way to open
Dim wb As Workbook
Set wb = Workbooks.Open(TargetFile, UpdateLinks:=False, ReadOnly:=True)

    'Do Stuff

wb.Close savechanges:=False

Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox "Offsets should now reflect settings made in meeting on " & FileDate

End Sub

Upvotes: 1

Views: 2129

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

If the workbook you're opening contains code in the Workbook_Open event then this will attempt to execute when the event fires .

To stop this behaviour use the Application.AutomationSecurity Property. 

Public Sub Test()

    Dim OriginalSecuritySetting As MsoAutomationSecurity

    OriginalSecuritySetting = Application.AutomationSecurity

    Application.AutomationSecurity = msoAutomationSecurityForceDisable

        'Open other workbook

    Application.AutomationSecurity = OriginalSecuritySetting

End Sub

Upvotes: 1

Related Questions