VBA - Checking a worbook's status and location to run external macros

Question

Here's the situation (I'll try to make it as cleal as possible) ; for context first : I'm making an Excel file that my team will use as a sort of dashboard, with links to various files, apps, with popups etc. There's also a catalog of display errors we use in our job, but for file's size sake, I've opted to use a different workbook and call macros there once it's open.

So we have :

FileA with Tab1 and Tab2

FileB with macros to run via Buttons that are in FileA, Tab2

--

When I'm in FileA, Tab1 and that I click on a button to go to Tab2, a macro runs that automatically opens FileB.

When I'm in Tab2 of FileA, I click on Button1 to activate the corresponding macro in FileB

I've put a code that checks if FileA is located where it should be, and if FileA isn't, a message asks if we want to locate it and open it.

When in Tab2, whenever we click on a button to run the macro, there's a check to see if FileB is open before going forth.

--

Theres is also a way to get into Tab2 without opening FileB (for updating). In Tab2, there is a link to open FileB, and one to close it.

What I would now, is that when I click on a button that would launch a macro in FileB, there's a check if FileB is open, and if not to open it, but to also check if it's stored at the right place, and if not, then to ask if we want to look for it.

--

I've tried to juggle around, but I just can't wrap my head around the problem, there's always a conflict, or a redudancy I can't loop around. My background in coding is inexistent, and I've tinkered my code by looking online, and so far, I've always found bits of code that I could easily adapt to my needs, but not in this case.

--

Following are the codes I've been using... maybe it can help shape out what I intend to do. Any help would be greatly appreciated ! Also, like I said, I've tinkered here and there, so maybe have the adding layers of code become redundant ? I'm not sure... I've taken a few day's pause to try and come back to it later, to no avail.

Also, pardon my french comments... I do work in France after all.

AND, yes, I know, the indentation isn't that great, but I plan to make a full swoop through the code once I'm done with the prototype.

///////////////////////////////////

This is the properly working code that I have between Tab1 and Tab2 :

