Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > on error
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default on error
I'm using the following code to open up serveral files and grab information
to post to a worksheet. If the file does not exist in the directory, my on
error code will work for the first file but shows me the error on any
subsequent issue.

thanks

On Error GoTo error

Range("b2").Select
For Each rw In Range("block1")
ChDir rw.Offset(0, 1)
x = rw
If rw = 0 Then GoTo endmacro
Set wkb = Workbooks.Open(rw, Notify:=False)
Application.Goto Reference:="interestinput"
Selection.Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows(x).Activate
Range("b6").Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Range("e2").End(xlDown).Offset(0, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("b2").End(xlDown).Offset(1, 0).Select
Application.CutCopyMode = False
wkb.Close savechanges:=False

error:
Next rw
endmacro:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("a2").Select
End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default on error
Once you have entered an error ahndler, you are in it until you "Resume" your
normal execution with the "Resume" key word. Your code never resumes normal
exectution. This means that after the first error you are now in an error
handler for the remainder of the exectution. Once in an error handler any new
errors will be unhandled and just pop up on you... From reading your code it
looks like you are opening workbooks (directory in one column file name in
the next) and copying data to your host workbook (let me know if I am off the
mark here). If so then try this code...


For Each rw In Range("block1")
x = rw
If rw = 0 Then GoTo endmacro
on error resume next
Set wkb = Workbooks.Open(rw.Offset(0, 1).value & "\" & _
rw.value, Notify:=False)
if not wbk is nothing then
Application.Goto Reference:="interestinput"
Selection.Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues Windows(x).Activate
Range("b6").Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Range("e2").End(xlDown).Offset(0, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("b2").End(xlDown).Offset(1, 0).Select
Application.CutCopyMode = False
wkb.Close savechanges:=False
end if
Next rw
endmacro:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("a2").Select
End Sub

--
HTH...

Jim Thomlinson


"Linus" wrote:

I'm using the following code to open up serveral files and grab information
to post to a worksheet. If the file does not exist in the directory, my on
error code will work for the first file but shows me the error on any
subsequent issue.

thanks

On Error GoTo error

Range("b2").Select
For Each rw In Range("block1")
ChDir rw.Offset(0, 1)
x = rw
If rw = 0 Then GoTo endmacro
Set wkb = Workbooks.Open(rw, Notify:=False)
Application.Goto Reference:="interestinput"
Selection.Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows(x).Activate
Range("b6").Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Range("e2").End(xlDown).Offset(0, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("b2").End(xlDown).Offset(1, 0).Select
Application.CutCopyMode = False
wkb.Close savechanges:=False

error:
Next rw
endmacro:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("a2").Select
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 31
Default on error
I had the same problem but R B Smissaert solved my problem. Perhaps you
could use his solution?

Link: http://www.excelforum.com/showthread.php?t=550766


"Linus" <u23074@uwe wrote in message news:61d412e214333@uwe...
I'm using the following code to open up serveral files and grab
information
to post to a worksheet. If the file does not exist in the directory, my
on
error code will work for the first file but shows me the error on any
subsequent issue.

thanks

On Error GoTo error

Range("b2").Select
For Each rw In Range("block1")
ChDir rw.Offset(0, 1)
x = rw
If rw = 0 Then GoTo endmacro
Set wkb = Workbooks.Open(rw, Notify:=False)
Application.Goto Reference:="interestinput"
Selection.Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows(x).Activate
Range("b6").Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Range("e2").End(xlDown).Offset(0, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("b2").End(xlDown).Offset(1, 0).Select
Application.CutCopyMode = False
wkb.Close savechanges:=False

error:
Next rw
endmacro:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("a2").Select
End Sub


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default on error
Some more thoughts...

First, I'd stay away from using Error as a label in your error handling code.
There's the "on Error" statement and an error object that could cause confusion
for you--even if excel's vba can figure it out.

Second, you do a lot of selecting and copying and selecting and pasting.

You can do lots without selecting stuff.

I'm not sure if "US Bank Statement Interest multiple months.xls" is the workbook
with the code or not. And I'm not sure what sheet should get the info--you
relied on that activesheet in that workbook.

But this may give you another approach:

Option Explicit
Sub testme()

Dim myCell As Range
Dim x As String
Dim wkb As Workbook
Dim TestStr As String
Dim myFileName As String
Dim myRng As Range

For Each myCell In ThisWorkbook.Worksheets("sheet1").Range("block1"). Cells
myFileName = myCell.Offset(0, 1).Value & "\" & myCell.Value
TestStr = ""
On Error Resume Next
TestStr = Dir(myFileName)
On Error GoTo 0

If TestStr = "" Then
myCell.Offset(0, 2).Value = "File Not Found"
Else
myCell.Offset(0, 2).Value = "Processed"

Set wkb = Workbooks.Open(Filename:=myFileName)
Set myRng = wkb.Names("interestinput").RefersToRange

myRng.Copy _
Destination:= _
ThisWorkbook.Worksheets("whateversheet").Range("a1 ")
End If
Next myCell

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

(I included a \ in the concatenation string--not sure if that's part of your
data, though.)

Linus wrote:

I'm using the following code to open up serveral files and grab information
to post to a worksheet. If the file does not exist in the directory, my on
error code will work for the first file but shows me the error on any
subsequent issue.

thanks

On Error GoTo error

Range("b2").Select
For Each rw In Range("block1")
ChDir rw.Offset(0, 1)
x = rw
If rw = 0 Then GoTo endmacro
Set wkb = Workbooks.Open(rw, Notify:=False)
Application.Goto Reference:="interestinput"
Selection.Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Windows(x).Activate
Range("b6").Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Range("e2").End(xlDown).Offset(0, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("b2").End(xlDown).Offset(1, 0).Select
Application.CutCopyMode = False
wkb.Close savechanges:=False

error:
Next rw
endmacro:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("a2").Select
End Sub

--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default on error
everything works great except when the file does not exist the macro still
does the Windows(x) activate, range("b6").copy when in fact Windows(x) does
not exist. It copies range("b6") from my host workbook

thanks

Jim Thomlinson wrote:
Once you have entered an error ahndler, you are in it until you "Resume" your
normal execution with the "Resume" key word. Your code never resumes normal
exectution. This means that after the first error you are now in an error
handler for the remainder of the exectution. Once in an error handler any new
errors will be unhandled and just pop up on you... From reading your code it
looks like you are opening workbooks (directory in one column file name in
the next) and copying data to your host workbook (let me know if I am off the
mark here). If so then try this code...

For Each rw In Range("block1")
x = rw
If rw = 0 Then GoTo endmacro
on error resume next
Set wkb = Workbooks.Open(rw.Offset(0, 1).value & "\" & _
rw.value, Notify:=False)
if not wbk is nothing then
Application.Goto Reference:="interestinput"
Selection.Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues Windows(x).Activate
Range("b6").Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Range("e2").End(xlDown).Offset(0, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("b2").End(xlDown).Offset(1, 0).Select
Application.CutCopyMode = False
wkb.Close savechanges:=False
end if
Next rw
endmacro:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("a2").Select
End Sub

I'm using the following code to open up serveral files and grab information
to post to a worksheet. If the file does not exist in the directory, my on
[quoted text clipped - 42 lines]
Range("a2").Select
End Sub
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default on error
Try this...

For Each rw In Range("block1")
x = rw
If rw = 0 Then GoTo endmacro
on error resume next
Set wkb = Workbooks.Open(rw.Offset(0, 1).value & "\" & _
rw.value, Notify:=False)
on error goto 0
if not wbk is nothing then
Application.Goto Reference:="interestinput"
Selection.Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues
Windows(x).Activate
Range("b6").Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Range("e2").End(xlDown).Offset(0, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("b2").End(xlDown).Offset(1, 0).Select
Application.CutCopyMode = False
wkb.Close savechanges:=False
set wbk = nothing
end if
Next rw
endmacro:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("a2").Select
End Sub

--
HTH...

Jim Thomlinson


"Linus" wrote:

everything works great except when the file does not exist the macro still
does the Windows(x) activate, range("b6").copy when in fact Windows(x) does
not exist. It copies range("b6") from my host workbook

thanks

Jim Thomlinson wrote:
Once you have entered an error ahndler, you are in it until you "Resume" your
normal execution with the "Resume" key word. Your code never resumes normal
exectution. This means that after the first error you are now in an error
handler for the remainder of the exectution. Once in an error handler any new
errors will be unhandled and just pop up on you... From reading your code it
looks like you are opening workbooks (directory in one column file name in
the next) and copying data to your host workbook (let me know if I am off the
mark here). If so then try this code...

For Each rw In Range("block1")
x = rw
If rw = 0 Then GoTo endmacro
on error resume next
Set wkb = Workbooks.Open(rw.Offset(0, 1).value & "\" & _
rw.value, Notify:=False)
if not wbk is nothing then
Application.Goto Reference:="interestinput"
Selection.Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues Windows(x).Activate
Range("b6").Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Range("e2").End(xlDown).Offset(0, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("b2").End(xlDown).Offset(1, 0).Select
Application.CutCopyMode = False
wkb.Close savechanges:=False
end if
Next rw
endmacro:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("a2").Select
End Sub

I'm using the following code to open up serveral files and grab information
to post to a worksheet. If the file does not exist in the directory, my on
[quoted text clipped - 42 lines]
Range("a2").Select
End Sub

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default on error
I get the error message "object required" for line
If Not wbk Is Nothing Then
Jim Thomlinson wrote:
Try this...

For Each rw In Range("block1")
x = rw
If rw = 0 Then GoTo endmacro
on error resume next
Set wkb = Workbooks.Open(rw.Offset(0, 1).value & "\" & _
rw.value, Notify:=False)
on error goto 0
if not wbk is nothing then
Application.Goto Reference:="interestinput"
Selection.Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues
Windows(x).Activate
Range("b6").Copy
Windows("US Bank Statement Interest multiple months.xls").Activate
Range("e2").End(xlDown).Offset(0, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.End(xlUp).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
Range("b2").End(xlDown).Offset(1, 0).Select
Application.CutCopyMode = False
wkb.Close savechanges:=False
set wbk = nothing
end if
Next rw
endmacro:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("a2").Select
End Sub

everything works great except when the file does not exist the macro still
does the Windows(x) activate, range("b6").copy when in fact Windows(x) does
[quoted text clipped - 51 lines]
Range("a2").Select
End Sub

--
Message posted via http://www.officekb.com
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2
Default on error
please disregard this message, I transposed wkb with wbk.

thanks

Linus wrote:
I get the error message "object required" for line
If Not wbk Is Nothing Then
Try this...

[quoted text clipped - 37 lines]
Range("a2").Select
End Sub

--
Message posted via http://www.officekb.com
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting instances of found text (Excel error? Or user error?) S Davis Excel Worksheet Functions 5 September 12th 06 04:52 PM
Error Handling - On Error GoTo doesn't trap error successfully David Excel Programming 9 February 16th 06 05:59 PM
Form Err.Raise error not trapped by entry procedure error handler [email protected] Excel Programming 1 February 8th 06 10:19 AM
Automation Error, Unknown Error. Error value - 440 Neo[_2_] Excel Programming 0 May 29th 04 05:26 AM


All times are GMT +1. The time now is 01:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement