| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two Open Workbooks
Hi,
I'm not sure I'm doing this right. I used this to open the other workbook in another procedure and it worked fine. Now I'm getting an out of range message. Also, am I identifying the two different workbook correctly in determining the LusedRow? Dim wbkCopyFrom As Workbook Set wbkCopyFrom = Workbooks("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo Done If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''")) Application.ScreenUpdating = False 'get the last tenant's row in the From workbook FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row 'get the last tenant's row in the New workbook NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row -- Thanks for your help. Karen53 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Two Open Workbooks
Close...
Dim wbkCopyFrom As Workbook on error resume next Set wbkCopyFrom = Workbooks("test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else -- HTH... Jim Thomlinson "Karen53" wrote: Hi, I'm not sure I'm doing this right. I used this to open the other workbook in another procedure and it worked fine. Now I'm getting an out of range message. Also, am I identifying the two different workbook correctly in determining the LusedRow? Dim wbkCopyFrom As Workbook Set wbkCopyFrom = Workbooks("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo Done If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''")) Application.ScreenUpdating = False 'get the last tenant's row in the From workbook FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row 'get the last tenant's row in the New workbook NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row -- Thanks for your help. Karen53 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Two Open Workbooks
Thanks, Jim,
I'll try it out tomorrow at work. What does the On Error GoTo 0 do? I've never seen a 0 used before. I would need a 0: before End Sub, right? -- Thanks for your help. Karen53 "Jim Thomlinson" wrote: Close... Dim wbkCopyFrom As Workbook on error resume next Set wbkCopyFrom = Workbooks("test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else -- HTH... Jim Thomlinson "Karen53" wrote: Hi, I'm not sure I'm doing this right. I used this to open the other workbook in another procedure and it worked fine. Now I'm getting an out of range message. Also, am I identifying the two different workbook correctly in determining the LusedRow? Dim wbkCopyFrom As Workbook Set wbkCopyFrom = Workbooks("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo Done If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''")) Application.ScreenUpdating = False 'get the last tenant's row in the From workbook FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row 'get the last tenant's row in the New workbook NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row -- Thanks for your help. Karen53 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Two Open Workbooks
The 0 is not a label in this special case.
You're just telling excel to handle the next error it finds. Karen53 wrote: Thanks, Jim, I'll try it out tomorrow at work. What does the On Error GoTo 0 do? I've never seen a 0 used before. I would need a 0: before End Sub, right? -- Thanks for your help. Karen53 "Jim Thomlinson" wrote: Close... Dim wbkCopyFrom As Workbook on error resume next Set wbkCopyFrom = Workbooks("test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else -- HTH... Jim Thomlinson "Karen53" wrote: Hi, I'm not sure I'm doing this right. I used this to open the other workbook in another procedure and it worked fine. Now I'm getting an out of range message. Also, am I identifying the two different workbook correctly in determining the LusedRow? Dim wbkCopyFrom As Workbook Set wbkCopyFrom = Workbooks("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo Done If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''")) Application.ScreenUpdating = False 'get the last tenant's row in the From workbook FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row 'get the last tenant's row in the New workbook NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row -- Thanks for your help. Karen53 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Two Open Workbooks
Dave;
is that the same as Err.Clear "Dave Peterson" wrote: The 0 is not a label in this special case. You're just telling excel to handle the next error it finds. Karen53 wrote: Thanks, Jim, I'll try it out tomorrow at work. What does the On Error GoTo 0 do? I've never seen a 0 used before. I would need a 0: before End Sub, right? -- Thanks for your help. Karen53 "Jim Thomlinson" wrote: Close... Dim wbkCopyFrom As Workbook on error resume next Set wbkCopyFrom = Workbooks("test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else -- HTH... Jim Thomlinson "Karen53" wrote: Hi, I'm not sure I'm doing this right. I used this to open the other workbook in another procedure and it worked fine. Now I'm getting an out of range message. Also, am I identifying the two different workbook correctly in determining the LusedRow? Dim wbkCopyFrom As Workbook Set wbkCopyFrom = Workbooks("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo Done If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''")) Application.ScreenUpdating = False 'get the last tenant's row in the From workbook FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row 'get the last tenant's row in the New workbook NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row -- Thanks for your help. Karen53 -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Two Open Workbooks
No, On Error GoTo 0 turns off the current error handler... in this case, the
one that was turned on with On Error Resume Next. Rick "DownThePaint" wrote in message ... Dave; is that the same as Err.Clear "Dave Peterson" wrote: The 0 is not a label in this special case. You're just telling excel to handle the next error it finds. Karen53 wrote: Thanks, Jim, I'll try it out tomorrow at work. What does the On Error GoTo 0 do? I've never seen a 0 used before. I would need a 0: before End Sub, right? -- Thanks for your help. Karen53 "Jim Thomlinson" wrote: Close... Dim wbkCopyFrom As Workbook on error resume next Set wbkCopyFrom = Workbooks("test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else -- HTH... Jim Thomlinson "Karen53" wrote: Hi, I'm not sure I'm doing this right. I used this to open the other workbook in another procedure and it worked fine. Now I'm getting an out of range message. Also, am I identifying the two different workbook correctly in determining the LusedRow? Dim wbkCopyFrom As Workbook Set wbkCopyFrom = Workbooks("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo Done If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''")) Application.ScreenUpdating = False 'get the last tenant's row in the From workbook FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row 'get the last tenant's row in the New workbook NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row -- Thanks for your help. Karen53 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Two Open Workbooks
Nope.
Err.clear clears the current error flag. On Error Resume Next MsgBox 1 / 0 MsgBox Err.Number Err.Clear MsgBox Err.Number On Error GoTo 0 MsgBox 1 / 0 MsgBox Err.Number Err.Clear MsgBox Err.Number The top group will "work". The bottom group won't get by the first msgbox. Chip Pearson explains error handling much better he http://cpearson.com/excel/ErrorHandling.htm DownThePaint wrote: Dave; is that the same as Err.Clear "Dave Peterson" wrote: The 0 is not a label in this special case. You're just telling excel to handle the next error it finds. Karen53 wrote: Thanks, Jim, I'll try it out tomorrow at work. What does the On Error GoTo 0 do? I've never seen a 0 used before. I would need a 0: before End Sub, right? -- Thanks for your help. Karen53 "Jim Thomlinson" wrote: Close... Dim wbkCopyFrom As Workbook on error resume next Set wbkCopyFrom = Workbooks("test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo 0 If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else -- HTH... Jim Thomlinson "Karen53" wrote: Hi, I'm not sure I'm doing this right. I used this to open the other workbook in another procedure and it worked fine. Now I'm getting an out of range message. Also, am I identifying the two different workbook correctly in determining the LusedRow? Dim wbkCopyFrom As Workbook Set wbkCopyFrom = Workbooks("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") If wbkCopyFrom Is Nothing Then Set wbkCopyFrom = Workbooks.Open("C:\Documents and Settings\Eileen\Desktop\New Workbooks\test.xls") On Error GoTo Done If wbkCopyFrom Is Nothing Then MsgBox "Cannot find originating file" Else Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''")) Application.ScreenUpdating = False 'get the last tenant's row in the From workbook FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row 'get the last tenant's row in the New workbook NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row -- Thanks for your help. Karen53 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA Excel.WorkBooks.Open can't open an XML format xls file | Excel Programming | |||
| When I open Excel, workbooks open automatically. How can I stop t | Excel Discussion (Misc queries) | |||
| workbooks.open function fails to open an existing excel file when used in ASP, but works in VB. | Excel Programming | |||
| Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed | Excel Programming | |||
| Workbooks.Open / .Open Text - How do you stop the .xls addition? | Excel Programming | |||
Linear Mode
