Check if File Is Open

[dodonohoe]

Hi all,

I have seen a couple of variations but nothing I could get to work. Best I could do was handle the errors when the file is already open. Basically if a file is already open I would like to A) Either leave it open and do nothing and B) Print it and leave it open.

Sub Inputs(ByVal x As Integer)
'This sub is for the excel piece
'tells the macro that the macro is the active workbbook so that it can go_
'back to the active workbook after opening other workbooks
Dim w As Workbook
    Set w = ActiveWorkbook
'dim all my variables, these are put in by the user on the main macro workbook
Dim Variablez As String
    Dim MyFolder As String
    Dim MyFile As String
    Dim wbk As Workbook
    Dim FileName As String
    Dim Action As String
MyFolder = Trim(Cells(x, 3))
    FileName = Trim(Cells(x, 4))
    Action = Trim(Cells(x, 5))
    Variablez = Trim(Cells(1, 6))
MyFile = Dir(MyFolder & Variablez & "\" & FileName)
'This piece opens the file and if its already open it doesn't throw an error
If MyFile <> "" And FileName <> "" And Action = "Open" Then
   Cells(x, 6) = "This report is open"
   On Error Resume Next: Workbooks.Open MyFolder & Variablez & "\" & MyFile: On Error GoTo 0
End If
'NEED TO CHANGE THE CODE BELOW SO THAT IF THE FILE IS ALREADY OPEN DONT THROW ERROR BUT PRINT BUT DON'T CLOSE OTHERWISE OPEN PRINT AND CLOSE
If MyFile <> "" And FileName <> "" And Action = "Print" Then
   Cells(x, 6) = "This report has been printed"
   On Error Resume Next: Set wbk = Workbooks.Open(MyFolder & Variablez & "\" & MyFile): On Error GoTo 0
   wbk.Sheets.PrintOut
   wbk.Close True
End If
w.Activate
End Sub

[Bob Phillips]

Just call a separate function like so

If WBIsOpen(MyFile) Then
'do nothing
Else
Workbooks.Open MyFolder & Variablez & "\" & MyFile

Function WBIsOpen(wbName As String) As Boolean
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks(wbName)
On Error GoTo 0
WBISOpen = Not wb Is Nothing
End Function

[dodonohoe]

Xlb, that worked perfectly if the file is already open and the action is to "open it". If I want to print that file that is already open I am having trouble referencing that workbook i.e. activating it to then print it. This is what I tried

If WBIsOpen(MyFile) And Action = "Print" Then
wb.Activate
wb.Sheets.PrintOut

Thanks

[Bob Phillips]

If WBIsOpen(MyFile, Action) Then 
   'do nothing
   Else 
   Workbooks.Open MyFolder & Variablez & "\" & MyFile

Function WBIsOpen(wbName As String, Action As String) As Boolean 
    Dim wb As Workbook 
    On Error Resume Next 
    Set wb = Workbooks(wbName) 
    On Error Goto 0 
    WBIsOpen = Not wb Is Nothing 
    If WBIsOpen Then
   If Action = "Print" Then
      wb.Sheets.Printout
   End If
    End If
End Function

[dodonohoe]

Thanks, I am marking this thread as solved