Is the Excel file open?

6 posts / 0 new
Last post
ecarlile
Offline
Joined: 03/05/2014 - 11:29
Is the Excel file open?

Wondering if anyone has a short function for checking if an Excel file is open? I've found a few VB examples, which don't seem to be compatible within the IDEA Scripting environment.

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

You are right, most of the VB examples that I also found don't work.  Many of them suggested using the GetObject function which makes sense but I couldn't get any of the examples to work.  I did finally find some code that seems to work but it is probably not for the faint of hear lol.  Try it out and let me know if it works for you.  When you are sending the name of the workbook to the function make sure you don't include the extension as it will probably give you a false.  

Sub Main
	MsgBox WorkbookOpen("MyFile")
End Sub

'*******************************************************************
'* Function: WorkbookOpen
'* Parameter: Name of Excel workbook with no extension
'* Returns: True if Workbook is open, false is excel not open or if workbook is not open
'* Purpose: Function first checks to see if Excel is running, if it is not it will return false
'*	        If Excel is running it will then check if the workbook is open returning true or false.
'*******************************************************************
Function WorkbookOpen(sWorkBookName As String) As Boolean
	Dim wmi As Object
	Dim procs As Object
	Dim oShell As Object
	Dim oWord As Object
	Dim colTasks As Object
	Dim oTask As Object
	Dim i As Integer
	Dim strName As String
	Set wmi = GetObject("winmgmts:")
	
	Set procs = wmi.ExecQuery("select * from Win32_Process Where Name='EXCEL.Exe'")

	If procs.Count > 0 Then
		'http://blogs.technet.com/b/heyscriptingguy/archive/2005/04/27/how-can-i-tell-if-an-excel-workbook-is-open-and-if-it-isn-t-open-it.aspx
		Set oShell = CreateObject("Wscript.Shell")
		Set oWord = CreateObject("Word.Application")
		Set colTasks = oWord.Tasks
		
		i = 0

		For Each oTask In colTasks
			strName = LCase(oTask.Name)
			'MsgBox strName
			If InStr(LCase(strName), LCase(sWorkBookName)) Then
				i = 1
			End If
		Next
		If i > 0 Then
			WorkbookOpen = true 'workbook exists
		Else
			WorkbookOpen = false
		End If
	Else
		WorkbookOpen = false 'excel not running
	End If
	
	Set oShell = Nothing
	Set oWord = Nothing
	Set colTask = Nothing
	Set procs = Nothing
	Set wmi = Nothing
End Function

 

ecarlile
Offline
Joined: 03/05/2014 - 11:29

Hi Brian
Works like a treat when its open on the local computer, but not if another computer on the network has the file open.
Thinking out loud here, wondering if there is a way to have the IDEA importer just open the file as a "Read-Only" and not require full access to the file while importing it? I will keep looking around for options
Edward
 

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Just thinking out loud, but does the script throw an error when you try and import a file that is read-only?  I haven't checked.  If it does maybe what you want to do is copy the file to another location and then import from that location.  There is probably a way to check if a file is locked, that might be the way to go.

Derek
Offline
Joined: 03/29/2016 - 03:10

 
When I used the code for checking if an Excel file is open, sometimes will have an error message as "ActiveX component Cannot create object"

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Derek,

I am sorry to hear that.  Unfortunately I found this code through a google search and I don't know enough about the winmgmts: object and how to use it to help you out.

Brian