'Permet d'assigner une navigation de Sheet en Sheet via des boutons (en l'occurence, les pastilles vertes) :
'ici, vers l'Herbier, qui ouvrira automatiquement un autre fichier
Sub Bascule_herbier_Click()
  
Sheets("Herbier").Visible = True
Sheets("Kiosk").Visible = False
Sheets("Support").Visible = False
Sheets("Divers").Visible = False
Sheets("CR200").Visible = False

MsgBox "Un fichier va s'ouvrir en arrière plan ; il se peut qu'il faille y accepter les macros : pour ce faire, cliquez sur le bouton jaune dans le bandeau supérieur de ce fichier, ou avant, sur la fenêtre qui pourrait s'ouvrir au milieu de l'écran.", vbOKOnly + vbInformation, "Action utilisateur"

'Par Excel-Malin.com ( https://excel-malin.com ) - Lance l'ouverture automatique du fichier cible pour les macros liées à l'Herbier

Dim Verification As Boolean
    Dim MonClasseur As String
   
    MonClasseur = "W:\Ateliers\PHOTO\Masks\Kiosk support\Kiosk - Fichiers utilitaires\Maskiosk_Companion_Herbier.xlsm"
   
    'D'abord tester si le fichier existe
    If Len(Dir(MonClasseur)) = 0 Then 's'il n'existe pas, montrer un avertissement et quitter la macro
        MsgBox "ERREUR: Le Classeur: [" & MonClasseur & "] n'est pas là où il devrait être"
       
        'Si le fichier n'est pas présent, on peut le le chercher et l'ouvrir ; le filtre est ciblé pour les fichiers Excel
        Dim Myfile_Name As Variant
Myfile_Name = Application.GetOpenFilename(FileFilter:="Excel Files(*.xl*),*.xl*)")

If Myfile_Name <> False Then
  Workbooks.Open FileName:=Myfile_Name
End If
    Exit Sub
    End If
 
  'Si le test est valide, le fichier va être ouvert ; dans cet autre fichier, une macro à l'ouverture va réduire la fenêtre pour rendre le focus sur le Kiosk
   On Error GoTo OuvertureFichierErreur
   Dim MonApplication As Object
   Dim MonFichier As String
   Set MonApplication = CreateObject("Shell.Application")
 
   MonFichier = "W:\Ateliers\PHOTO\Masks\Kiosk support\Kiosk - Fichiers utilitaires\Maskiosk_Companion_Herbier.xlsm"
   MonApplication.Open (MonFichier)
    
   Set MonApplication = Nothing
Exit Sub

OuvertureFichierErreur:
   Set MonApplication = Nothing
    MsgBox "Erreur lors de l'ouverture du fichier..."
  End Sub

///////////////////////////////////

What I've been trying to work with :

Sub Herbier_Appel_09()
Dim Ret

Dim wb As Workbook
For Each wb In Workbooks

    Ret = IsWorkBookOpen("\\crodisk.cro.st.com\w\Ateliers\PHOTO\Masks\Kiosk support\Kiosk - Fichiers utilitaires\Maskiosk_Companion_Herbier.xlsm")

    If Ret = True Then
       
    Else
        answer = MsgBox("Le Herbier Companion n'est pas ouvert ; voulez-vous l'ouvrir ?", vbYesNo + vbQuestion)
       
                On Error Resume Next
  If answer = vbYes Then

    Workbooks.Open "\\crodisk.cro.st.com\w\Ateliers\PHOTO\Masks\Kiosk support\Kiosk - Fichiers utilitaires\Maskiosk_Companion_Herbier.xlsm"

Else: End

End If
End If

Next

Application.Run "Maskiosk_Companion_Herbier.xlsm!RPR_01"

End Sub

'Variable necéssaire au check d'ouverture du Companion_Herbier ; chaque appel à cette variable est
'dans les divers Sub Herbier_Appel_##.

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

/////////////////////////////////////////////////////////////

And this as well :

Sub Sample020()
    Dim xRet As Boolean
    xRet = IsWorkBookOpen("Maskiosk_Companion_Herbier.xlsm")
    If xRet Then
    
    Else
         
    answer = MsgBox("Le Herbier Companion n'est pas ouvert ; voulez-vous l'ouvrir ?", vbYesNo + vbQuestion)
       
                On Error Resume Next
  If answer = vbYes Then

'D'abord tester si le fichier existe
    If Workbooks("Maskiosk_Companion_Herbier.xlsm").Open = 0 Then 's'il n'existe pas, montrer un avertissement et quitter la macro
        MsgBox "ERREUR: Le Classeur: [" & MonClasseur & "] n'est pas là où il devrait être"
       
        'Si le fichier n'est pas présent, on peut le le chercher et l'ouvrir ; le filtre est ciblé pour les fichiers Excel
        Dim Myfile_Name As Variant
Myfile_Name = Application.GetOpenFilename(FileFilter:="Excel Files(*.xl*),*.xl*)")

If Myfile_Name <> False Then
  Workbooks.Open FileName:=Myfile_Name
End If

    Workbooks.Open "\\crodisk.cro.st.com\w\Ateliers\PHOTO\Masks\Kiosk support\Kiosk - Fichiers utilitaires\Maskiosk_Companion_Herbier.xlsm"

Else: End

End If
End If
End If

Application.Run "Maskiosk_Companion_Herbier.xlsm!RPR_01"
End Sub

Answer 1

Try this:

Option Explicit

Public Const MACRO_PATH As String = "\\crodisk.cro.st.com\w\Ateliers\PHOTO\Masks\" & _
                                    "Kiosk support\Kiosk - Fichiers utilitaires\"
Public Const MACRO_FILE As String = "Maskiosk_Companion_Herbier.xlsm"

Sub Tester()

     RunMacro "RPR_01"

End Sub

'Utility - run a macro by name
Function RunMacro(macroName As String) As Boolean
    'add quotes in case macro file name ever has spaces
    If MacroWorkbookOK Then Application.Run "'" & MACRO_FILE & "'!" & macroName
End Function

'utility - check for the macro workbook - return True if open or can be opened
Function MacroWorkbookOK() As Boolean
    Dim wb As Workbook, f
    
    'already open?
    On Error Resume Next
    Set wb = Workbooks(MACRO_FILE)
    On Error GoTo 0
    
    'not open - see if it can be opened from the expected path
    If wb Is Nothing Then
        On Error Resume Next 'in case network location is not found
        f = Dir(MACRO_PATH & MACRO_FILE)
        On Error GoTo 0
        If Len(f) > 0 Then
            Set wb = Workbooks.Open(MACRO_PATH & MACRO_FILE, ReadOnly:=True)
            ThisWorkbook.Activate
        End If
    End If
    
    'not open and not at expected path - ask user to browse
    If wb Is Nothing Then
        f = Application.GetOpenFilename(FileFilter:="Excel Files(*.xl*),*.xl*)", _
                                        Title:="Please locate the macro file '" & MACRO_FILE & "'")
        If f <> False Then
            If UCase(Dir(f)) <> UCase(MACRO_FILE) Then
                'warn user to select the correct file
                MsgBox "Selected file must be named '" & MACRO_FILE & "'"
            Else
                Set wb = Workbooks.Open(f, ReadOnly:=True)
                ThisWorkbook.Activate
            End If
        End If
    End If
    MacroWorkbookOK = Not wb Is Nothing
End Function