Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    File Open (Excel 2000)

    I need to determine whether a file is open before I open it. I found the following code at j-walk put is clueless on how to use it.

    Here is the code:

    'The WorkbookIsOpen Function
    'Private Function WorkbookIsOpen(wbname) As Boolean
    '' Returns TRUE if the workbook is open
    ' Dim x As Workbook
    ' On Error Resume Next
    ' Set x = Workbooks(wbname)
    ' If Err = 0 Then WorkbookIsOpen = True _
    ' Else WorkbookIsOpen = False
    'End Function

    I need the code to check to see whether test is open.

    Workbooks.Open FileName:="Test.xls"

    Thanks

    Mario

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File Open (Excel 2000)

    I would make the WorkBookIsOpen function public, so that you can call it from any module, but that is not essential.

    Here is an example of the slightly modified function and of the way you can call it:

    Public Function WorkbookIsOpen(wbkName As String) As Boolean
    ' Returns TRUE if the workbook is open
    Dim wbk As Workbook
    On Error Resume Next
    Set wbk = Workbooks(wbkName)
    WorkbookIsOpen = (Err = 0)
    End Function

    Private Sub TestOpen()
    Dim strName As String
    strName = "Test.xls"
    If WorkbookIsOpen(strName) Then
    Workbooks(strName).Activate
    Else
    Workbooks.Open strName
    End If
    End Sub

    Note: this function will cause an error if Test.xls is not open and also isn't found in the current folder.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Open (Excel 2000)

    I've copied the function into my personal file and change the code to:

    strName = calls(i)
    If WorkbookIsOpen(strName) Then Next
    Workbooks.Open FileName:=calls(i)

    Thanks

    Mario

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File Open (Excel 2000)

    Why do you use <font face="Georgia"><font color=blue>Workbooks.Open FileName:=calls(i)</font face=georgia></font color=blue> instead of <font face="Georgia"><font color=blue>Workbooks.Open FileName:=strName</font face=georgia></font color=blue>?

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Open (Excel 2000)

    I have a list of 18 files stored in a variant and are using calls(1) to calls(18) to store it in.

    Mario

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: File Open (Excel 2000)

    Mario,

    I suppose you have code like

    For i = 1 To 18
    strName = calls(i)
    If WorkbookIsOpen(strName) Then Next
    Workbooks.Open FileName:=calls(i)
    Next i

    You use calls(i) twice within the loop. I would either use calls(i) throughout, or refer to strName where possible.

    You can change the code to

    For i = 1 To 18
    strName = calls(i)
    If Not WorkbookIsOpen(strName) Then Workbooks.Open FileName:=strName
    Next i

    Regards,
    Hans

  7. #7
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: File Open (Excel 2000)

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •