| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check if workbook open (Not working)
Hi,
My program starts with a simple form to create a new workbook or open an existing one. Once you do either, the form disappears and either opens or starts an new workbook. Once you close either the newly created workbook or the existing one, the form re-appears asking you the same question of creating a new workbook and/or opening an existing one. If the user selects to open an existing workbook and that workbook is the name of the previous workbook he/she has opened prior, in actuality the workbook may very well already be open but not active or visible. My code below checks if the workbook is already opened and if so, closes it and re-opens it. Problem: If the workbook is open the code will close it and the program will go dead stopping after (Workbooks(StrWkBk).Close savechanges:=False). Is there a way to force the program/script to continue? On Error resume next ThisWorkbook.Activate Workbooks(StrWkBk).Close savechanges:=False Set wbResults1 = Workbooks.Open(StrName) -- PK |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if workbook open (Not working)
Hello Patrick,
Not sure, but two things you can look at. First is that your variables are different in the close and open statements. Didn't know if that was by design or not, but could contribute. The other suggestion might be to add On Error Goto 0 between the close statement and open statement then run it again and see if you get an error message. I don't know that the code might be going dead so much as maybe there's an error in that line, but it's simply passing it over due to the resume next statement. Don't know if that will help or not. Good luck. -- Mike Lee McKinney,TX USA "Patrick Kirk" wrote: Hi, My program starts with a simple form to create a new workbook or open an existing one. Once you do either, the form disappears and either opens or starts an new workbook. Once you close either the newly created workbook or the existing one, the form re-appears asking you the same question of creating a new workbook and/or opening an existing one. If the user selects to open an existing workbook and that workbook is the name of the previous workbook he/she has opened prior, in actuality the workbook may very well already be open but not active or visible. My code below checks if the workbook is already opened and if so, closes it and re-opens it. Problem: If the workbook is open the code will close it and the program will go dead stopping after (Workbooks(StrWkBk).Close savechanges:=False). Is there a way to force the program/script to continue? On Error resume next ThisWorkbook.Activate Workbooks(StrWkBk).Close savechanges:=False Set wbResults1 = Workbooks.Open(StrName) -- PK |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if workbook open (Not working)
Mike,
Thanks for the response. Code continues to stop or go dead. Im not able to execute the following lines. -- PK "mikelee101" wrote: Hello Patrick, Not sure, but two things you can look at. First is that your variables are different in the close and open statements. Didn't know if that was by design or not, but could contribute. The other suggestion might be to add On Error Goto 0 between the close statement and open statement then run it again and see if you get an error message. I don't know that the code might be going dead so much as maybe there's an error in that line, but it's simply passing it over due to the resume next statement. Don't know if that will help or not. Good luck. -- Mike Lee McKinney,TX USA "Patrick Kirk" wrote: Hi, My program starts with a simple form to create a new workbook or open an existing one. Once you do either, the form disappears and either opens or starts an new workbook. Once you close either the newly created workbook or the existing one, the form re-appears asking you the same question of creating a new workbook and/or opening an existing one. If the user selects to open an existing workbook and that workbook is the name of the previous workbook he/she has opened prior, in actuality the workbook may very well already be open but not active or visible. My code below checks if the workbook is already opened and if so, closes it and re-opens it. Problem: If the workbook is open the code will close it and the program will go dead stopping after (Workbooks(StrWkBk).Close savechanges:=False). Is there a way to force the program/script to continue? On Error resume next ThisWorkbook.Activate Workbooks(StrWkBk).Close savechanges:=False Set wbResults1 = Workbooks.Open(StrName) -- PK |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check if workbook open (Not working)
If you are closing the workbook that contains the code, then it will go dead
and there is no way to continue. I am not sure why you have the ThisWorkbook.Activate line in there because if it is not the workbook being closed and is the remaining workbook, it automatically becomes the active workbook. "Patrick Kirk" wrote: Mike, Thanks for the response. Code continues to stop or go dead. Im not able to execute the following lines. -- PK "mikelee101" wrote: Hello Patrick, Not sure, but two things you can look at. First is that your variables are different in the close and open statements. Didn't know if that was by design or not, but could contribute. The other suggestion might be to add On Error Goto 0 between the close statement and open statement then run it again and see if you get an error message. I don't know that the code might be going dead so much as maybe there's an error in that line, but it's simply passing it over due to the resume next statement. Don't know if that will help or not. Good luck. -- Mike Lee McKinney,TX USA "Patrick Kirk" wrote: Hi, My program starts with a simple form to create a new workbook or open an existing one. Once you do either, the form disappears and either opens or starts an new workbook. Once you close either the newly created workbook or the existing one, the form re-appears asking you the same question of creating a new workbook and/or opening an existing one. If the user selects to open an existing workbook and that workbook is the name of the previous workbook he/she has opened prior, in actuality the workbook may very well already be open but not active or visible. My code below checks if the workbook is already opened and if so, closes it and re-opens it. Problem: If the workbook is open the code will close it and the program will go dead stopping after (Workbooks(StrWkBk).Close savechanges:=False). Is there a way to force the program/script to continue? On Error resume next ThisWorkbook.Activate Workbooks(StrWkBk).Close savechanges:=False Set wbResults1 = Workbooks.Open(StrName) -- PK |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA to check is someone else has a workbook open | Excel Discussion (Misc queries) | |||
| Check for open workbook | Excel Programming | |||
| Check if workbook is already open... | Excel Programming | |||
| Check if workbook open | Excel Discussion (Misc queries) | |||
| Check for open workbook | Excel Programming | |||
Hybrid Mode
