Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba advice how 2get a input box to quit by entering the word quit+ 4 a msgbox to disp | Excel Programming | |||
Quit | Excel Programming | |||
Excel97 not quit | Excel Programming |