Error Trap Help

Printable View

Apr 26th, 2011, 03:15 PM
Macroquest

Error Trap Help
I have a number of documents in a folder. They are in a variety of formats. I have written a macro to loop through them one at a time, modify the format and save the file to a "NewFiles" folder. Occasionally a file has a problem and halts the code due to an error. I want to place them in a folder called "BadFiles", but I cant seem to get it to work. Any help is appreciated!

Here is the code thus far:
1. Sub LoopDirectory()
2. ' This code will allow the user to browse to a folder where MS Word documents reside.
3. ' When executed to will loop through all documents
4. ' Then run the referenced subs and save the modified files in a subdirectory called NewFiles.
6. Application.FileDialog(msoFileDialogFolderPicker).Show
7. vDirectory = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
8. On Error GoTo Err:
9. If Len(Dir(vDirectory & "\" & "NewFiles", vbDirectory)) = 0 Then
10. MkDir vDirectory & "\" & "NewFiles"
11. MkDir vDirectory & "\" & "BadFiles"
12. End If
13. vFile = Dir(vDirectory & "\" & "*.*")
14. Do While vFile <> ""
15. Documents.Open FileName:=vDirectory & "\" & vFile
16. RemoveHeadAndFoot 'Call sub from below
17. ActiveDocument.SaveAs (vDirectory & "\" & "NewFiles" & "\" & ActiveDocument.Name)
18. ActiveDocument.Close
19. vFile = Dir
20. Loop
21. End Sub
22. Err:
23. If Err.Number <> 0 Then
24. msg "Error: " & Err.Number
25. Err.Clear
26. ActiveDocument.SaveAs (vDirectory & "\" & "BadFiles" & "\" & ActiveDocument.Name)
27. ActiveDocument.Close
28. End If
29. End sub
30. Sub RemoveHeadAndFoot()
31. ' This code will remove existing Headers and Footers in all documents in the folder.
32. Dim oSec As Section
33. Dim oHead As HeaderFooter
34. Dim oFoot As HeaderFooter
35. For Each oSec In ActiveDocument.Sections
36. For Each oHead In oSec.Headers
37. If oHead.Exists Then oHead.Range.Delete
38. Next oHead
39. For Each oFoot In oSec.Footers
40. If oFoot.Exists Then oFoot.Range.Delete
41. Next oFoot
42. Next oSec
43. End Sub
Apr 26th, 2011, 04:41 PM
westconn1

Re: Error Trap Help
Quote:

I want to place them in a folder called "BadFiles", but I cant seem to get it to work. Any help is appreciated!

so what happens?
error, nothing or wrong result?
if you want to continue with the rest of the files you need a resume next statement, probably in place of activedocument.close in error handler, depending where the error occurs as to where it will resume, you need to run some of the code conditional on if there was an error, something like
vb Code:
```
on error goto err
if waserror then
    saveas in badfiles
   else saveas in newfiles
end if
waserror = false
close doc
vfile =  dir
exit sub
err:
waserror = true
resume next
end sub
```
of course none of this is real code, just a guide
Apr 27th, 2011, 06:24 PM
Macroquest

Re: Error Trap Help
Well, the following code gets farther, but when it throws the error and gets the the statement (Line 39) ActiveDocument.SaveAs (vDirectory & "\" & "BadFiles" & "\" & ActiveDocument.Name), then it throws error 5152 - Not a valid file name.
1. Sub LoopDirectory()
2. ' This code will allow the user to browse to a folder where MS Word documents reside.
3. ' When executed to will loop through all documents
4. ' Then run the referenced subs and save the modified files in a subdirectory called NewFiles.
6. Application.FileDialog(msoFileDialogFolderPicker).Show
7. vDirectory = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
8. If Len(Dir(vDirectory & "\" & "NewFiles", vbDirectory)) = 0 Then
9. MkDir vDirectory & "\" & "NewFiles"
10. MkDir vDirectory & "\" & "BadFiles"
11. End If
12. vfile = Dir(vDirectory & "\" & "*.*")
13. Do While vfile <> ""
14. Documents.Open FileName:=vDirectory & "\" & vfile
15. RemoveHeadAndFoot
16. ActiveDocument.SaveAs (vDirectory & "\" & "NewFiles" & "\" & ActiveDocument.Name)
17. ActiveDocument.Close
18. vfile = Dir
19. Loop
20. End Sub
21. Sub RemoveHeadAndFoot()
22. ' This code will remove existing Headers and Footers in all documents in the folder.
23. Dim oSec As Section
24. Dim oHead As HeaderFooter
25. Dim oFoot As HeaderFooter
26. 'ActiveDocument.Unprotect Password:=""
27. On Error GoTo ErrHandler
28. For Each oSec In ActiveDocument.Sections
29. For Each oHead In oSec.Headers
30. If oHead.Exists Then oHead.Range.Delete
31. Next oHead
32. For Each oFoot In oSec.Footers
33. If oFoot.Exists Then oFoot.Range.Delete
34. Next oFoot
35. Next oSec
36. Exit Sub
37. ErrHandler:
38. If Err.Number <> 0 Then
39. ActiveDocument.SaveAs (vDirectory & "\" & "BadFiles" & "\" & ActiveDocument.Name)
40. End If
41. ActiveDocument.Close
42. Resume Next
43. End Sub
Apr 27th, 2011, 07:42 PM
nO_OnE

Re: Error Trap Help

Are you running this from a VBA Editor, or from a version of VB?

If you are using VBA I would put the following code before line #39:

Code:

Debug.print vDirectory & " - " & Activedocument.name

This will print out the strings to the immediate window so you can see what they are. The last one, after the debugger gives you the error, will be the one giving you the problems.

I would assume it is because you are trying to open the file within the program (judging by your code it looks like word). Then you are trying to save it from within the program. If you open up a file within the program that does not have a normal extention it may give you an error when you try to save it from the program, if it doesn't support saving in that format...
Apr 27th, 2011, 10:14 PM
westconn1

Re: Error Trap Help
is the error happening because the non-error saveAs also have the same error?

the error handling logic is still not correct and can cause problems unless you test what type of error you are getting
eg if error in file open, different problem to saveAs
if on error you close workbook, resume next line of code may refer to closed workbook, then another error occurs, saveas in error handler will then error as the workbook is closed,

you need to step through code line by line to find where errors can occur, then only do specific stuff in error handler depending on error

for testing purposes
vb Code:
```
msgbox err.number & vbnewline & err.description
'also
vDirectory = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
msgbox vDirectory
```
Apr 28th, 2011, 08:20 PM
nO_OnE

Re: Error Trap Help

True, but if he is trying to save a document that is in an unsupported format, it doesn't really matter what error handling he uses, he will never be able to use the save as method if he wants to keep it the same format...
He will have to use another method to move the file.
Apr 28th, 2011, 08:51 PM
Macroquest

Re: Error Trap Help

All files are .doc I have found that some of the older files are password protected and that was throwing the error. The suggestions made were useful I can now get the troublesome files to save to the "BadFiles" folder, but they are also being saved to the "NewFiles" folder so I'm trying to see where the code is being directed after the ActiveDocument.SaveAs command.

Have not had much time today to work on it.
Apr 29th, 2011, 01:50 AM
Doogle

Re: Error Trap Help

Quote:

Originally Posted by Macroquest

but they are also being saved to the "NewFiles" folder

That's because of the 'Resume Next' - the Subroutine will complete and the

Code:

ActiveDocument.SaveAs (vDirectory & "\" & "NewFiles" & "\" & ActiveDocument.Name)

statement in the calling routine will be executed.

Notwithstanding the earlier comments about testing for specific errors, it might be better to convert RemoveHeadAndFoot to a Function which returns 0 or 1 - ie 0 if there has been no error and 1 if there has. You could then test the result in the calling routine, and if it's 0 then save to the NewFiles Folder.

Code:

Sub LoopDirectory() ' This code will allow the user to browse to a folder where MS Word documents reside. ' When executed to will loop through all documents ' Then run the referenced subs and save the modified files in a subdirectory called NewFiles. Application.FileDialog(msoFileDialogFolderPicker).Show vDirectory = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) If Len(Dir(vDirectory & "\" & "NewFiles", vbDirectory)) = 0 Then MkDir vDirectory & "\" & "NewFiles" MkDir vDirectory & "\" & "BadFiles" End If vfile = Dir(vDirectory & "\" & "*.*") Do While vfile <> "" Documents.Open FileName:=vDirectory & "\" & vfile ' ' If the file isn't saved to the BadFiles Folder by RemoveHeadAndFoot ' then save it to the NewFiles Folder ' If RemoveHeadAndFoot = 0 Then ActiveDocument.SaveAs (vDirectory & "\" & "NewFiles" & "\" & ActiveDocument.Name) ActiveDocument.Close End If vfile = Dir Loop End Sub Function RemoveHeadAndFoot() ' This code will remove existing Headers and Footers in all documents in the folder. Dim oSec As Section Dim oHead As HeaderFooter Dim oFoot As HeaderFooter RemoveHeadAndFoot = 0 'ActiveDocument.Unprotect Password:="" On Error GoTo ErrHandler For Each oSec In ActiveDocument.Sections For Each oHead In oSec.Headers If oHead.Exists Then oHead.Range.Delete Next oHead For Each oFoot In oSec.Footers If oFoot.Exists Then oFoot.Range.Delete Next oFoot Next oSec Exit Function ErrHandler: ActiveDocument.SaveAs (vDirectory & "\" & "BadFiles" & "\" & ActiveDocument.Name) ActiveDocument.Close ' ' Signal that the file has been saved ' RemoveHeadAndFoot = 1 End Function
May 5th, 2011, 10:11 AM
Macroquest

Re: Error Trap Help

Thank you for the reply!

I had to make one minor change - adding "vDirectory = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)" as the first line of the ErrHandler (see red text below). It now runs as intended.

I'm very new to programming and unfamiliar with functions, so I have one more question; I originally had RemoveHeadAndFoot as a Sub, not a function. I planned to call a series of Subs for various routines I needed to perform. I planned to comment them out if they were not needed (See blue text below).

Is there an easy way to incorporate them as subs to be run within the existing function or else define them as functions?

Code:

Sub LoopDirectory() ' This code will allow the user to browse to a folder where MS Word documents reside. ' When executed to will loop through all documents ' Then run the referenced subs and save the modified files in a subdirectory called NewFiles. Application.FileDialog(msoFileDialogFolderPicker).Show vDirectory = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) If Len(Dir(vDirectory & "\" & "NewFiles", vbDirectory)) = 0 Then MkDir vDirectory & "\" & "NewFiles" MkDir vDirectory & "\" & "BadFiles" End If vfile = Dir(vDirectory & "\" & "*.*") Do While vfile <> "" Documents.Open FileName:=vDirectory & "\" & vfile ' 'Sub1 'Sub2 ' If the file isn't saved to the BadFiles Folder by RemoveHeadAndFoot ' then save it to the NewFiles Folder ' If RemoveHeadAndFoota = 0 Then ActiveDocument.SaveAs (vDirectory & "\" & "NewFiles" & "\" & ActiveDocument.Name) ActiveDocument.Close End If vfile = Dir Loop End Sub Function RemoveHeadAndFoot() ' This code will remove existing Headers and Footers in all documents in the folder. Dim oSec As Section Dim oHead As HeaderFooter Dim oFoot As HeaderFooter RemoveHeadAndFoota = 0 On Error GoTo ErrHandler For Each oSec In ActiveDocument.Sections For Each oHead In oSec.Headers If oHead.Exists Then oHead.Range.Delete Next oHead For Each oFoot In oSec.Footers If oFoot.Exists Then oFoot.Range.Delete Next oFoot Next oSec Exit Function ErrHandler: vDirectory = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) ActiveDocument.SaveAs (vDirectory & "\" & "BadFiles" & "\" & ActiveDocument.Name) ActiveDocument.Close ' ' Signal that the file has been saved ' RemoveHeadAndFoota = 1 End Function
May 5th, 2011, 09:31 PM
westconn1

Re: Error Trap Help

you should just pass vdirectory to the function, rather than using the filedialog again