The VBA Dir function is a powerful, built-in function used for interacting with the file system. Here's a concise summary of its main functions:
Finding the First Matching File/Folder:
- When called with a pathname argument (which can include wildcards like * for multiple characters and ? for single characters), Dir returns the name of the first file or folder that matches the specified pattern.
- Example: Dir("C:\MyFolder\*.txt") will return the name of the first .txt file found in C:\MyFolder.
Iterating Through Matching Files/Folders:
- After the initial call with a pathname, subsequent calls to Dir without any arguments (Dir) will return the name of the next file or folder that matches the original pattern and path.
- This allows you to easily loop through all files in a directory that meet certain criteria, as demonstrated in your improved code.
Checking for Existence:
- If Dir does not find any file or folder matching the specified pattern, it returns a zero-length string (""). This makes it a common and efficient way to check if a file or folder exists.
- Example: If Dir("C:\MyFile.txt") <> "" Then MsgBox "File exists"
Specifying Attributes:
The optional attributes argument allows you to filter results based on file attributes (e.g., hidden files, system files, directories). You can combine these attributes using addition.
Common attributes:
- vbNormal (0): Normal files (default if omitted)
- vbReadOnly (1): Read-only files
- vbHidden (2): Hidden files
- vbSystem (4): System files
- vbVolume (8): Volume label (if specified, others are ignored)
- vbDirectory (16): Directories or folders
Example: Dir("C:\*", vbDirectory) will return the name of the first directory in C:\.
In essence, Dir is your go-to function in VBA for:
- Discovering files and folders based on patterns.
- Looping through collections of files/folders.
- Verifying the existence of specific files or folders.
VBA Code to process multiple Word documents:
The following code processes each Word document which is inside D:\Words folder. The spelling and grammar errors are disabled and in the bottom RHS pagination is done which is of style: Page X of Y. X stands for current page number and Y for total page count in the document.
Sub ProcessWordDocuments()
Dim sPath As String
Dim sFile As String
Dim doc As Word.Document
sPath = "D:\Words\"
If Dir(sPath, vbDirectory) = "" Then
MsgBox "The specified folder does not exist: " & sPath, vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
sFile = Dir(sPath & "*.doc?")
Do While sFile <> ""
On Error GoTo ErrorHandler
Set doc = Documents.Open(FileName:=sPath & sFile)
doc.ShowGrammaticalErrors = False
doc.ShowSpellingErrors = False
Call InsertCustomPageNumbers(doc)
doc.Save
doc.Close SaveChanges:=False
sFile = Dir
Loop
MsgBox "All Word documents in '" & sPath & "' have been processed.", vbInformation
GoTo CleanExit
ErrorHandler:
MsgBox "An error occurred while processing '" & sFile & "': " & Err.Description, vbCritical
If Not doc Is Nothing Then
If doc.Saved = False Then
doc.Close SaveChanges:=wdDoNotSaveChanges
Else
doc.Close
End If
End If
Resume Next
CleanExit:
Application.ScreenUpdating = True
End Sub
Private Sub InsertCustomPageNumbers(doc As Word.Document)
Dim sec As Section
Dim footerRange As Word.Range
For Each sec In doc.Sections
With sec.Footers(wdHeaderFooterPrimary)
.Range.Delete
Set footerRange = .Range
footerRange.Collapse Direction:=wdCollapseEnd ' Start at the end
footerRange.ParagraphFormat.Alignment = wdAlignParagraphRight
' Insert total pages field (Y)
footerRange.Fields.Add Range:=footerRange, Type:=wdFieldNumPages
footerRange.Collapse Direction:=wdCollapseStart ' Collapse to before the just inserted field
' Insert " of "
footerRange.InsertBefore " of "
footerRange.Collapse Direction:=wdCollapseStart
' Insert current page number field (X)
footerRange.Fields.Add Range:=footerRange, Type:=wdFieldPage
footerRange.Collapse Direction:=wdCollapseStart
' Insert "Page "
footerRange.InsertBefore "Page "
With .Range.Font
.Bold = True
.Name = "Calibri"
.Size = 11
End With
End With
Next sec
doc.Fields.Update
End Sub
VBA Code with detailed comments
Sub ProcessWordDocuments()
' Main subroutine to iterate through Word documents in a specified folder
' and apply formatting (hide errors, insert custom page numbers).
Dim sPath As String ' Declares a string variable to hold the folder path.
Dim sFile As String ' Declares a string variable to hold the current file name.
Dim doc As Word.Document ' Declares an object variable to represent an opened Word document.
' --- Configuration ---
' Set the path to the folder containing your Word documents.
' Ensure a trailing backslash for correct path concatenation.
sPath = "D:\Words\"
' --- Folder Existence Check ---
' Verify if the specified folder exists before proceeding.
If Dir(sPath, vbDirectory) = "" Then
MsgBox "The specified folder does not exist: " & sPath, vbExclamation
Exit Sub ' Exit the subroutine if the folder is not found.
End If
' --- Performance Optimization ---
' Turn off screen updating to speed up the process and prevent screen flickering.
' This makes the macro run much faster, especially with many documents.
Application.ScreenUpdating = False
' --- File Iteration Loop ---
' Find the first Word document in the specified folder.
' "*.doc?" matches both .doc (Word 97-2003) and .docx (Word 2007+) files.
sFile = Dir(sPath & "*.doc?")
' Loop through all found Word documents until no more files are found (sFile becomes "").
Do While sFile <> ""
' --- Error Handling for Current Document ---
' Enable error handling for the current iteration. If an error occurs,
' the code jumps to the 'ErrorHandler' label.
On Error GoTo ErrorHandler
' --- Open Document ---
' Open the current document.
Set doc = Documents.Open(FileName:=sPath & sFile)
' --- Apply Formatting: Hide Spelling and Grammatical Errors ---
' Set document properties to hide wavy underlines for errors.
doc.ShowGrammaticalErrors = False
doc.ShowSpellingErrors = False
' --- Call Helper Subroutine: Insert Custom Page Numbers ---
' Call the private helper subroutine to insert the desired page numbering format.
Call InsertCustomPageNumbers(doc)
' --- Save and Close Document ---
' Save changes made to the document.
doc.Save
' Close the document without prompting to save changes again (as it was just saved).
doc.Close SaveChanges:=False
' --- Get Next File ---
' Get the next Word document in the folder.
sFile = Dir ' No arguments mean it continues from the previous Dir search.
Loop
' --- Completion Message ---
' Display a message box indicating that all documents have been processed successfully.
MsgBox "All Word documents in '" & sPath & "' have been processed.", vbInformation
GoTo CleanExit ' Jump to the clean exit section.
' --- Error Handler ---
ErrorHandler:
' Display an error message if an error occurs during processing a document.
MsgBox "An error occurred while processing '" & sFile & "': " & Err.Description, vbCritical
' Attempt to gracefully close the document if it was opened.
If Not doc Is Nothing Then
' If the document had unsaved changes before the error, close without saving them.
If doc.Saved = False Then
doc.Close SaveChanges:=wdDoNotSaveChanges
Else
' If it was already saved or no changes were made, just close it.
doc.Close
End If
End If
' Resume execution at the line immediately following the error.
' This allows the loop to continue processing other files.
Resume Next
' --- Clean Exit ---
CleanExit:
' Turn screen updating back on after the macro finishes.
Application.ScreenUpdating = True
End Sub
Private Sub InsertCustomPageNumbers(doc As Word.Document)
' This private helper subroutine inserts custom page numbers in the format "Page X of Y"
' into the primary footer of each section in the given document.
Dim sec As Section ' Declares an object variable for each section in the document.
Dim footerRange As Word.Range ' Declares a Range object to manipulate footer content.
' Loop through each section of the document.
' Documents can have multiple sections, each with its own headers/footers.
For Each sec In doc.Sections
' Work with the primary footer of the current section.
With sec.Footers(wdHeaderFooterPrimary)
' --- Clear Existing Footer Content ---
' Delete all existing content within the footer range to ensure a clean overwrite.
.Range.Delete
' --- Prepare Footer Range for Insertion ---
' Get a fresh range object representing the (now empty) footer.
Set footerRange = .Range
' Collapse the range to a single insertion point at the very end of the footer.
' This is crucial for building the content backwards from right to left.
footerRange.Collapse Direction:=wdCollapseEnd
' Align the paragraph containing the page number to the right.
footerRange.ParagraphFormat.Alignment = wdAlignParagraphRight
' --- Insert Total Pages Field (Y) ---
' Insert the field that displays the total number of pages in the document.
footerRange.Fields.Add Range:=footerRange, Type:=wdFieldNumPages
' Collapse the range to a single point *before* the just-inserted field.
' This prepares for inserting the " of " text to its left.
footerRange.Collapse Direction:=wdCollapseStart
' --- Insert " of " Text ---
' Insert the literal text " of " to the left of the total pages field.
footerRange.InsertBefore " of "
' Collapse the range to a single point *before* the just-inserted " of " text.
' This prepares for inserting the current page number.
footerRange.Collapse Direction:=wdCollapseStart
' --- Insert Current Page Number Field (X) ---
' Insert the field that displays the current page number.
footerRange.Fields.Add Range:=footerRange, Type:=wdFieldPage
' Collapse the range to a single point *before* the just-inserted current page field.
' This prepares for inserting the "Page " text.
footerRange.Collapse Direction:=wdCollapseStart
' --- Insert "Page " Text ---
' Insert the literal text "Page " to the left of the current page number.
footerRange.InsertBefore "Page "
' No need to collapse after this, as it's the first element.
' --- Apply Font Formatting ---
' Apply bold, font name (Calibri), and font size (11) to the entire footer's content.
With .Range.Font
.Bold = True
.Name = "Calibri"
.Size = 11
End With
End With
Next sec
' --- Update Fields ---
' After all insertions in all sections, update all fields in the document.
' This ensures that the page numbers and total page counts are accurately calculated and displayed.
doc.Fields.Update
End Sub
No comments:
Post a Comment