PDA

View Full Version : [SOLVED] activate opened workbook



mm07
07-21-2017, 05:48 AM
hello, I would like to activate opened workbook. My idea is folloving, but it doesn´t work. Can anyone help me ?

Dim Fname As Double
Dim Fname2 As Double


Fname = "C:\file"
Fname2 = "C:\file 2"


If IsFileOpen("Fname") = True Then Workbooks(Fname).Activate


ElseIf IsFileOpen("Fname2") = True Then Workbooks(Fname2).Activate


Else: MsgBox ("Open certain files")


End If

mdmackillop
07-21-2017, 06:39 AM
Don't save files on the Root drive (C :. Windows doesn't like it.
Nearest to your code:

Sub Test()
Dim Fname As String
Dim Fname2 As String
Dim Fld As String


Fld = "C:\VBAX\"
Fname = "file.xlsx"
Fname2 = "file 2.xlsx"
If IsFileOpen(Fld & Fname) = True Then
Workbooks(Fname).Activate
ElseIf IsFileOpen(Fld & Fname2) = True Then
Workbooks(Fname2).Activate
Else: MsgBox "Open certain files"
End If
End Sub


Function IsFileOpen(FileName As String)
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=468
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

mm07
07-24-2017, 05:09 AM
Thank you so much. It works very well.