none
VB Script to check if file is open - exit if so RRS feed

  • Question

  • Good Morning Folks -

    I have a network share at work that is leveraged by the finance team.  There are times when running various automation scripts that certain files are not updated due to the fact someone has an excel file open.

    Therefore, I'm looking to write a piece of VB script code that first checks to see if the file is open. If not, proceed, if it is open, then exit.

    Can anyone assist? Thank you!

    Friday, September 15, 2017 7:32 AM

All replies

  • I've been playing around with this and it seems to be working okay.  Is this best practice or can someone recommend another method?

    ExcelFileName = "Format.xlsx"
    
    On Error Resume Next
    Set xl = GetObject(, "Excel.Application")  'attach to running Excel instance
    If Err Then
      If Err.Number = 429 Then
        WScript.Echo "Workbook not open (Excel is not running)."
      Else
        WScript.Echo Err.Description & " (0x" & Hex(Err.Number) & ")"
      End If
      WScript.Quit 1
    End If
    On Error Goto 0
    
    Set wb = Nothing
    For Each obj In xl.Workbooks
      If obj.Name = ExcelFileName Then  
      WScript.Echo "Workbook is open"
      WScript.Sleep 10000
        Set wb = obj
        Exit For
      End If
    Next
    If wb Is Nothing Then
      WScript.Echo "Workbook not open."
      WScript.Quit 1
    End If
    
    WScript.Echo "Exiting VB Script"
    WScript.Quit 99


    The issue is, I can't seem to supply a full path to the excel file, as when I do, it doesn't work as expected. Since my VB script doesn't exist int he same folders on the finance drive, this wont work. Can anyone suggest a edit to get the full path to work? Thanks!

    • Edited by cdtakacs1 Friday, September 15, 2017 8:22 AM
    Friday, September 15, 2017 8:04 AM
  • You say VB Script.  Do you mean VBA?  This is a VBA forum.  If you mean VB Script then you probably should use a different forum.  This is a VBA function I use to test if an xlsx or docx file is open.

    Function IsXlsFileOpen(xlsPath As String) As Boolean
    
      Dim fso As New Scripting.FileSystemObject  'Reference MS Scripting Runtime
      Dim fileNum As Integer
      
      On Error GoTo ErrHandler
      
      If fso.FileExists(xlsPath) Then
        fileNum = FreeFile()
        Open xlsPath For Input Lock Read As fileNum
        On Error Resume Next
        Close #fileNum
      End If
      Exit Function
    
    ErrHandler:
       IsXlsFileOpen = True
    End Function
    

    Sunday, September 17, 2017 2:00 PM
  • There is such function here:

    http://www.EXCELGAARD.dk/Lib/FileStatus/

    Sunday, September 17, 2017 6:35 PM