Hello forum members,
I recently came across two macros in my code that seem to have potential clashes. After I used the private function Browseforfile, then I cannot use the "MergeMultiDocsIntoOne" macro as only excel file is allowed to choose. I don't understand why and I cannot figure it out.
I am seeking your guidance and expertise to resolve this issue. Below are the details of the macros:
Macro 1: MergeMultiDocsIntoOne()
This macro is designed to merge multiple Word documents into a single document. It performs the following steps:
Inserts a section break at the end of the current selection.
Prompts the user to select multiple Word documents using the file picker dialog.
Inserts each selected document into the current document.
Inserts a page break between each inserted document.
Code:
Sub MergeMultiDocsIntoOne()
Selection.EndKey Unit:=wdStory
Selection.InsertBreak Type:=wdSectionBreakNextPage
Dim dlgFile As FileDialog
Dim nTotalFiles As Integer
Dim nEachSelectedFile As Integer
Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
With dlgFile
.AllowMultiSelect = True
If .Show <> -1 Then
Exit Sub
Else
nTotalFiles = .SelectedItems.Count
End If
End With
For nEachSelectedFile = 1 To nTotalFiles
Selection.InsertFile dlgFile.SelectedItems.Item(nEachSelectedFile)
If nEachSelectedFile < nTotalFiles Then
Selection.InsertBreak Type:=wdPageBreak
Else
If nEachSelectedFile = nTotalFiles Then
Exit Sub
End If
End If
Next nEachSelectedFile
End Sub
Macro 2: Browse For File (this is a private function of the macro, i cannot share the whole macro here due to privacy)
This macro is a function that opens a file picker dialog and allows the user to select a file. It has two optional parameters: "strTitle" for specifying the dialog title and "bExcel" to determine if only Excel workbooks should be displayed in the dialog. It performs the following steps:
Creates a file dialog object.
Configures the file dialog properties based on the parameters.
Shows the file picker dialog to the user.
Returns the path of the selected file.
It appears that both macros use the same object variable "dlgFile" of the "FileDialog" type, which may lead to conflicts when running the code.
Code:
Private Function BrowseForFile(Optional strTitle As String, Optional bExcel As Boolean) As String
Dim fDialog As FileDialog
On Error GoTo err_Handler
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.Title = strTitle
.AllowMultiSelect = False
.Filters.Clear
If bExcel Then
.Filters.add "Excel workbooks", "*.xls,*.xlsx,*.xlsm"
Else
.Filters.add "Word documents", "*.doc,*.docx,*.docm"
End If
.InitialView = msoFileDialogViewList
If .Show <> -1 Then GoTo err_Handler:
BrowseForFile = fDialog.SelectedItems.Item(1)
End With
lbl_Exit:
Exit Function
err_Handler:
BrowseForFile = vbNullString
Resume lbl_Exit
End Function
I am unsure how to resolve this clash and would appreciate any assistance or suggestions on how to modify these macros to avoid conflicts and ensure smooth execution.
Thank you in advance for your support!