Potential Clashes in Macros

yanyan9896 · #1 10-09-2023, 04:24 AM

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!

Italophile · #2 10-09-2023, 04:38 AM

The BrowseForFile function adds a filter to the File Picker dialog. As MergeMultiDocsIntoOne uses the same dialog without setting a filter it will inherit the filter from the previously used function.

The solution is simple - clear the filters, and optionally set a filter for the required file types, for the File Picker in your MergeMultiDocsIntoOne routine. You can simply copy and paste from the other function, e.g.

Code:

  With dlgFile
    .AllowMultiSelect = True
    .Filters.Clear
    .Filters.add "Word documents", "*.doc,*.docx,*.docm"

yanyan9896 · #3 10-09-2023, 04:59 AM

Quote:

Originally Posted by Italophile

The BrowseForFile function adds a filter to the File Picker dialog. As MergeMultiDocsIntoOne uses the same dialog without setting a filter it will inherit the filter from the previously used function.

The solution is simple - clear the filters, and optionally set a filter for the required file types, for the File Picker in your MergeMultiDocsIntoOne routine. You can simply copy and paste from the other function, e.g.

Code:

  With dlgFile
    .AllowMultiSelect = True
    .Filters.Clear
    .Filters.add "Word documents", "*.doc,*.docx,*.docm"

Thank you for your helpful explanation regarding the BrowseForFile function and its impact on the MergeMultiDocsIntoOne routine. I appreciate your suggestion to clear the filters and set a filter for the required file types in the File Picker dialog. I will implement this solution by copying and pasting the filter code from the other function.

Once again, thank you for your assistance.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Seeking help with Cross-Reference Links Beween Documents	SAikins	Word	5	05-18-2018 06:15 AM
Seeking help recreating a template from the internet	AwesomeWithAQ	Word	5	01-12-2018 11:13 AM
Seeking advice as to how to align items in an office directory that I made using PowerPoint	mcsharpe	PowerPoint	1	10-12-2015 04:21 PM
Seeking PowerPoint Freelance Help	gazdabitner	PowerPoint	0	12-06-2010 02:12 PM
Seeking Excel Expert Advice for Web Publishing	tjmichner	Excel	0	10-25-2006 08:40 AM