Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for open file
I need to check if a file is open - the problem I am having is that it's from
a template - I don't know how to find it. This is what I have tried: If Windows("Quote System1").Visible = True Then MsgBox "Visible" _ Else MsgBox "Not Visible" If the window is open, it will give a msgbox saying "Visible", but if it's not open it gives me an error - Subscript out of range. I also tried this: dim wbk as workbook on error resume next set wbk = workbooks("Quote System1.xls") on error goto 0 is wbk is nothing then _ Msgbox "Open the Quote System" This one gives me the message box whether the file is open or not. I also tried "Quote System1" (left out the .xls), but that didn't work either. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for open file
If that second routine gives you the message, then your workbook isn't named:
"Quote System1.xls" I'd check for extra spaces (or other typos). And it's better to include that .xls when you do stuff like this. Is that a 1 (one) or L (ell) at the end of System? jweasl wrote: I need to check if a file is open - the problem I am having is that it's from a template - I don't know how to find it. This is what I have tried: If Windows("Quote System1").Visible = True Then MsgBox "Visible" _ Else MsgBox "Not Visible" If the window is open, it will give a msgbox saying "Visible", but if it's not open it gives me an error - Subscript out of range. I also tried this: dim wbk as workbook on error resume next set wbk = workbooks("Quote System1.xls") on error goto 0 is wbk is nothing then _ Msgbox "Open the Quote System" This one gives me the message box whether the file is open or not. I also tried "Quote System1" (left out the .xls), but that didn't work either. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for open file
Hi Dave,
You may be correct - my template is Quote System.xlt - when I open the template (opening the window I'm looking for), it's called "Quote System1" (number 1)... there are no other spaces. Any other suggestions? "Dave Peterson" wrote: If that second routine gives you the message, then your workbook isn't named: "Quote System1.xls" I'd check for extra spaces (or other typos). And it's better to include that .xls when you do stuff like this. Is that a 1 (one) or L (ell) at the end of System? jweasl wrote: I need to check if a file is open - the problem I am having is that it's from a template - I don't know how to find it. This is what I have tried: If Windows("Quote System1").Visible = True Then MsgBox "Visible" _ Else MsgBox "Not Visible" If the window is open, it will give a msgbox saying "Visible", but if it's not open it gives me an error - Subscript out of range. I also tried this: dim wbk as workbook on error resume next set wbk = workbooks("Quote System1.xls") on error goto 0 is wbk is nothing then _ Msgbox "Open the Quote System" This one gives me the message box whether the file is open or not. I also tried "Quote System1" (left out the .xls), but that didn't work either. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for open file
The second option will not work because the quote has not been saved yet... I
am a little busy so maybe Dave can help you. If I recall off the top of my head you can traverse through all of the open workbooks and check if the name matches "Quote System"... -- HTH... Jim Thomlinson "jweasl" wrote: I need to check if a file is open - the problem I am having is that it's from a template - I don't know how to find it. This is what I have tried: If Windows("Quote System1").Visible = True Then MsgBox "Visible" _ Else MsgBox "Not Visible" If the window is open, it will give a msgbox saying "Visible", but if it's not open it gives me an error - Subscript out of range. I also tried this: dim wbk as workbook on error resume next set wbk = workbooks("Quote System1.xls") on error goto 0 is wbk is nothing then _ Msgbox "Open the Quote System" This one gives me the message box whether the file is open or not. I also tried "Quote System1" (left out the .xls), but that didn't work either. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for open file
Thanks for your help... I finally got this to work:
Sub test() On Error GoTo Handler If Windows("Quote System1").Visible = True Then MsgBox "Visible" _ Else: GoTo Handler GoTo handlers Handler: MsgBox "Open the Quote System" Exit Sub handlers: End Sub "jweasl" wrote: Hi Dave, You may be correct - my template is Quote System.xlt - when I open the template (opening the window I'm looking for), it's called "Quote System1" (number 1)... there are no other spaces. Any other suggestions? "Dave Peterson" wrote: If that second routine gives you the message, then your workbook isn't named: "Quote System1.xls" I'd check for extra spaces (or other typos). And it's better to include that .xls when you do stuff like this. Is that a 1 (one) or L (ell) at the end of System? jweasl wrote: I need to check if a file is open - the problem I am having is that it's from a template - I don't know how to find it. This is what I have tried: If Windows("Quote System1").Visible = True Then MsgBox "Visible" _ Else MsgBox "Not Visible" If the window is open, it will give a msgbox saying "Visible", but if it's not open it gives me an error - Subscript out of range. I also tried this: dim wbk as workbook on error resume next set wbk = workbooks("Quote System1.xls") on error goto 0 is wbk is nothing then _ Msgbox "Open the Quote System" This one gives me the message box whether the file is open or not. I also tried "Quote System1" (left out the .xls), but that didn't work either. -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for open file
First, I missed that it was opened from a template. (So the .xls can't be there
until the workbook is saved.) dim qwkbk as workbook dim QuoteWkbkIsOpen as boolean quotewkbkisopen = false for each qwkbk in application.workbooks if lcase(qwkbk.name) like "quote system*" then quotewkbkisopen = true exit for end if next qwkbk if quotewkbkisopen then 'no warning else 'warning end if (or open it yourself???) if quotewkbkisopen then 'no warning else set qwkbk = workbooks.add(template:="\\yourpathto\quote system.xlt") 'warning end if The use qwkbk as a reference for that workbook. jweasl wrote: Thanks for your help... I finally got this to work: Sub test() On Error GoTo Handler If Windows("Quote System1").Visible = True Then MsgBox "Visible" _ Else: GoTo Handler GoTo handlers Handler: MsgBox "Open the Quote System" Exit Sub handlers: End Sub "jweasl" wrote: Hi Dave, You may be correct - my template is Quote System.xlt - when I open the template (opening the window I'm looking for), it's called "Quote System1" (number 1)... there are no other spaces. Any other suggestions? "Dave Peterson" wrote: If that second routine gives you the message, then your workbook isn't named: "Quote System1.xls" I'd check for extra spaces (or other typos). And it's better to include that .xls when you do stuff like this. Is that a 1 (one) or L (ell) at the end of System? jweasl wrote: I need to check if a file is open - the problem I am having is that it's from a template - I don't know how to find it. This is what I have tried: If Windows("Quote System1").Visible = True Then MsgBox "Visible" _ Else MsgBox "Not Visible" If the window is open, it will give a msgbox saying "Visible", but if it's not open it gives me an error - Subscript out of range. I also tried this: dim wbk as workbook on error resume next set wbk = workbooks("Quote System1.xls") on error goto 0 is wbk is nothing then _ Msgbox "Open the Quote System" This one gives me the message box whether the file is open or not. I also tried "Quote System1" (left out the .xls), but that didn't work either. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
checking file already open status | Excel Programming | |||
Checking if Workbook is Open | Excel Programming | |||
Checking for an open file in Excel | Excel Programming | |||
Checking for Open Workbook | Excel Programming | |||
VBA Excel Checking whether an XL file is already open elsewhere | Excel Programming |