Replace Static Path with Dynamic Path in Word Macro

Question

Hi All,

Thank you in advance.

Currently I have a path that is hardcoded into my macro and I'd like to make it instead to open the "open dialog" and allow the user using the macro to select the file that they want to use. Full code is below but specifically what I have is this:

Dim sFname As String

    sFname = "changes.docx"

    Set oDoc = ActiveDocument

    Set oChanges = Documents.Open(FileName:=sFname, Visible:=False)

    Set oTable = oChanges.Tables(1)

I'd like sFname to instead equal the document that the user selects. Thanks again!

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

Full Code:

Dim oChanges As Document, oDoc As Document

Dim oTable As Table

Dim orng As Range

Dim rFindText As Range, rReplacement As Range

Dim i As Long

Dim sFname As String

    sFname = "changes.docx"

    Set oDoc = ActiveDocument

    Set oChanges = Documents.Open(FileName:=sFname, Visible:=False)

    Set oTable = oChanges.Tables(1)

    For i = 1 To oTable.Rows.Count

        Set orng = oDoc.Range

        Set rFindText = oTable.Cell(i, 1).Range

        rFindText.End = rFindText.End - 1

        Set rReplacement = oTable.Cell(i, 2).Range

        rReplacement.End = rReplacement.End - 1

        With orng.Find

             .ClearFormatting

             .Replacement.ClearFormatting

             .Text = rFindText

             .Replacement.Text = rReplacement

             .Forward = True

             .Wrap = wdFindContinue

             .Format = True

             .Font.Bold = rFindText.Bold

             .Font.Italic = rFindText.Italic

             .MatchCase = True

        Do While .Execute(FindText:=rFindText, _

                              Wrap:=wdFindStop) = True

                orng.FormattedText = rReplacement.FormattedText

                orng.Collapse wdCollapseEnd

            Loop

        End With

    Next i

lbl_Exit:

    Exit Sub

End Sub

Answer 1

There are a number of ways to do this, but probably the simplest is to use the built-in Open dialog, which combines choosing the file and opening it into one operation. Try this instead of your call to Documents.Open:

    With Dialogs(wdDialogFileOpen)

        If .Show = -1 Then

             Set oChanges = ActiveDocument

             sFname = oChanges.Name

        End If

    End With

Another way is to use the Application.FileDialog object to get the name. Here's some sample code:

Sub x()

    Dim sFname As String

    With Application.FileDialog(msoFileDialogOpen)

        .AllowMultiSelect = False

        .InitialFileName = Options.DefaultFilePath(wdDocumentsPath) & "\*.*"

        .Show

        If .SelectedItems.Count Then sFname = .SelectedItems(1)

    End With

    If Len(sFname) = 0 Then

        ' user canceled the dialog

        Exit Sub

    End If

    ' continue with rest of macro

    MsgBox sFname

End Sub

You can set some additional properties of the FileDialog before calling .Show. For instance, there's a .FilterIndex property that sets the default in the file-type dropdown; if you want to limit the display to only *.docx files, set .FilterIndex = 3.

Answer 2

Hi Jay!

So sorry for the long delay but wanted to say THANK YOU for your help. The first method didn't work (spit out an error) but the second one works like a charm.

The macro is just about done but I have one more issue that I'm about to post about right now. Thanks again for all of your help.