Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a workbook is open already
Follow-up on my last thread (which was answered, thus the new thread but same project...) At the beginning of my code, I use getopenfilename and then open workbook. I want a check (if/then?) between the getopenfilename an the open command, that will check to see if the filename entered i already open, if it is, skip the open command and continue with th rest of the code. Make sense? This is what I have: Sub get1degdata() Dim fname As Variant Dim Wkbk As Workbook Dim wksht As Worksheet fname = Application.GetOpenFilename("Excel files(*.xls),*.xls") Set Wkbk = Workbooks.Open(fname) Wkbk.Activate MsgBox fname |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a workbook is open already
This is what I use; Dim wbk As Workbook Dim old_book As Workbook Set old_book = ActiveWorkbook On Error Resume Next Set wbk = Workbooks("Test.xls") On Error GoTo 0 If wbk Is Nothing Then Workbooks.Open Filename:= _ "C:\Documents\test.xls" old_book.Activate Exit Sub End If Lenny Kieran1028 Wrote: Follow-up on my last thread (which was answered, thus the new thread but same project...) At the beginning of my code, I use getopenfilename and then open workbook. I want a check (if/then?) between the getopenfilename an the open command, that will check to see if the filename entered i already open, if it is, skip the open command and continue with th rest of the code. Make sense? This is what I have: Sub get1degdata() Dim fname As Variant Dim Wkbk As Workbook Dim wksht As Worksheet fname = Application.GetOpenFilename("Excel files(*.xls),*.xls") Set Wkbk = Workbooks.Open(fname) Wkbk.Activate MsgBox fname . . . Thanks, Kiera -- Lenny_82 ----------------------------------------------------------------------- Lenny_821's Profile: http://www.excelforum.com/member.php...fo&userid=1517 View this thread: http://www.excelforum.com/showthread.php?threadid=31944 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a workbook is open already
Try something like the following:
Dim WB As Workbook Dim IsWorkbookOpen As Boolean For Each WB In Workbooks If WB.FullName = FName Then IsWorkbookOpen = True Exit For End If Next WB If IsWorkbookOpen = False Then Set WB = Workbooks.Open(FName) End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kieran1028" wrote in message ... Follow-up on my last thread (which was answered, thus the new thread, but same project...) At the beginning of my code, I use getopenfilename and then open a workbook. I want a check (if/then?) between the getopenfilename and the open command, that will check to see if the filename entered is already open, if it is, skip the open command and continue with the rest of the code. Make sense? This is what I have: Sub get1degdata() Dim fname As Variant Dim Wkbk As Workbook Dim wksht As Worksheet fname = Application.GetOpenFilename("Excel files(*.xls),*.xls") Set Wkbk = Workbooks.Open(fname) Wkbk.Activate MsgBox fname |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a workbook is open already
Lenny, But if you have several open workbooks, how would you test if just ONE of them was the name entered in getopenfilename? I think your method assumes that oldbook is the only other open book, right? -- Kieran1028 ------------------------------------------------------------------------ Kieran1028's Profile: http://www.excelforum.com/member.php...o&userid=15678 View this thread: http://www.excelforum.com/showthread...hreadid=319442 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a workbook is open already
You need Chip's function also
Function IsWorkbookOpen(WBName As String) As Boolean On Error Resume Next IsWorkbookOpen = CBool(Len(Workbooks(WBName).Name)) End Function -- Regards Ron de Bruin http://www.rondebruin.nl "Chip Pearson" wrote in message ... Try something like the following: Dim WB As Workbook Dim IsWorkbookOpen As Boolean For Each WB In Workbooks If WB.FullName = FName Then IsWorkbookOpen = True Exit For End If Next WB If IsWorkbookOpen = False Then Set WB = Workbooks.Open(FName) End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kieran1028" wrote in message ... Follow-up on my last thread (which was answered, thus the new thread, but same project...) At the beginning of my code, I use getopenfilename and then open a workbook. I want a check (if/then?) between the getopenfilename and the open command, that will check to see if the filename entered is already open, if it is, skip the open command and continue with the rest of the code. Make sense? This is what I have: Sub get1degdata() Dim fname As Variant Dim Wkbk As Workbook Dim wksht As Worksheet fname = Application.GetOpenFilename("Excel files(*.xls),*.xls") Set Wkbk = Workbooks.Open(fname) Wkbk.Activate MsgBox fname |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a workbook is open already
Oops
Forget it<g -- Regards Ron de Bruin http://www.rondebruin.nl "Ron de Bruin" wrote in message ... You need Chip's function also Function IsWorkbookOpen(WBName As String) As Boolean On Error Resume Next IsWorkbookOpen = CBool(Len(Workbooks(WBName).Name)) End Function -- Regards Ron de Bruin http://www.rondebruin.nl "Chip Pearson" wrote in message ... Try something like the following: Dim WB As Workbook Dim IsWorkbookOpen As Boolean For Each WB In Workbooks If WB.FullName = FName Then IsWorkbookOpen = True Exit For End If Next WB If IsWorkbookOpen = False Then Set WB = Workbooks.Open(FName) End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kieran1028" wrote in message ... Follow-up on my last thread (which was answered, thus the new thread, but same project...) At the beginning of my code, I use getopenfilename and then open a workbook. I want a check (if/then?) between the getopenfilename and the open command, that will check to see if the filename entered is already open, if it is, skip the open command and continue with the rest of the code. Make sense? This is what I have: Sub get1degdata() Dim fname As Variant Dim Wkbk As Workbook Dim wksht As Worksheet fname = Application.GetOpenFilename("Excel files(*.xls),*.xls") Set Wkbk = Workbooks.Open(fname) Wkbk.Activate MsgBox fname |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a workbook is open already
That works only with the workbook name, not the full file name,
as the OP needed. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Ron de Bruin" wrote in message ... You need Chip's function also Function IsWorkbookOpen(WBName As String) As Boolean On Error Resume Next IsWorkbookOpen = CBool(Len(Workbooks(WBName).Name)) End Function -- Regards Ron de Bruin http://www.rondebruin.nl "Chip Pearson" wrote in message ... Try something like the following: Dim WB As Workbook Dim IsWorkbookOpen As Boolean For Each WB In Workbooks If WB.FullName = FName Then IsWorkbookOpen = True Exit For End If Next WB If IsWorkbookOpen = False Then Set WB = Workbooks.Open(FName) End If -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Kieran1028" wrote in message ... Follow-up on my last thread (which was answered, thus the new thread, but same project...) At the beginning of my code, I use getopenfilename and then open a workbook. I want a check (if/then?) between the getopenfilename and the open command, that will check to see if the filename entered is already open, if it is, skip the open command and continue with the rest of the code. Make sense? This is what I have: Sub get1degdata() Dim fname As Variant Dim Wkbk As Workbook Dim wksht As Worksheet fname = Application.GetOpenFilename("Excel files(*.xls),*.xls") Set Wkbk = Workbooks.Open(fname) Wkbk.Activate MsgBox fname |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test if a workbook is open already
Chip, your code worked (again). Although I did need to add the line WB.Activate After the ISWorkbookOpen = True line. Thanks (again)! -- Kieran1028 ------------------------------------------------------------------------ Kieran1028's Profile: http://www.excelforum.com/member.php...o&userid=15678 View this thread: http://www.excelforum.com/showthread...hreadid=319442 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Test to see if a workbook is open | Excel Discussion (Misc queries) | |||
Test if Lotus Notes open | Excel Programming | |||
Test that a workbook is open | Excel Programming | |||
test for workbook open | Excel Programming | |||
Test to see if a spreadsheet window is open | Excel Programming |