check if workbook is open and run a macro

[redree]

I'm Looking for VBscript that will check to see if a specific Excel workbook is open and then run a macro, and in case if the workbook is close it will open the workbook.
i tried bellow code but it open the worbook as read only if the workbook is previously open.
when running the macro it will add 1 to cell F9, when running the macro via vbscript same workbook will open as read only and run the macro.
apreciate your help

Set xl = CreateObject("Excel.application")
 
xl.Application.Workbooks.open "C:\Users\redree\Desktop\project1.xlsm"
xl.Application.Visible = True
xl.Application.run "'project1.xlsm'!copy"
 
Set xl = Nothing

[MikeTheBike]

Hi

pehaps somthing like this

Option Explicit
 
Function WorkbookIsOpen(ByVal BookName As String) As Boolean
    Dim wkbk As Workbook
    With Application
        For Each wkbk In .Workbooks
            If wkbk.Name = BookName Then
                WorkbookIsOpen = True
                Exit Function
            End If
        Next wkbk
    End With
    WorkbookIsOpen = False
End Function
 
Sub Test()
    MsgBox WorkbookIsOpen(ThisWorkbook.Name)
End Sub

In your code change

With Application

to

With xl

assuming xl is a global or modyle level variable.

You will also need to extract the File Name from the Path string

HTH

[redree]

i'm stuck in 3rd line
my file name is "project" and file path is "C:\Users\redree\Desktop\project1.xlsm"
how can i insert them i your code for testing
thank you for your help as i am very new in scripting.

[MikeTheBike]

Try this

Option Explicit
 
Function WorkbookIsOpen(ByVal BookName As String) As Boolean
    Dim wkbk As Workbook
    With Application
        For Each wkbk In .Workbooks
            If wkbk.Name = BookName Then
                WorkbookIsOpen = True
                Exit Function
            End If
        Next wkbk
    End With
    WorkbookIsOpen = False
End Function
 
Sub Test()
    Dim Path As String
    Dim FileName As String
 
    Path = "C:\Users\redree\Desktop\project1.xlsm"
    FileName = Mid(Path, InStrRev(Path, "\") + 1)
 
    MsgBox WorkbookIsOpen(FileName)
End Sub

??

MTB