Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Is a workbook open
In running a Visual Basic macro in Excel 2002, I have the
following situation. The macro opens another workbook (that already exists) to read and write some values in it. If the other workbook is already open, then it has to be closed and reopened each time (in response to a dialog box) or the macro terminates. I would like to omit the request to open the other workbook in the event that it is already open. But I cannot find any function in Visual Basic that can return the status of the other workbook (i.e., whether or not it is already open). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Is a workbook open
Hi Linda,
Dim WB as Workbook On Error Resume Next Set WB = Workbooks("YourWorkbookName") On Error Goto 0 If Not WB is Nothing Then 'Workbook is open Else 'Workbook not open End if --- Regards, Norman "linda" wrote in message ... In running a Visual Basic macro in Excel 2002, I have the following situation. The macro opens another workbook (that already exists) to read and write some values in it. If the other workbook is already open, then it has to be closed and reopened each time (in response to a dialog box) or the macro terminates. I would like to omit the request to open the other workbook in the event that it is already open. But I cannot find any function in Visual Basic that can return the status of the other workbook (i.e., whether or not it is already open). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Is a workbook open
Dim bk as Workbook
Dim bOpen On Error Resume Next set bk = workbooks("Mybook.xls") On Error goto 0 bOpen = False if not bk is nothing then ' wkbk is open do nothing bOpen = True Else set bk = Workbooks.Open("C:\Myfiles\Mybook.xls") End if ' at this point bk holds a reference to Mybook.xls .. . . if not bOpen then bk.Close Savechanges:=False End if End Sub -- Regards, Tom Ogilvy "linda" wrote in message ... In running a Visual Basic macro in Excel 2002, I have the following situation. The macro opens another workbook (that already exists) to read and write some values in it. If the other workbook is already open, then it has to be closed and reopened each time (in response to a dialog box) or the macro terminates. I would like to omit the request to open the other workbook in the event that it is already open. But I cannot find any function in Visual Basic that can return the status of the other workbook (i.e., whether or not it is already open). |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Is a workbook open
Linda,
On Error Resume Next Windows("Filename.xls").Close False 'To not save changes, True to save Workbooks.Open "C:\Excel\Filename.xls" HTH, Bernie MS Excel MVP "linda" wrote in message ... In running a Visual Basic macro in Excel 2002, I have the following situation. The macro opens another workbook (that already exists) to read and write some values in it. If the other workbook is already open, then it has to be closed and reopened each time (in response to a dialog box) or the macro terminates. I would like to omit the request to open the other workbook in the event that it is already open. But I cannot find any function in Visual Basic that can return the status of the other workbook (i.e., whether or not it is already open). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to: Open closed workbook/Search data tables/Return data to open workbook | Excel Discussion (Misc queries) | |||
Opening Excel, Book1 opens, remains open with other workbook open | Excel Discussion (Misc queries) | |||
how do i open a data workbook when i open a timesheet workbook | Excel Discussion (Misc queries) | |||
excel 2003 saved file will not open without a blank workbook open | Excel Discussion (Misc queries) | |||
Search open sheets in workbook and insert into open sheet | Excel Discussion (Misc queries) |