if workbooks("a") exist and is close, then open it.

[uktous]

Hi,

Could you please write the macro that can perform the following function?

If workbooks("a") exist and if workbooks("a") is close, then open workbooks("a").

Thanks

[sassora]

Try this:


Sub OpenFileIfExistsAndClosed()

Dim FileName As String

FileName = "C:\a.xlsx"

If blnTestDir(FileName) = True Then
If Not IsFileOpen(FileName) Then
Workbooks.Open FileName
Else
MsgBox "File already open"
End If
Else
MsgBox "File does not exist"
End If
End Sub



Function blnTestDir(FileName As String)

If Dir(FileName, vbDirectory) = "" Then
blnTestDir = False
Else
blnTestDir = True
End If

End Function

'See xld's entry: Check If a File Is Already Open
Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

[Kenneth Hobs]

Sub Test_Isworkbookopen()
Dim fn As String
fn = "x:\ken.xlsm"
If Dir(fn) = "" Then Exit Sub
If IsWorkbookOpen(fn) Then Workbooks.Open (fn)
End Sub

Function IsWorkbookOpen(stName As String) As Boolean
Dim Wkb As Workbook
On Error Resume Next ' In Case it isn't Open
Set Wkb = Workbooks(stName)
If Not Wkb Is Nothing Then IsWorkbookOpen = True
'Boolean Function assumed To be False unless Set To True
End Function

[snb]

I think a oneliner suffices:

sub snb()
if dir("G:\OF\ken.xlsm")<>"" then getobject("G:\OF\ken.xlsm")
end sub

[sassora]

Nice one liner.

When I ran it, I noticed that the workbook opened in the VBA editor but I couldn't see the spreadsheet. Would this be an improvement?

Sub snb2()
If Dir("G:\a.xlsx") <> "" Then Workbooks.Open "G:\a.xlsx"
End Sub

[snb]

@Sassora

sometimes you want to open a workbook only as a hidden one
Sub snb()
If Dir("G:\OF\adressen.xls") <> "" Then GetObject("G:\OF\adressen.xls")
End Sub


sometimes you want it to be visible:

Sub snb()
If Dir("G:\OF\adressen.xls") <> "" Then GetObject("G:\OF\adressen.xls").Application.Visible = True
End Sub


or (your suggestion)

Sub sassora()
If Dir("G:\OF\adressen.xls") <> "" Then Workbooks.Open ("G:\OF\adressen.xls")
End Sub

[Kenneth Hobs]

Opening the workbook with it already open will cause an error if you do not code for it. Sassora's code handles the case if the workbook is open you or another. Mine works if open in the user's current session.

IF you want a one liner:
On Error Resume Next: Workbooks.Open ("x:\ken.xlsm"): On Error GoTo 0

[snb]

Opening the workbook with it already open will cause an error if you do not code for it.
Which is not the case on my system. (excel 2010), nor using 'getobject', nor using 'workbooks.open'.