quit on error
When I open Workbook2 (WB2) I need for it to check and see if Workbook1 (WB1)
exists and if so, I need for data to be updated. I have the following code; On Error Resume Next Workbooks.Open ("C:\path\wb1"), Password:="xxx" Sheets("sheet1").Select Cells.Select Selection.Copy Windows("wb2.xls").Activate Sheets("data").Select Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Workbooks("wb1.xls").Close True Kill ("C:\path\WB1.xls") On Error goto 0 It works fine if WB1 exists. However if WB1 does not exist it removes all the data in WB2 Sheets("data"). I need for it to somehow quit all together when it errors on "Workbooks.Open ("C:\path\wb1"), Password:="xxx"" when the workbook does not exist. Any help is very much appreciated. |
quit on error
Give this a try...
Dim wbk as workbook On Error Resume Next set wbk = Workbooks.Open ("C:\path\wb1"), Password:="xxx" on error goto 0 if wbk is nothing then msgbox "C:\path\wb1 was not found..." else Sheets("sheet1").Cells.Copy Windows("wb2.xls").Activate Sheets("data").Range("A1").PasteSpecial Paste:=xlValues wbk .Close True Kill ("C:\path\WB1.xls") end if -- HTH... Jim Thomlinson "Ronbo" wrote: When I open Workbook2 (WB2) I need for it to check and see if Workbook1 (WB1) exists and if so, I need for data to be updated. I have the following code; On Error Resume Next Workbooks.Open ("C:\path\wb1"), Password:="xxx" Sheets("sheet1").Select Cells.Select Selection.Copy Windows("wb2.xls").Activate Sheets("data").Select Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Workbooks("wb1.xls").Close True Kill ("C:\path\WB1.xls") On Error goto 0 It works fine if WB1 exists. However if WB1 does not exist it removes all the data in WB2 Sheets("data"). I need for it to somehow quit all together when it errors on "Workbooks.Open ("C:\path\wb1"), Password:="xxx"" when the workbook does not exist. Any help is very much appreciated. |
quit on error
Jim:
Thanks for your help and help in the past. This looks like what I am looking for but I am getting a compile error on line "set wbk = Workbooks.Open ("C:\path\wb1"), Password:="xxx"" Expected: end of statement. What am I doing wrong? Again, thanks for your time and help. "Jim Thomlinson" wrote: Give this a try... Dim wbk as workbook On Error Resume Next set wbk = Workbooks.Open ("C:\path\wb1"), Password:="xxx" on error goto 0 if wbk is nothing then msgbox "C:\path\wb1 was not found..." else Sheets("sheet1").Cells.Copy Windows("wb2.xls").Activate Sheets("data").Range("A1").PasteSpecial Paste:=xlValues wbk .Close True Kill ("C:\path\WB1.xls") end if -- HTH... Jim Thomlinson "Ronbo" wrote: When I open Workbook2 (WB2) I need for it to check and see if Workbook1 (WB1) exists and if so, I need for data to be updated. I have the following code; On Error Resume Next Workbooks.Open ("C:\path\wb1"), Password:="xxx" Sheets("sheet1").Select Cells.Select Selection.Copy Windows("wb2.xls").Activate Sheets("data").Select Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Workbooks("wb1.xls").Close True Kill ("C:\path\WB1.xls") On Error goto 0 It works fine if WB1 exists. However if WB1 does not exist it removes all the data in WB2 Sheets("data"). I need for it to somehow quit all together when it errors on "Workbooks.Open ("C:\path\wb1"), Password:="xxx"" when the workbook does not exist. Any help is very much appreciated. |
quit on error
Try this...
set wbk = Workbooks.Open ("C:\path\wb1", Password:="xxx") -- HTH... Jim Thomlinson "Ronbo" wrote: Jim: Thanks for your help and help in the past. This looks like what I am looking for but I am getting a compile error on line "set wbk = Workbooks.Open ("C:\path\wb1"), Password:="xxx"" Expected: end of statement. What am I doing wrong? Again, thanks for your time and help. "Jim Thomlinson" wrote: Give this a try... Dim wbk as workbook On Error Resume Next set wbk = Workbooks.Open ("C:\path\wb1"), Password:="xxx" on error goto 0 if wbk is nothing then msgbox "C:\path\wb1 was not found..." else Sheets("sheet1").Cells.Copy Windows("wb2.xls").Activate Sheets("data").Range("A1").PasteSpecial Paste:=xlValues wbk .Close True Kill ("C:\path\WB1.xls") end if -- HTH... Jim Thomlinson "Ronbo" wrote: When I open Workbook2 (WB2) I need for it to check and see if Workbook1 (WB1) exists and if so, I need for data to be updated. I have the following code; On Error Resume Next Workbooks.Open ("C:\path\wb1"), Password:="xxx" Sheets("sheet1").Select Cells.Select Selection.Copy Windows("wb2.xls").Activate Sheets("data").Select Range("A1").Select Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Workbooks("wb1.xls").Close True Kill ("C:\path\WB1.xls") On Error goto 0 It works fine if WB1 exists. However if WB1 does not exist it removes all the data in WB2 Sheets("data"). I need for it to somehow quit all together when it errors on "Workbooks.Open ("C:\path\wb1"), Password:="xxx"" when the workbook does not exist. Any help is very much appreciated. |
All times are GMT +1. The time now is 03:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com