PDA

View Full Version : [SOLVED] Check if File Is Open



dodonohoe
01-28-2013, 03:36 AM
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
01-28-2013, 04:14 AM
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
01-28-2013, 06:47 AM
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
01-28-2013, 07:28 AM
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
02-25-2013, 02:23 AM
Thanks, I am marking this thread as solved