Workbook Merging and Err Handling

Hi BARENTINEMATT,
Do you still need help on this one?
On the face of it your requirement sounds very easy to fulfil.

But I am having difficulty understanding you?
_1 ) Have you got some Typos here ?

Originally Posted by BARENTINEMATT

., wh..ile i was testing the code one of the files ("DetailReport(2)") was able to merge with the others. Upon further investigation the cause was not with the code but in fact when it was downloaded the file was somehow corrupted.....

_ 2 ) You have given a large code snippet, but not the complete code. ( unless you just missed the first Sub bit ). You have not said specifically where you want the extra error handling. I assume it is where the Files are opened.

I do not recognise that code unfortunately so it is a bit difficult coming in to look at it without a better description from you of what it does.
But it appears that if a Workbook cannot be opened, then currently the code then ignores trying to do anything with it and just goes on to the next Workbook. So again, you description of the problem is very confusing ?? You say “a file was able to merge , the cause of which was that it was corrupt, " and then what you say suggests it was not opened because it was corrupt”. So that is all nonsense or a typo ??

There is no point in me trying to modify the code , as it is not clear what you want.
For example , after getting the message with the File name that could not be opened, what should happen then?
_ Should the code continue or stop. Currently the code just ignores Files that cannot be opened and goes on to the next File.
If you wanted the code to continue, then you would have a very simple code line to add just to tell you that file X was not opened.
_ If the code should end after not opening a file then you modify the code to include an error handling code section, typically towards the end of the code. You then arrange for the code to go there on an Error. There your message would be given just before the code ends.


Both modifications are very simple. You need I think to be a bit clearer on describing the problem and what you want.

Alan

p.s.

Let me give some background info that may help you to solve the problem yourself.

On Error Resume Next
This is a user defined Error handler which overrides the default error handler. It actually suppresses an error situation being raised, and instructs the program to continue just after the line which would have raised an “Error Situation”. An “Error Situation” , sometimes called an Exception, or Error exception , or an Erected Exceptional VBA state, etc..etc.. is a situation in which VBA is “thinking” something is wrong. It behaves in such a situation differently. For example it ignores any further attempts by you to define a new Error Handler. It also ignores your attempts to “switch off” or “switch back” to the default error handler. In the case of using On Error Resume Next Error handler Statement, VBA has been prevented from being aroused to its Erected exceptional state of being. Because of this suppression, you will often see the Error Handler Statement On Error Resume Next being accompanied close by a further Error Handling Statement, On Error GoTo 0( One might use also an On Error GoTo Label user defined error handler instead here, assigning a different user defined error handler. - Again this is possible because VBA has been prevented from being aroused to its Erected exceptional state of being. )
On Error GoTo 0
This Error handler Statement “goes” nowhere. It “switches off” the current user defined or “switches back” to the default error handler. It would normally only work if no Error had been encountered. But in the case of using On Error Resume Next it will work. This is because, as noted, On Error Resume Next has surprised the Arousal
On Error GoTo Label
This Error handling Statement does “go somewhere!”. It “goes” to the code section labeled Label: ( One can also replace the label with a code line, if you have that code line in your code ). In this case there is no suppression of the aroused Erected Exceptional VBA state. VBA is now “thinking” something is wrong. It behaves in such a situation differently. For example it ignores any further attempts by you to define a new Error Handler. It also ignores your attempts to “switch off” or “switch back” to the default error handler. However, this error handler allows the code to continue, ( rather than stopping as is the case by the default error handler ) , at the point the code “goes to”. Typically things are done there that should be done on the case of an error.
Object Err
There is an Object Err available to us. This has a couple of Methods and some Properties. These Properties are strings of information about the last type of error that was encountered. So this can be used, for example, as an indication of if an error occurred after using On Error Resume Next . Note however, If one is wanting to utilize this information in the case of a On Error Resume Next, then one should do that before an On Error GoTo Label or a On Error GoTo 0 . This is because, these two Error Handling Statements Clear that information. ( This clearing can also be done by a code line of Err.Clear )









http://excelmatters.com/2015/03/17/on-error-wtf/
https://app.box.com/s/8zkhjcmbxrqnlnexqpktuy41clgqm4zo

Hi BARENTINEMATT,
It is probably a good idea to get in the habit of
_1 ) giving the URL link to any site that you use, in this case the Ron de Bruin site You got the code
From
-2) post a full working code, a shortened version if possible. In this case probably the full code you are using would be OK..
_....................

Originally Posted by BARENTINEMATT

.my question is how can i word the error handling to exit sub and display a msgbox with the filename that failed instead of the current resume next method. ...

That is fairly easy, I tend to put that sort of error handling as standard on most of my codes..

Originally Posted by Doc.AElstein

.....
_ If the code should end after not opening a file then you modify the code to include an error handling code section, typically towards the end of the code. You then arrange for the code to go there on an Error. There your message would be given just before the code ends.....

_....
_.....
_._____________________________
I am not a Profi myself and prefer to have some test data ( reduced in size where possible ) so I can test and run any code I give. But for this error handler part I think I have done it often enough so I can work “blind” and it should work..
-..

So
here we go
_1 ) Change this bit of the code so that on encountering an Error, although an Exceptional State is still Erected, the code does not stop but is sent somewhere ( in this case to an error handling section towards the end of the code. ( The Error handling section can go anywhere, but as you want to stop shortly after going there it is convenient to put it there) ) :

Please Login or Register  to view this content.

_...........................


_2) change the end of your code Thus:

Please Login or Register  to view this content.

_.. I could not test that, but it should do what you want

_...........................
_._________________________-

As for the rest,

Originally Posted by BARENTINEMATT

....
I may need to post a new thread but additionally i would like to edit it somehow to copy the header of just the first workbook but not rest i initially though it would be pretty simple but i cant seem figure out a good starting piont.....

.. it is probably Ok to post here as it fits the Thread Title, or you could start a new Thread, as you choose..
As before , it is probably very easy to do. It is just a case of explaining carefully exactly what you want. Ideally some test data is needed, as the “Before”, and then some Hand filled in output as the “After”, showing exactly as you want the output to look based on your supplied sample input data.

Alan

P.s.
Some notes here on how to get info across to us
http://www.excelforum.com/hello-intr...ml#post4478582

If you can show clearly what you want in a post in this thread ( the one we are in now ) , and if no one else does it for you I will do it for you tomorrow