-
Apr 26th, 2011, 03:15 PM
#1
Thread Starter
New Member
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:
- 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)
- On Error GoTo Err:
- 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
- RemoveHeadAndFoot 'Call sub from below
- ActiveDocument.SaveAs (vDirectory & "\" & "NewFiles" & "\" & ActiveDocument.Name)
- ActiveDocument.Close
- vFile = Dir
- Loop
- End Sub
- Err:
- If Err.Number <> 0 Then
- msg "Error: " & Err.Number
- Err.Clear
- ActiveDocument.SaveAs (vDirectory & "\" & "BadFiles" & "\" & ActiveDocument.Name)
- ActiveDocument.Close
- End If
- End sub
- Sub 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
- 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
- End Sub
-
Apr 26th, 2011, 04:41 PM
#2
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:
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
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
-
Apr 27th, 2011, 06:24 PM
#3
Thread Starter
New Member
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.
- 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
- RemoveHeadAndFoot
- ActiveDocument.SaveAs (vDirectory & "\" & "NewFiles" & "\" & ActiveDocument.Name)
- ActiveDocument.Close
- vfile = Dir
- Loop
- End Sub
- Sub 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
- '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 Sub
- ErrHandler:
- If Err.Number <> 0 Then
- ActiveDocument.SaveAs (vDirectory & "\" & "BadFiles" & "\" & ActiveDocument.Name)
- End If
- ActiveDocument.Close
- Resume Next
- End Sub
-
Apr 27th, 2011, 07:42 PM
#4
Hyperactive Member
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
#5
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
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
-
Apr 28th, 2011, 08:20 PM
#6
Hyperactive Member
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
#7
Thread Starter
New Member
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
#8
Re: Error Trap Help
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
Last edited by Doogle; Apr 29th, 2011 at 02:15 AM.
-
May 5th, 2011, 10:11 AM
#9
Thread Starter
New Member
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
-
May 5th, 2011, 09:31 PM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|