check Wb is open and copy data to it
in [Programming]
|
From: Jock on 20 Apr 2010 07:09 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 07:14 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 07:31 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 07:38 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 08:08 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
|
Next
|
Last
Pages: 1 2 3 Prev: Pivot tables Next: Cell.Text different from Cell.Value when dates are concerned.. |