How to allow user input in the File name/path-CodePudding

How can we use the file name or file path as an input from the user who will be using the macro. Either they are able input the filename or file path in the following variable. Currently the file name "fivetables.docx" is hardcoded.

Set wdDoc = .Documents.Open(ActiveWorkbook.Path & "\fivetables.docx")

Full Script Below -

Sub ImportWordTables()
Application.ScreenUpdating = False
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim xlWkSht As Worksheet, i As Long, j As Long, t As Long
With wdApp
  .Visible = False
  Set wdDoc = .Documents.Open(ActiveWorkbook.Path & "\fivetables.docx")
  With wdDoc
    t = .Tables.Count
    i = CLng(InputBox("The document has " & t & " tables." & vbCr & _
        "Table to start at?"))
    If i < 1 Then GoTo ErrExit
    If i > t Then GoTo ErrExit
    j = CLng(InputBox("The document has " & t & " tables." & vbCr & _
        "Table to end at?"))
    If j > t Then j = t
    For t = i To j
      .Tables(t).Range.Copy
      Set xlWkSht = ActiveWorkbook.Worksheets.Add
      xlWkSht.PasteSpecial "HTML"
      xlWkSht.Range("A1").CurrentRegion.EntireColumn.AutoFit
    Next
ErrExit:
    .Close False
  End With
  .Quit
End With
Application.ScreenUpdating = True
End Sub

CodePudding user response：

For example:

Sub ImportWordTables()
Application.ScreenUpdating = False
Dim StrFlNm As String
With Application.FileDialog(msoFileDialogOpen)
  .AllowMultiSelect = False
  .Filters.Add "Documents", "*.doc; *.docx; *.docm", 1
  If .Show = -1 Then
    StrFlNm = .SelectedItems(1)
  Else
    Exit Sub
  End If
End With
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim xlWkSht As Worksheet, i As Long, j As Long, t As Long
With wdApp
  .Visible = False
  Set wdDoc = .Documents.Open(FileName:=StrFlNm, AddToRecentFiles:=False)

etc.

CodePudding user response：

Use the built-in FileSaveAs dialog.

See How to set the default suggested filename to be displayed by the Save As dialog the first time a user saves a new document, Getting help with calling Word's built-in dialogs using VBA (and why doing so can be much more useful than you'd think), and How to change the directory of the Save As dialog.

Basically, call the dialog from within your code and let the user use the dialog to set the name and path.

The easiest and best way is to specify the path you want in the Name argument of the Dialogs object:
With Dialogs(wdDialogFileSaveAs)
    .Name = "c:\windows\temp\"
    .Show
End With

You can, of course, use some method and have your user type a path and name into a variable and then use that variable directly. The dialogs are there and are designed for this purpose, though. Why not use them?

If you want to have the dialog preloaded with a different file name, then put your preferred filename there in place of ActiveDocument.Name, e.g.:
With Dialogs(wdDialogFileSaveAs)
    .Name = "C:\My Documents\temp.doc"
    .Show
End With

Quoted material from How to change the directory of the Save As dialog.