Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a way via VBA to determine 1) if Excel is open and 2) if a given file is open?

    I have a process that blows up if the user has certain workbooks open.

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Abraxus' post='764836' date='11-Mar-2009 16:42']Is there a way via VBA to determine 1) if Excel is open and 2) if a given file is open?

    I have a process that blows up if the user has certain workbooks open.

    Thanks![/quote]
    You can use this:
    Code:
    Dim xlApp As Object
    Dim xlWbk As Object
    
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
      MsgBox "Excel is not running", vbInformation
    Else
      MsgBox "Excel is running", vbInformation
    End If
    
    Set xlWbk = GetObject("C:\Docs\MyWorkbook.xls")
    If xlWbk Is Nothing Then
      MsgBox "Workbook is not open", vbInformation
    Else
      MsgBox "Workbook is open", vbInformation
    End If
    On Error GoTo 0

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I modified your code to this
    Code:
    Function ExcelCheck2(strFileIn As String) As Boolean
    	Dim xlApp As Object
    		
    	On Error Resume Next
    	Set xlApp = GetObject(strFileIn)
    	If xlApp Is Nothing Then
    	  ExcelCheck2 = False
    	Else
    	  ExcelCheck2 = True
    	End If
    
    End Function
    but it never returns a FALSE....
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  4. #4
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I even tried this
    Code:
    Function ExcelCheck2(strFileIn As String) As Boolean
    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp Is Nothing Then
      MsgBox "Excel is not running", vbInformation
    Else
      MsgBox "Excel is running", vbInformation
    End If
    
    Set xlApp = GetObject(strFileIn)
    If xlApp Is Nothing Then
      MsgBox "Workbook is not open", vbInformation
      ExcelCheck2 = False
    Else
      MsgBox "Workbook is open", vbInformation
      ExcelCheck2 = True
    End If
    On Error GoTo 0
    End Function
    but never get a correct response on the workbook being open...
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I apologize - GetObject will check whether Excel is running, but it will open the workbook if it wasn't open yet.
    Try this function:
    Code:
    Function ExcelCheck(strFileIn As String) As Boolean
      Dim xlApp As Object
      Dim xlWbk As Object
    
      On Error Resume Next
      Set xlApp = GetObject(, "Excel.Application")
      If Not xlApp Is Nothing Then
    	For Each xlWbk In xlApp.Workbooks
    	  If xlWbk.FullName = strFileIn Then
    		ExcelCheck = True
    		Exit For
    	  End If
    	Next xlWbk
      End If
    End Function
    Warning: it may fail if the user has more than one instance of Excel.exe running (this is not the same as having multiple workbooks open).

Posting Permissions

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