Workbook Activate Or Open

[John Davis]

Hello All:

I have a workbook which I use alot. Sometimes it is already Open. However sometimes it is Closed and filed away. I have a macro which is designed too run when the workbook is already open. Is there code which I could use to say if the workbook is Open then activate and run the macro, else Open the workbook and run the macro. I think this can be done, I believe it would require an if statement. I just don't know how to construct this code. Any help would be appreciated.

 

[xld]

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

Sub test()
    If Not IsFileOpen("C:\MyTest\volker2.xls") Then
        Workbooks.Open "C:\MyTest\volker2.xls"
    End If
End Sub

 

[John Davis]

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

Sub test()
    If Not IsFileOpen("C:\MyTest\volker2.xls") Then
        Workbooks.Open "C:\MyTest\volker2.xls"
    End If
End Sub

Thanks alot XLD. Works perfectly. BTW where is Wessex located on the map? Is it a country? Which continent?

 

[xld]

Only just seen your question, hence the long delay.

Wessex is an ancient Saxon kingdom of England. It was in the south west of the country which is where I live, and is still a used name if not a recognised region. The region is where my roots are, and where I still live, hence my affinity with the area.

Wessex was the greatest ancient kingdom, and fought hard against the invading Danes, especially its greatest king, Alfred the Great (whom you may know of from the myth of King Alfred who burnt the cakes).

There is a good explanation in wikipedia http://en.wikipedia.org/wiki/Wessex

 

[John Davis]

Only just seen your question, hence the long delay.

Wessex is an ancient Saxon kingdom of England. It was in the south west of the country which is where I live, and is still a used name if not a recognised region. The region is where my roots are, and where I still live, hence my affinity with the area.

Wessex was the greatest ancient kingdom, and fought hard against the invading Danes, especially its greatest king, Alfred the Great (whom you may know of from the myth of King Alfred who burnt the cakes).

There is a good explanation in wikipedia http://en.wikipedia.org/wiki/Wessex

Thanks for the info. Having majored in History, it's refreshing to learn a little about Geography and History as well as Excel and macros.