Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking if Workbook is Open
Hi,
I have a workbook that is a template for charting data selected from another workbook. The other workbook might be one of several different ones so I do not want to link to any one specific workbook. I have a macro that pulls a subsection of data from the other workbook into the charting workbook. When the macro begins I want test to see if the other workbook open is already open. If it is then the macro proceeds and pulls int he data. It the workbook is not open then I want that workbook to open. How do I test to see if a workbook is open in the application? If the result could be a boolean then I could use and IF statement to do the test and decide whether to open the worksheet. Thanks. -- Mark Mesarch School of Natural Resources University of Nebraska-Lincoln |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking if Workbook is Open
Hi Mark
Function IsOpen(sName As String) As Boolean On Error Resume Next IsOpen = Len(Workbooks(sName).Name) End Function Sub test() MsgBox IsOpen("Book2.xls") End Sub HTH. Best wishes Harald "MMesarch" skrev i melding ... Hi, I have a workbook that is a template for charting data selected from another workbook. The other workbook might be one of several different ones so I do not want to link to any one specific workbook. I have a macro that pulls a subsection of data from the other workbook into the charting workbook. When the macro begins I want test to see if the other workbook open is already open. If it is then the macro proceeds and pulls int he data. It the workbook is not open then I want that workbook to open. How do I test to see if a workbook is open in the application? If the result could be a boolean then I could use and IF statement to do the test and decide whether to open the worksheet. Thanks. -- Mark Mesarch School of Natural Resources University of Nebraska-Lincoln |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking if Workbook is Open
Harald and Sebastien, Thanks that did the trick
-- Mark Mesarch School of Natural Resources University of Nebraska-Lincoln "Harald Staff" wrote: Hi Mark Function IsOpen(sName As String) As Boolean On Error Resume Next IsOpen = Len(Workbooks(sName).Name) End Function Sub test() MsgBox IsOpen("Book2.xls") End Sub HTH. Best wishes Harald "MMesarch" skrev i melding ... Hi, I have a workbook that is a template for charting data selected from another workbook. The other workbook might be one of several different ones so I do not want to link to any one specific workbook. I have a macro that pulls a subsection of data from the other workbook into the charting workbook. When the macro begins I want test to see if the other workbook open is already open. If it is then the macro proceeds and pulls int he data. It the workbook is not open then I want that workbook to open. How do I test to see if a workbook is open in the application? If the result could be a boolean then I could use and IF statement to do the test and decide whether to open the worksheet. Thanks. -- Mark Mesarch School of Natural Resources University of Nebraska-Lincoln |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking if Workbook is Open
Hi,
You could just set a workbook variable with error checking as Harald proposed. If you also need to check on the path to differentiate the case when a book with same name is already open but from a different path (in which case you won't be able to open your book unless you first close that one), you can use the following macro: '----------------------------------------------- ' -If book is not open then returns 0 ' -if book is open then returns 1 ' (if path not specified in Bookname, does not differentiate on path) ' -if a book is not open but a book with same name but another path is open then 2 ' (in this last case: won't be able to open the book) Function IsBookOpen(BookName As String) As Long Dim wkb As Workbook Dim sep As String 'path separator Dim path As String 'book path Dim name As String ' book filename Dim pos As Long 'position of last 'sep' in bookname sep = Application.PathSeparator 'if BookName contains pathSeparator then extract path and name If BookName Like ("*" & sep & "*") Then pos = InStrRev(BookName, sep) path = Left(BookName, pos) name = Right(BookName, Len(BookName) - pos) Else path = "" name = BookName End If On Error Resume Next Set wkb = Workbooks(name) If Err < 0 Then 'not open IsBookOpen = 0 Else 'open If path = "" Then 'open and since path not specified, it assumes it is the one. IsBookOpen = 1 ElseIf UCase(wkb.path & sep) = UCase(path) Then 'and same bookName & path IsBookOpen = 1 Else 'and same bookname but different path IsBookOpen = 2 End If End If End Function '---------------------------------------------------------- Regards, SĂ©bastien <http://www.ondemandanalysis.com "MMesarch" wrote: Hi, I have a workbook that is a template for charting data selected from another workbook. The other workbook might be one of several different ones so I do not want to link to any one specific workbook. I have a macro that pulls a subsection of data from the other workbook into the charting workbook. When the macro begins I want test to see if the other workbook open is already open. If it is then the macro proceeds and pulls int he data. It the workbook is not open then I want that workbook to open. How do I test to see if a workbook is open in the application? If the result could be a boolean then I could use and IF statement to do the test and decide whether to open the worksheet. Thanks. -- Mark Mesarch School of Natural Resources University of Nebraska-Lincoln |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking if Workbook is Open
"sebastienm" skrev i melding
... If you also need to check on the path to differentiate the case when a book with same name is already open but from a different path (...) Good point, Sébastien. Thanks. Best wishes Harald |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking for open workbook... | Excel Programming | |||
Checking to see if a Workbook is Open | Excel Programming | |||
Checking if workbook is open | Excel Programming | |||
Checking for Open Workbook | Excel Programming | |||
checking if workbook is open before accessing | Excel Programming |