Results 1 to 10 of 10

Thread: Error Trap Help

  1. #1

    Thread Starter
    New Member
    Join Date
    Apr 2011
    Posts
    13

    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.

    5. Application.FileDialog(msoFileDialogFolderPicker).Show
    6. vDirectory = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
    7. On Error GoTo Err:
    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 'Call sub from below
    16. ActiveDocument.SaveAs (vDirectory & "\" & "NewFiles" & "\" & ActiveDocument.Name)
    17. ActiveDocument.Close
    18. vFile = Dir
    19. Loop
    20. End Sub
    21. Err:
    22. If Err.Number <> 0 Then
    23. msg "Error: " & Err.Number
    24. Err.Clear
    25. ActiveDocument.SaveAs (vDirectory & "\" & "BadFiles" & "\" & ActiveDocument.Name)
    26. ActiveDocument.Close
    27. End If
    28. End sub

    29. Sub RemoveHeadAndFoot()
    30. ' This code will remove existing Headers and Footers in all documents in the folder.
    31. Dim oSec As Section
    32. Dim oHead As HeaderFooter
    33. Dim oFoot As HeaderFooter
    34. For Each oSec In ActiveDocument.Sections
    35. For Each oHead In oSec.Headers
    36. If oHead.Exists Then oHead.Range.Delete
    37. Next oHead
    38. For Each oFoot In oSec.Footers
    39. If oFoot.Exists Then oFoot.Range.Delete
    40. Next oFoot
    41. Next oSec
    42. End Sub

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Error Trap Help

    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:
    1. on error goto err
    2. if waserror then
    3.     saveas in badfiles
    4.    else saveas in newfiles
    5. end if
    6. waserror = false
    7. close doc
    8. vfile =  dir
    9. exit sub
    10. err:
    11. waserror = true
    12. resume next
    13. end sub
    of course none of this is real code, just a guide
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Apr 2011
    Posts
    13

    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.

    5. Application.FileDialog(msoFileDialogFolderPicker).Show
    6. vDirectory = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
    7. If Len(Dir(vDirectory & "\" & "NewFiles", vbDirectory)) = 0 Then
    8. MkDir vDirectory & "\" & "NewFiles"
    9. MkDir vDirectory & "\" & "BadFiles"
    10. End If
    11. vfile = Dir(vDirectory & "\" & "*.*")
    12. Do While vfile <> ""
    13. Documents.Open FileName:=vDirectory & "\" & vfile
    14. RemoveHeadAndFoot
    15. ActiveDocument.SaveAs (vDirectory & "\" & "NewFiles" & "\" & ActiveDocument.Name)
    16. ActiveDocument.Close
    17. vfile = Dir
    18. Loop
    19. End Sub
    20. Sub RemoveHeadAndFoot()
    21. ' This code will remove existing Headers and Footers in all documents in the folder.
    22. Dim oSec As Section
    23. Dim oHead As HeaderFooter
    24. Dim oFoot As HeaderFooter
    25. 'ActiveDocument.Unprotect Password:=""
    26. On Error GoTo ErrHandler
    27. For Each oSec In ActiveDocument.Sections
    28. For Each oHead In oSec.Headers
    29. If oHead.Exists Then oHead.Range.Delete
    30. Next oHead
    31. For Each oFoot In oSec.Footers
    32. If oFoot.Exists Then oFoot.Range.Delete
    33. Next oFoot
    34. Next oSec
    35. Exit Sub
    36. ErrHandler:
    37. If Err.Number <> 0 Then
    38. ActiveDocument.SaveAs (vDirectory & "\" & "BadFiles" & "\" & ActiveDocument.Name)
    39. End If
    40. ActiveDocument.Close
    41. Resume Next
    42. End Sub

  4. #4
    Hyperactive Member
    Join Date
    Oct 2010
    Location
    Indiana
    Posts
    457

    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...

  5. #5
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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:
    1. msgbox err.number & vbnewline & err.description
    2. 'also
    3. vDirectory = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
    4. msgbox vDirectory
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  6. #6
    Hyperactive Member
    Join Date
    Oct 2010
    Location
    Indiana
    Posts
    457

    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.

  7. #7

    Thread Starter
    New Member
    Join Date
    Apr 2011
    Posts
    13

    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.

  8. #8
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: Error Trap Help

    Quote Originally Posted by Macroquest View Post
    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
    Last edited by Doogle; Apr 29th, 2011 at 02:15 AM.

  9. #9

    Thread Starter
    New Member
    Join Date
    Apr 2011
    Posts
    13

    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
    Last edited by Hack; May 5th, 2011 at 10:51 AM. Reason: Added Code Tags

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Error Trap Help

    you should just pass vdirectory to the function, rather than using the filedialog again
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width