Macro bombs out after opening file

[melodramatic]

I have a macro that I've been using for some time, but it quit on me after my Office 365 update last Friday.

In troubleshooting, I added msgboxes everywhere to see what I was getting. The variables are being assigned correctly. I unprotected all sheets, just in case it didn't like working on protected sheets (although the cells with variables being enter are always unlocked).

Basically, as soon as the Test Binder Prep Macro file opens up (and it does open), everything quits.

Help?

Sub TestBinderPrep()
'
' This Macro calls up the Test Binder Prep macro
' Macro created 9/22/2017 by Melody October May
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If Left(Range("G2"), 3) = "XXX" Then
resp = MsgBox("Test Binder Prep cannot run " & vbNewLine & "without forms first being created." & vbNewLine & vbNewLine & _
"Please run the ''Complete Forms'' macro above, then you may return to the Test Binder Prep macro.", vbOKOnly, "Forms for Binder Prep do not exist")
Exit Sub
End If
If MsgBox("You will be opening the Test Binder Prep macro, to create Specimen & Test folders, as well as test documentation.", vbOKCancel, "Confirm opening file") = vbCancel Then
Exit Sub
Else
Dim macroPath As String
Dim PN As Long
Dim ConnType As String

PN = Range("B5")
If Left(Range("B13"), 3) = "XOM" Then
Protocol = "XOM"
Else
Protocol = "ISO / API"
End If
    ConnType = Range("B12")
   
If Left(PN, 3) = 520 Then
macroPath = "N:\In House Projects\9200006 - Administration\ISO TESTING Projects\MACRO\"
Else
macroPath = "P:\LAB\ISO TESTING Projects\MACRO\"
End If

Workbooks.Open Filename:=macroPath & "\Test Binder Prep Macro.xlsm", ReadOnly:=True

'THIS IS WHERE IT'S HANGING UP - NOTHING ELSE HAPPENS AFTER OPENING THE FILE (

Sheets("Input").Select

Range("F6").Value = PN
Range("K1").Value = Protocol
Range("K2").Value = ConnType

MsgBox "Please input the tests you'll be setting up, then press 'Create Binders'"

End If
End Sub

 

[jimrward]

From a quick visual check your string macropath ends with a slash and your test binder string literal begins with a slash thus introducing a double slash into the open statement so it may have created a new xlsm file in an unknown location and therefore nothing to see

 

[melodramatic]

Huh - that's weird. I'm surprised my vba has been that forgiving.

Let me do the change to the extra "\" and see what I get. Be back in a few...

My back in a few is done. I removed the "\" at the beginning of the filename. Still getting the same thing. File opens, but it does not place the variables in the cells.

My current code...

Sub TestBinderPrep()
'
' This Macro calls up the Test Binder Prep macro
' Macro created 9/22/2017 by Melody October May
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
If Left(Range("G2"), 3) = "XXX" Then
resp = MsgBox("Test Binder Prep cannot run " & vbNewLine & "without forms first being created." & vbNewLine & vbNewLine & _
"Please run the ''Complete Forms'' macro above, then you may return to the Test Binder Prep macro.", vbOKOnly, "Forms for Binder Prep do not exist")
Exit Sub
End If
If MsgBox("You will be opening the Test Binder Prep macro, to create Specimen & Test folders, as well as test documentation.", vbOKCancel, "Confirm opening file") = vbCancel Then
Exit Sub
Else
Dim macroPath As String
Dim PN As String
Dim ConnType As String

PN = Range("B5")
If Left(Range("B13"), 3) = "XOM" Then
Protocol = "XOM"
Else
Protocol = "ISO / API"
End If
ConnType = Range("B12")

If Left(PN, 3) = 520 Then
macroPath = "N:\In House Projects\9200006 - Administration\ISO TESTING Projects\MACRO\"
Else
macroPath = "P:\LAB\ISO TESTING Projects\MACRO\"
End If

Workbooks.Open Filename:=macroPath & "Test Binder Prep Macro.xlsm", ReadOnly:=True

'THIS IS WHERE IT'S HANGING UP - NOTHING ELSE HAPPENS AFTER OPENING THE FILE (

Sheets("Input").Select
Range("F6").Value = PN
Range("K1").Value = Protocol
    Range("K2").Value = ConnType
   
End If
End Sub

 

[jimrward]

Ok if you select and copy the contents of one or both of the macropath strings and paste in Windows explorer can you see you xlsm file

 

[jimrward]

This will test if your network shares are setup correct if you do see the file try opening it with double click to see and check if folder permissions are setup correctly

 

[melodramatic]

OK - yes, I can see the file, and I have full rights to it.

Once the file comes up in the macro, I can easily manually put in the three variables in the cells. My only question is why does the macro bomb out at that point and not place those variables for me?
I've put a MsgBox right below the file open. That MsgBox does not come up for me, meaning the fail is immediately after the file open.
I've commented out the DisplayAlerts line, and no alerts come up when running this.

Ack!

 

[jimrward]

I have run out of ideas as I was working on the premise it was working before and must be an environment issue
is the file name correct no additional spaces crept in
try renaming the file in both network location to a single word filename and adjust the code above to a single name and try again

 

[melodramatic]

Let me do that in a bit - having to play with another project right now.

 

[melodramatic]

OK, got it fixed. And I don't know how.

Getting frustrated with it, I decided to re-build the macro a little bit at a time. Same file, just a new macro beneath the first one called "Testing".

First I put in all the variables manually - and removed all IF statements. It worked.

Then I put in the variables. Still worked.

Started adding the IF statements one at a time, wondering which one had screwed it all up. Every single IF statement work.

Finally had the entire macro re-create, the exact same (I even copied both macros side by side on an Excel sheet and to make sure that it was TRUE all lines match). The new Testing macro, with everything the same, worked.

So, needless to say, I renamed the old macro OLDTestBinderPrep, the new macro went from Testing to TestBinderPrep, and it is now working perfectly.

Have you ever had one of those days when you just know you're going to wake up that night thinking, WTF?????

 

[jimrward]

Phew glad to hear you have a result, I remember something weird happening with macro names in the past in one of my projects excel seems to sometimes lose the scope

i didn’t think for one minute that was your issue as I was focussed on possible environmental problems given the recent move to O365 of which I have no experience of

where ever you are in the world have a better evening