Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for open workbook
Hi.
I need to create vba code within a excel macro to detemine if a workbook is already open. If it is, I want to continue with the macro. If it isnt, I want to open the workbook and then continue with the macro. I am sure this must be pretty easy. I'm just struggling to work out how to do it. Can anyone give me some suitable code. Many thanks in advance. Nigel ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for open workbook
On Sat, 1 Nov 2003 03:43:08 -0500, nigelab
wrote: I need to create vba code within a excel macro to detemine if a workbook is already open. If it is, I want to continue with the macro. If it isnt, I want to open the workbook and then continue with the macro. This is a little rough, but it'll give you an idea of how to do it: Sub TestForOpenFile() Dim s_WbkName As String Dim wbk As Workbook 'Note that the workbook name may or may not have 'an extension, depending on your Windows Explorer 'settings. I'd therefore test both. 'If the workbook's not open, an error will 'occur (which we suppress), and wbk will 'remain nothing. On Error Resume Next Set wbk = Application.Workbooks("TestWorkbook") If wbk Is Nothing Then Set wbk = Application.Workbooks("TestWorkbook.xls") End If 'Change this to your file s_WbkName = "C:\Documents and Settings\" _ & "Hank Scorpio\My Documents\Testworkbook.xls" On Error GoTo 0 If wbk Is Nothing Then 'Make sure that the file is there 'before you try to open it. If Dir(s_WbkName, _ vbNormal) = "" Then MsgBox "The file does not exist." Exit Sub Else Workbooks.Open s_WbkName End If End If MsgBox "This is the rest of the macro." End Sub --------------------------------------------------------- Hank Scorpio scorpionet who hates spam is at iprimus.com.au (You know what to do.) * Please keep all replies in this Newsgroup. Thanks! * |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for open workbook
On Sat, 1 Nov 2003 03:43:08 -0500, nigelab
wrote in microsoft.public.excel.programming: I need to create vba code within a excel macro to detemine if a workbook is already open. If it is, I want to continue with the macro. If it isnt, I want to open the workbook and then continue with the macro. I am sure this must be pretty easy. I'm just struggling to work out how to do it. Can anyone give me some suitable code. Try this: ================================================== ==================== Dim wb As Workbook Dim haveFound As Boolean Const sName As String = "fubar.xls" haveFound = False For Each wb In Workbooks If wb.Name = sName Then haveFound = True Exit For End If Next wb MsgBox haveFound ================================================== ==================== Another common method is to simply refer to the workbook in question by its name and use the Err object to determine the success: ================================================== ==================== On Error Resume Next Set wb = Workbooks(sName) If Err < 0 Then haveFound = False Else haveFound = True End If On Error GoTo 0 MsgBox haveFound ================================================== ==================== I prefer the first method. -- Michael Bednarek, Brisbane, Australia http://mcmbednarek.tripod.com/ "POST NO BILLS" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for open workbook
On Sat, 01 Nov 2003 03:43:08 -0500, nigelab wrote:
Hi. I need to create vba code within a excel macro to detemine if a workbook is already open. If it is, I want to continue with the macro. If it isnt, I want to open the workbook and then continue with the macro. I am sure this must be pretty easy. I'm just struggling to work out how to do it. Can anyone give me some suitable code. Many thanks in advance. Nigel ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ I had the same problem last week. I found a simple way depending if you want only one person in the workbook at the same time. There is a property on the workbook ReadOnly. If you use this, you can detect if someone else has taken a lock on the file. So... If SomeWorkbook.ReadOnly = True Then SomeCodeHere End If Cheers, Mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Testing for open workbook
Thanks to all. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
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) | |||
testing. do not open. | Excel Worksheet Functions |