Look at the file name before it saves file into a folder

Question

My goal is to add a icon to my toolbar in the quick access section.

I wish this code could look at the file that is open, check if the file name contains or rather look likes this "_pr11.xlsx". If so, I want my code to save file as, see code below, and otherwise do or actually run/start an application. If the application is already running, I would like the code to do nothing of course, or say it could do a msgbox.

Obviously I can not archive this and maybe its not even possible? Is any of this scenario even doable?

I would much appreciate all the help and guidance so thanx in advance!

Sub IfOpenFileIs_PR11RepportThen()

    If ActiveWorkbook.name 'ends with' = "*_pr11*.xlsx" Then
        ActiveWorkbook.SaveAs Filename:="C:\Temp\PR11\" & "R11 (P3)" & " fram t.o.m. " & Format(Now - 1, "YYYY-MM-DD"), FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWindow.Close
    Else
        if 'application is alreay running then
            MsgBox "Generera en ny PR11 (P3) rapport"
        Else
            Dim strAppPath As String, varApp As Variant
            strAppPath = "C:\Program Files (x86)\Citrix\ICA Client\SelfServicePlugin\SelfService.exe" -launch -reg "Software\Microsoft\Windows\CurrentVersion\Uninstall\intern-581c115@@Z47F003.W2K16 - Agresso_1" -startmenuShortcut"
            varApp = Shell(strAppPath, 1)
        End If
    End If
End Sub

Do a `right` on `ActiveWorkbook.name` for the `if` statement? — findwindow, Apr 27 '22 at 21:48

score 0 · Answer 1 · answered Apr 28 '22 at 01:07

The first part is easy with InStr() and Right():

    If InStr(1, ActiveWorkbook.Name, "_pr11", vbTextCompare) <> 0 _
       And Right(ActiveWorkbook.Name, 5) = ".xlsx" Then

Case doesn't matter with vbTextCompare.

The check on whether the app is running is a new one for me from this StackOverflow question: Determine if application is running with Excel

Sub exampleIsProcessRunning()
    Debug.Print "Spotify:" & IsProcessRunning("spotify.EXE")
    Debug.Print "NOT:" & IsProcessRunning("NOT RUNNING.EXE")
End Sub

Function IsProcessRunning(process As String)
    Dim objList As Object
    
    Set objList = GetObject("winmgmts:") _
        .ExecQuery("select * from win32_process where name='" & process & "'")
    
    IsProcessRunning = objList.Count > 0
End Function

Look at the file name before it saves file into a folder

1 Answers1