|
Test for Open File
Hi all,
I need to create a If statment that tests if a Xls file is open or not. Any ideas? Thanks in advance. Aaron |
Test for Open File
On error resume next
set bk = workbooks("MyBook.xls") On error goto 0 if not bk is nothing then msgbox "MyBook.xls is already open in excel" else msgbox "MyBook.xls is not open" End if if you mean located on a shared drive and being used by someone else: http://support.microsoft.com?kbid=138621 XL: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=291295 XL2002: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=213383 XL2000: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=184982 WD97: VBA Function to Check If File or Document Is Open -- Regards, Tom Ogilvy "Aaron" wrote in message ... Hi all, I need to create a If statment that tests if a Xls file is open or not. Any ideas? Thanks in advance. Aaron |
Test for Open File
Hi Tom,
I cannot get this to work. It may be because I am using the Macro in workbook1 and need it to look at workbook2 and if open then run code. Would this make a difference? Thanks, Aaron "Tom Ogilvy" wrote: On error resume next set bk = workbooks("MyBook.xls") On error goto 0 if not bk is nothing then msgbox "MyBook.xls is already open in excel" else msgbox "MyBook.xls is not open" End if if you mean located on a shared drive and being used by someone else: http://support.microsoft.com?kbid=138621 XL: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=291295 XL2002: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=213383 XL2000: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=184982 WD97: VBA Function to Check If File or Document Is Open -- Regards, Tom Ogilvy "Aaron" wrote in message ... Hi all, I need to create a If statment that tests if a Xls file is open or not. Any ideas? Thanks in advance. Aaron |
Test for Open File
First, Tom offered two suggestions.
Which one did you try? And which one did you really mean to try? You may want to post the code that you tried, too. And one more question... If workbook1.xls is open in one instance of excel and workbook2.xls is open in another instance (not just different windows of the same instance), then you'll want to use Tom's second suggestion. Aaron wrote: Hi Tom, I cannot get this to work. It may be because I am using the Macro in workbook1 and need it to look at workbook2 and if open then run code. Would this make a difference? Thanks, Aaron "Tom Ogilvy" wrote: On error resume next set bk = workbooks("MyBook.xls") On error goto 0 if not bk is nothing then msgbox "MyBook.xls is already open in excel" else msgbox "MyBook.xls is not open" End if if you mean located on a shared drive and being used by someone else: http://support.microsoft.com?kbid=138621 XL: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=291295 XL2002: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=213383 XL2000: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=184982 WD97: VBA Function to Check If File or Document Is Open -- Regards, Tom Ogilvy "Aaron" wrote in message ... Hi all, I need to create a If statment that tests if a Xls file is open or not. Any ideas? Thanks in advance. Aaron -- Dave Peterson |
Test for Open File
Hi Dave,
I used the first one because I only wanted to know if I had the file open in the same instance. The Error I am getting is when the file is not open and is "object required", debug is pointing to "if not bk is nothing then". Thanks, Aaron "Dave Peterson" wrote: First, Tom offered two suggestions. Which one did you try? And which one did you really mean to try? You may want to post the code that you tried, too. And one more question... If workbook1.xls is open in one instance of excel and workbook2.xls is open in another instance (not just different windows of the same instance), then you'll want to use Tom's second suggestion. Aaron wrote: Hi Tom, I cannot get this to work. It may be because I am using the Macro in workbook1 and need it to look at workbook2 and if open then run code. Would this make a difference? Thanks, Aaron "Tom Ogilvy" wrote: On error resume next set bk = workbooks("MyBook.xls") On error goto 0 if not bk is nothing then msgbox "MyBook.xls is already open in excel" else msgbox "MyBook.xls is not open" End if if you mean located on a shared drive and being used by someone else: http://support.microsoft.com?kbid=138621 XL: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=291295 XL2002: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=213383 XL2000: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=184982 WD97: VBA Function to Check If File or Document Is Open -- Regards, Tom Ogilvy "Aaron" wrote in message ... Hi all, I need to create a If statment that tests if a Xls file is open or not. Any ideas? Thanks in advance. Aaron -- Dave Peterson |
Test for Open File
I added one line and it worked ok:
Dim bk As Workbook On Error Resume Next Set bk = Workbooks("MyBook.xls") On Error GoTo 0 If Not bk Is Nothing Then MsgBox "MyBook.xls is already open in excel" Else MsgBox "MyBook.xls is not open" End If Aaron wrote: Hi Dave, I used the first one because I only wanted to know if I had the file open in the same instance. The Error I am getting is when the file is not open and is "object required", debug is pointing to "if not bk is nothing then". Thanks, Aaron "Dave Peterson" wrote: First, Tom offered two suggestions. Which one did you try? And which one did you really mean to try? You may want to post the code that you tried, too. And one more question... If workbook1.xls is open in one instance of excel and workbook2.xls is open in another instance (not just different windows of the same instance), then you'll want to use Tom's second suggestion. Aaron wrote: Hi Tom, I cannot get this to work. It may be because I am using the Macro in workbook1 and need it to look at workbook2 and if open then run code. Would this make a difference? Thanks, Aaron "Tom Ogilvy" wrote: On error resume next set bk = workbooks("MyBook.xls") On error goto 0 if not bk is nothing then msgbox "MyBook.xls is already open in excel" else msgbox "MyBook.xls is not open" End if if you mean located on a shared drive and being used by someone else: http://support.microsoft.com?kbid=138621 XL: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=291295 XL2002: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=213383 XL2000: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=184982 WD97: VBA Function to Check If File or Document Is Open -- Regards, Tom Ogilvy "Aaron" wrote in message ... Hi all, I need to create a If statment that tests if a Xls file is open or not. Any ideas? Thanks in advance. Aaron -- Dave Peterson -- Dave Peterson |
Test for Open File
Prefect! That Dave and Tom!!
"Dave Peterson" wrote: I added one line and it worked ok: Dim bk As Workbook On Error Resume Next Set bk = Workbooks("MyBook.xls") On Error GoTo 0 If Not bk Is Nothing Then MsgBox "MyBook.xls is already open in excel" Else MsgBox "MyBook.xls is not open" End If Aaron wrote: Hi Dave, I used the first one because I only wanted to know if I had the file open in the same instance. The Error I am getting is when the file is not open and is "object required", debug is pointing to "if not bk is nothing then". Thanks, Aaron "Dave Peterson" wrote: First, Tom offered two suggestions. Which one did you try? And which one did you really mean to try? You may want to post the code that you tried, too. And one more question... If workbook1.xls is open in one instance of excel and workbook2.xls is open in another instance (not just different windows of the same instance), then you'll want to use Tom's second suggestion. Aaron wrote: Hi Tom, I cannot get this to work. It may be because I am using the Macro in workbook1 and need it to look at workbook2 and if open then run code. Would this make a difference? Thanks, Aaron "Tom Ogilvy" wrote: On error resume next set bk = workbooks("MyBook.xls") On error goto 0 if not bk is nothing then msgbox "MyBook.xls is already open in excel" else msgbox "MyBook.xls is not open" End if if you mean located on a shared drive and being used by someone else: http://support.microsoft.com?kbid=138621 XL: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=291295 XL2002: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=213383 XL2000: Macro Code to Check Whether a File Is Already Open http://support.microsoft.com?kbid=184982 WD97: VBA Function to Check If File or Document Is Open -- Regards, Tom Ogilvy "Aaron" wrote in message ... Hi all, I need to create a If statment that tests if a Xls file is open or not. Any ideas? Thanks in advance. Aaron -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 02:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com