Workbooks.open not working consistently

[BOBbED]

I have a macro which I created about 10 years ago in VBA for Excel for Mac. I and others in my company used this consistently over this time period with no issues. Now I have been having multiple problems since switching to Excel For Mac in Office 365. I use this one particular macro to insert lines saved in another spreadsheet into the spreadsheet I am currently working on. As I said, it worked perfectly for all previous versions on Mac and PC. Now when I run it I get inconsistent results.

The code is written to allow me to use a file open dialogue box to open the spreadsheet file I want to use and insert into my current spreadsheet. I ran the debugger and noticed that the workshops.open command was not always opening the file I had identified. The first time after opening my spreadsheet it runs fine, but then its inconsistent afterwards.

Any Ideas?

Here's the portion of the code that opens the other file. The whole macro is very long, so I am only including this segment here.

- - - - - -


Dim FileToOpen As String


Continue = MsgBox("This will insert a new Task before the Task currently selected. You must now select a Task from the templates saved on your hard drive!.", vbOKCancel)

If Continue = 1 Then




line1:
FileToOpen = Application.GetOpenFilename 'This opens a file open dialogue box and records the file name and location as a string

If Not FileToOpen = "False" Then
GoTo line3:

Else: GoTo lastline:


End If 'End If

line3:
Workbooks.Open (FileToOpen)


lastline:

 

[Norie]

As far as I can see that code will only ever open the workbook the user has selected via the GetOpenFileName dialog.

You say that's not the case, so what is happening?

Are different, random? files being opened?

Is the rest of the code not referring to/running against the workbook that's been opened?

 

[BOBbED]

As far as I can see that code will only ever open the workbook the user has selected via the GetOpenFileName dialog.

You say that's not the case, so what is happening?

Are different, random? files being opened?

Is the rest of the code not referring to/running against the workbook that's been opened?

- - - - - - -

What's happening the second time I run it is that it doesn't open ANY FILE.

The first thing after this code (shown above) is a check to see if there is a Key Word on the newly opened document (which I created so the macro wouldn't continue if someone opened the wrong file.) The "Watch" I set for the variable I used for comparison against this key word comes up as empty. This is how I figured out that it wasn't actually opening a file the second time around. Since its not finding the Key Word (since it didn't open any file) the IF statement sends it to "Lastline:" which is the end of the SUB.

I should also note that I use a Sub routine at the beginning of this macro (not shown on my original post) which speeds up the macro performance. I then use another Sub at the end to restore to the original states. Here's the code I am using for those two Sub routines.

AT MACRO START

- - - - -

Sub OptimizeCode_Begin()


Application.Calculation = xlCalculationAutomatic
CalcState = Application.Calculation
Application.Calculation = xlCalculationManual


EventState = Application.EnableEvents
Application.EnableEvents = False


StatusBarState = Application.DisplayStatusBar
Application.DisplayStatusBar = False


Application.ScreenUpdating = False


PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False


End Sub

- - - -

AT MACRO END

- - - - -

Sub OptimizeCode_End()


Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayStatusBar = StatusBarState
ActiveSheet.DisplayPageBreaks = PageBreakState


End Sub