Search code examples
vbams-wordmailmerge

How to open file dialog rather than point a file in Marco of MS Word


Here is the Marco in Word that can produce barcodes, but when I record Marco I have chosen a file (t1.csv), how can I ask users to choose a file? I have tried some vb code about open file dialog but doesn't work for me

Sub To_Bar_Code()
'
' To_Bar_Code Macro
'
'
    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Users\jiqi9\Desktop\BC\BarCode\t1.csv", ConfirmConversions:=False, _
        ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
        Connection:="", SQLStatement:="", SQLStatement1:="", SubType:= _
        wdMergeSubTypeOther
    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
    Windows("LabelFinal").Activate
End Sub

Solution

  • For example:

    Sub To_Bar_Code()
    Dim MMSrc As String
    Application.DisplayAlerts = wdAlertsNone
    With Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
      .Title = "Select the mailmerge source file"
      .Filters.Add "CSV Files", "*.csv"
      .AllowMultiSelect = False
      If .Show = -1 Then
        MMSrc = .SelectedItems(1)
      Else
        MsgBox "No source file selected. Exiting", vbExclamation
        Exit Sub
      End If
    End With
    With ActiveDocument.MailMerge
      .MainDocumentType = wdFormLetters
      .OpenDataSource Name:=MMSrc, ConfirmConversions:=False, ReadOnly:=True, _
        LinkToSource:=False, AddToRecentFiles:=False, Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", _
        SQLStatement1:="", SubType:=wdMergeSubTypeOther
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
      End With
      .Execute Pause:=False
    End With
    Application.DisplayAlerts = wdAlertsAll
    Windows("LabelFinal").Activate
    End Sub