check Wb is open and copy data to it
in [Programming]
|
From: Jacob Skaria on 20 Apr 2010 08:34 Try the below Function IsWorkbookOpen(strWorkbook) As Boolean Dim wb As Workbook On Error Resume Next Set wb = Workbooks(strWorkbook) If Not wb Is Nothing Then IsWorkbookOpen = True End Function Sub X_Fer() 'copies data If IsWorkbookOpen("R of C.xls") Then Set DestSh = Workbooks("R of C").Worksheets("Register") Else Workbooks.Open "S:\blah\blah\R of C.xls" End If End Sub -- Jacob (MVP - Excel) "Jock" wrote: > When the second Wb is open, no problems. If it is closed, however, then I get > the run time error. > I tried putting the full path in i.e. - IsWorkbookOpen ("S:\blah\blah\R of > C.xls"). > Part of the code below. > > Sub X_Fer() 'copies data > > Dim Srng As Range > Dim Drng As Range > Dim DestWb As Workbooks > Dim DestSh As Worksheet > Dim SouSh As Worksheet > Dim SSh As Worksheet > Dim rng As Range > > > > Set SSh = Workbooks("CMS").Worksheets("new") 'source worksheet > > If IsWorkbookOpen("S:\blah\blah\R of C.xls") Then > Set DestSh = Workbooks("R of C").Worksheets("Register") 'destination worksheet > Else > Workbooks.Open ("S:\blah\blah\R of C.xls") > > 'more code here... > > thanks. > -- > Traa Dy Liooar > > Jock > > > "Jacob Skaria" wrote: > > > Why dont you post what you tried. When the workbook is open you dont need to > > mention the full path..Just the workbook name as displayed in the caption. > > The extension .xls or .xlsx depends whether the workbook is saved...I hope it > > is saved and so you will need the extension... > > > > Msgbox IsWorkbookOpen("filename.xls") > > > > will return True/False .. > > > > Paste the code in a fresh module and try. If you are still unable to...then > > post the code which you are trying.. > > > > > > > > -- > > Jacob (MVP - Excel) > > > > > > "Jock" wrote: > > > > > Thanks Jacob. > > > I'm still getting the 'subscript out of range' error message (Run time error > > > 9) on the Set wb = stage of the Function. > > > I have tried the full path to the file (which is on a network) with and > > > without the .xls extention with the same result. > > > -- > > > Traa Dy Liooar > > > > > > Jock > > > > > > > > > "Jacob Skaria" wrote: > > > > > > > Copy the below function and use it in your macro as shown in the below example > > > > > > > > Sub Macro1() > > > > If IsWorkbookOpen("book3.xls") Then > > > > 'place your code > > > > End If > > > > > > > > End Sub > > > > Function IsWorkbookOpen(strWorkbook) As Boolean > > > > Dim wb As Workbook > > > > On Error Resume Next > > > > Set wb = Workbooks(strWorkbook) > > > > If Not wb Is Nothing Then IsWorkbookOpen = True > > > > End Function > > > > > > > > > > > > -- > > > > Jacob (MVP - Excel) > > > > > > > > > > > > "Jock" wrote: > > > > > > > > > Wb1, Sht2 has data in cols A-D. This data is copied to this location by code > > > > > daily when new cases are started on sht1 in the same Wb. > > > > > Once a week, I need the data from Sht2 to be copied to WB2, Sht3 by clicking > > > > > a button. Once data copied over to Wb2, Sht 3, the data on Wb1, Sht 2 to be > > > > > deleted. > > > > > The problem I am having is checking to see if Wb2 is open - I get the > > > > > 'subscript out of range' error. > > > > > How do I check if a workbook is open (without getting a debug window), open > > > > > it if it is closed and show a message to that effect? > > > > > -- > > > > > Traa Dy Liooar > > > > > > > > > > Jock
From: Don Guillett on 20 Apr 2010 08:39 This should activate if open or open if closed Sub GetWorkbook() workbookname = "wb2" On Error GoTo OpenWorkbook Windows(workbookname & ".xls").Activate Exit Sub OpenWorkbook: Workbooks.Open(workbookname & ".xls").RunAutoMacros xlAutoOpen End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Jock" <Jock(a)discussions.microsoft.com> wrote in message news:32A0350B-A88A-4E36-8770-DB6CA7277BD6(a)microsoft.com... > Wb1, Sht2 has data in cols A-D. This data is copied to this location by > code > daily when new cases are started on sht1 in the same Wb. > Once a week, I need the data from Sht2 to be copied to WB2, Sht3 by > clicking > a button. Once data copied over to Wb2, Sht 3, the data on Wb1, Sht 2 to > be > deleted. > The problem I am having is checking to see if Wb2 is open - I get the > 'subscript out of range' error. > How do I check if a workbook is open (without getting a debug window), > open > it if it is closed and show a message to that effect? > -- > Traa Dy Liooar > > Jock
From: Jock on 20 Apr 2010 09:28 Hi Jacob, thanks for trying. Even with new, empty modules I get the same run time error. I'll just have to code a message box to appear if "R of C" isn't open and get the users to do it manually -- Traa Dy Liooar Jock "Jacob Skaria" wrote: > Try the below > > Function IsWorkbookOpen(strWorkbook) As Boolean > Dim wb As Workbook > On Error Resume Next > Set wb = Workbooks(strWorkbook) > If Not wb Is Nothing Then IsWorkbookOpen = True > End Function > > Sub X_Fer() 'copies data > > If IsWorkbookOpen("R of C.xls") Then > Set DestSh = Workbooks("R of C").Worksheets("Register") > Else > Workbooks.Open "S:\blah\blah\R of C.xls" > End If > > End Sub > > -- > Jacob (MVP - Excel) > > > "Jock" wrote: > > > When the second Wb is open, no problems. If it is closed, however, then I get > > the run time error. > > I tried putting the full path in i.e. - IsWorkbookOpen ("S:\blah\blah\R of > > C.xls"). > > Part of the code below. > > > > Sub X_Fer() 'copies data > > > > Dim Srng As Range > > Dim Drng As Range > > Dim DestWb As Workbooks > > Dim DestSh As Worksheet > > Dim SouSh As Worksheet > > Dim SSh As Worksheet > > Dim rng As Range > > > > > > > > Set SSh = Workbooks("CMS").Worksheets("new") 'source worksheet > > > > If IsWorkbookOpen("S:\blah\blah\R of C.xls") Then > > Set DestSh = Workbooks("R of C").Worksheets("Register") 'destination worksheet > > Else > > Workbooks.Open ("S:\blah\blah\R of C.xls") > > > > 'more code here... > > > > thanks. > > -- > > Traa Dy Liooar > > > > Jock > > > > > > "Jacob Skaria" wrote: > > > > > Why dont you post what you tried. When the workbook is open you dont need to > > > mention the full path..Just the workbook name as displayed in the caption. > > > The extension .xls or .xlsx depends whether the workbook is saved...I hope it > > > is saved and so you will need the extension... > > > > > > Msgbox IsWorkbookOpen("filename.xls") > > > > > > will return True/False .. > > > > > > Paste the code in a fresh module and try. If you are still unable to...then > > > post the code which you are trying.. > > > > > > > > > > > > -- > > > Jacob (MVP - Excel) > > > > > > > > > "Jock" wrote: > > > > > > > Thanks Jacob. > > > > I'm still getting the 'subscript out of range' error message (Run time error > > > > 9) on the Set wb = stage of the Function. > > > > I have tried the full path to the file (which is on a network) with and > > > > without the .xls extention with the same result. > > > > -- > > > > Traa Dy Liooar > > > > > > > > Jock > > > > > > > > > > > > "Jacob Skaria" wrote: > > > > > > > > > Copy the below function and use it in your macro as shown in the below example > > > > > > > > > > Sub Macro1() > > > > > If IsWorkbookOpen("book3.xls") Then > > > > > 'place your code > > > > > End If > > > > > > > > > > End Sub > > > > > Function IsWorkbookOpen(strWorkbook) As Boolean > > > > > Dim wb As Workbook > > > > > On Error Resume Next > > > > > Set wb = Workbooks(strWorkbook) > > > > > If Not wb Is Nothing Then IsWorkbookOpen = True > > > > > End Function > > > > > > > > > > > > > > > -- > > > > > Jacob (MVP - Excel) > > > > > > > > > > > > > > > "Jock" wrote: > > > > > > > > > > > Wb1, Sht2 has data in cols A-D. This data is copied to this location by code > > > > > > daily when new cases are started on sht1 in the same Wb. > > > > > > Once a week, I need the data from Sht2 to be copied to WB2, Sht3 by clicking > > > > > > a button. Once data copied over to Wb2, Sht 3, the data on Wb1, Sht 2 to be > > > > > > deleted. > > > > > > The problem I am having is checking to see if Wb2 is open - I get the > > > > > > 'subscript out of range' error. > > > > > > How do I check if a workbook is open (without getting a debug window), open > > > > > > it if it is closed and show a message to that effect? > > > > > > -- > > > > > > Traa Dy Liooar > > > > > > > > > > > > Jock
From: Jacob Skaria on 20 Apr 2010 09:44 I missed the extension in the previous post... If IsWorkbookOpen("R of C.xls") Then Set DestSh = Workbooks("R of C.xls").Worksheets("Register") Else Workbooks.Open "S:\blah\blah\R of C.xls" End If -- Jacob (MVP - Excel) "Jock" wrote: > Hi Jacob, thanks for trying. > Even with new, empty modules I get the same run time error. > I'll just have to code a message box to appear if "R of C" isn't open and > get the users to do it manually > -- > Traa Dy Liooar > > Jock > > > "Jacob Skaria" wrote: > > > Try the below > > > > Function IsWorkbookOpen(strWorkbook) As Boolean > > Dim wb As Workbook > > On Error Resume Next > > Set wb = Workbooks(strWorkbook) > > If Not wb Is Nothing Then IsWorkbookOpen = True > > End Function > > > > Sub X_Fer() 'copies data > > > > If IsWorkbookOpen("R of C.xls") Then > > Set DestSh = Workbooks("R of C").Worksheets("Register") > > Else > > Workbooks.Open "S:\blah\blah\R of C.xls" > > End If > > > > End Sub > > > > -- > > Jacob (MVP - Excel) > > > > > > "Jock" wrote: > > > > > When the second Wb is open, no problems. If it is closed, however, then I get > > > the run time error. > > > I tried putting the full path in i.e. - IsWorkbookOpen ("S:\blah\blah\R of > > > C.xls"). > > > Part of the code below. > > > > > > Sub X_Fer() 'copies data > > > > > > Dim Srng As Range > > > Dim Drng As Range > > > Dim DestWb As Workbooks > > > Dim DestSh As Worksheet > > > Dim SouSh As Worksheet > > > Dim SSh As Worksheet > > > Dim rng As Range > > > > > > > > > > > > Set SSh = Workbooks("CMS").Worksheets("new") 'source worksheet > > > > > > If IsWorkbookOpen("S:\blah\blah\R of C.xls") Then > > > Set DestSh = Workbooks("R of C").Worksheets("Register") 'destination worksheet > > > Else > > > Workbooks.Open ("S:\blah\blah\R of C.xls") > > > > > > 'more code here... > > > > > > thanks. > > > -- > > > Traa Dy Liooar > > > > > > Jock > > > > > > > > > "Jacob Skaria" wrote: > > > > > > > Why dont you post what you tried. When the workbook is open you dont need to > > > > mention the full path..Just the workbook name as displayed in the caption. > > > > The extension .xls or .xlsx depends whether the workbook is saved...I hope it > > > > is saved and so you will need the extension... > > > > > > > > Msgbox IsWorkbookOpen("filename.xls") > > > > > > > > will return True/False .. > > > > > > > > Paste the code in a fresh module and try. If you are still unable to...then > > > > post the code which you are trying.. > > > > > > > > > > > > > > > > -- > > > > Jacob (MVP - Excel) > > > > > > > > > > > > "Jock" wrote: > > > > > > > > > Thanks Jacob. > > > > > I'm still getting the 'subscript out of range' error message (Run time error > > > > > 9) on the Set wb = stage of the Function. > > > > > I have tried the full path to the file (which is on a network) with and > > > > > without the .xls extention with the same result. > > > > > -- > > > > > Traa Dy Liooar > > > > > > > > > > Jock > > > > > > > > > > > > > > > "Jacob Skaria" wrote: > > > > > > > > > > > Copy the below function and use it in your macro as shown in the below example > > > > > > > > > > > > Sub Macro1() > > > > > > If IsWorkbookOpen("book3.xls") Then > > > > > > 'place your code > > > > > > End If > > > > > > > > > > > > End Sub > > > > > > Function IsWorkbookOpen(strWorkbook) As Boolean > > > > > > Dim wb As Workbook > > > > > > On Error Resume Next > > > > > > Set wb = Workbooks(strWorkbook) > > > > > > If Not wb Is Nothing Then IsWorkbookOpen = True > > > > > > End Function > > > > > > > > > > > > > > > > > > -- > > > > > > Jacob (MVP - Excel) > > > > > > > > > > > > > > > > > > "Jock" wrote: > > > > > > > > > > > > > Wb1, Sht2 has data in cols A-D. This data is copied to this location by code > > > > > > > daily when new cases are started on sht1 in the same Wb. > > > > > > > Once a week, I need the data from Sht2 to be copied to WB2, Sht3 by clicking > > > > > > > a button. Once data copied over to Wb2, Sht 3, the data on Wb1, Sht 2 to be > > > > > > > deleted. > > > > > > > The problem I am having is checking to see if Wb2 is open - I get the > > > > > > > 'subscript out of range' error. > > > > > > > How do I check if a workbook is open (without getting a debug window), open > > > > > > > it if it is closed and show a message to that effect? > > > > > > > -- > > > > > > > Traa Dy Liooar > > > > > > > > > > > > > > Jock
From: Jock on 20 Apr 2010 10:51 Guys. Regardless of what I do, when the second Wb is closed, I get run time errors in: Set wb = Workbooks(strWorkbook) (Jacob), and Windows(workbookname & ".xls").Activate (Don) When Wb is open, both work fine! Thanks though. -- Traa Dy Liooar Jock "Don Guillett" wrote: > This should activate if open or open if closed > > Sub GetWorkbook() > workbookname = "wb2" > On Error GoTo OpenWorkbook > Windows(workbookname & ".xls").Activate > Exit Sub > OpenWorkbook: > Workbooks.Open(workbookname & ".xls").RunAutoMacros xlAutoOpen > End Sub > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett(a)gmail.com > "Jock" <Jock(a)discussions.microsoft.com> wrote in message > news:32A0350B-A88A-4E36-8770-DB6CA7277BD6(a)microsoft.com... > > Wb1, Sht2 has data in cols A-D. This data is copied to this location by > > code > > daily when new cases are started on sht1 in the same Wb. > > Once a week, I need the data from Sht2 to be copied to WB2, Sht3 by > > clicking > > a button. Once data copied over to Wb2, Sht 3, the data on Wb1, Sht 2 to > > be > > deleted. > > The problem I am having is checking to see if Wb2 is open - I get the > > 'subscript out of range' error. > > How do I check if a workbook is open (without getting a debug window), > > open > > it if it is closed and show a message to that effect? > > -- > > Traa Dy Liooar > > > > Jock > > . >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Pivot tables Next: Cell.Text different from Cell.Value when dates are concerned.. |