CODEWITHSUNDEEP
excelvba
ThatsReallyConfusing
ThatsReallyConfusing

Reputation: 1

Macro Only Finishes If Run Though VBA Application or Macro List

I have a 3 master sheets that I frequently want open (May, June, and July). I have multiple other macros that grab data from these master sheets.

I've created a macro (OpenFiles) that opens them. I want to be able to call OpenFiles at the start of a macro if I know I will be referencing one of these master sheets.

Sub OpenFiles calls another sub(zzGetIt) that checks if the workbook is already open, otherwise it opens it. It works perfectly if I run it from the VBA application, or by choosing it in the macro list in Excel. But if I use a hotkey to call it, or if it is called through another macro, it exits out of all subs after opening a single file (and ends on that workbook instead of activating my original active workbook).

It will open a workbook if one of them is missing, but it will never open more than one (if, say I have only 1/3 workbooks open- 2 should open). And the only scenario where the macro will continue to the msgbox at the end is if all three files are already open.

Please help- I think this must be super obvious since the macro runs fine if I run it from VBA.

I've tried the following:

    Sub zzGetIt(sfullname As String)

    Dim ZGetIt As Workbook
    Dim wb As Workbook

    Dim ReadOnly As Boolean    
    Dim o As Boolean

    Dim sFile As String

    sFile = Dir(sfullname)

        MsgBox ("Trying to fetch")
        For Each wb In Application.Workbooks
        If wb.Name = sFile Then
            o = True
            Exit For
        End If
        Next wb

        If o = False Then
        Set zGetIt = Workbooks.Open(sfullname, ReadOnly:=ReadOnly)
        End If
        'reset o
        o = False
        MsgBox ("Finished fetching " & sFile)
    End Sub


    Sub OpenFiles()

    Dim Current As Worksheet
    Set Current = ActiveSheet

    Dim May As String
    Dim Jun As String
    Dim Jul As String

    May = "A:\Files\My Stuff\05 May 2019 - Master.xlsx"
    Jun = "A:\Files\My Stuff\06 June 2019 - Master.xlsx"
    Jul = "A:\Files\My Stuff\07 July 2019 - Master.xlsx"

    Call zzGetIt(May)
    Call zzGetIt(Jun)
    Call zzGetIt(Jul)

    Current.Activate
    Set Current = Nothing

    Msgbox("I can only get this msgbox if I run from macro list or 
    VBA application OR if all 3 workbooks were already open before I ran the 
    macro")

    End Sub

If May needs to be opened it will stop at May so I do not receive the msgbox after the sub is called for the first time.

I want the macro to open any of the three workbooks that are not already open and I need it to continue until the msgbox at the very end pops up

Upvotes: 0

Views: 533

Answers (2)

ThatsReallyConfusing
ThatsReallyConfusing

Reputation: 1

I've solved the issue... found this article. The issue is using a hotkey with SHIFT. Hotkey used must be lower-case or use a button to call the macro

Ctrl+Shift+a
'won't work

Ctrl+a
'will work

Upvotes: 0

David Zemens
David Zemens

Reputation: 53623

I don't see anything obviously wrong with your code that might cause the observed behavior. But I would still do it differently. Perhaps this will help. I've revised your procedures that check for the file already open/open the file if not already open, but apart from that the main difference is that I'm calling this procedure in a loop from OpenFiles.

Option Explicit

Sub OpenFiles()

Dim Current As Worksheet
Set Current = ActiveSheet
Dim files As New Collection
Dim file

files.Add "A:\Files\My Stuff\05 May 2019 - Master.xlsx"
files.Add "A:\Files\My Stuff\06 June 2019 - Master.xlsx"
files.Add "A:\Files\My Stuff\07 July 2019 - Master.xlsx"

For Each file In files
    Debug.Print "Fetching file " & file
    If isFileOpen(CStr(file)) Then
        Debug.Print file & " is already open :) "
    Else
        Call GetFile(CStr(file), False)
    End If
Next

Current.Activate
Set Current = Nothing

MsgBox ("Finished!")

End Sub
Private Function isFileOpen(fullPath$) As Boolean
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks(Dir(fullPath))
If Err.Number = 0 Then isFileOpen = True
End Function

Private Sub GetFile(fullPath$, readOnly As Boolean)
' No error handling here, this SHOULD raise an error if the file can't 
' be opened for any reason (invalid path, locked/in-use unless readOnly=True, etc. 
Debug.Print "Attempting to open " & fullPath
Workbooks.Open fullPath, readOnly:=readOnly

End Sub

Upvotes: 0

Related Questions