Change file path, hard coded in macro, via pop up

[Coenieh]

Hi,

I'm looking to get users to enter or change a file path in either a pop up or cell on a summary sheet, which will then change or update the path in the code, currently hard coded as per piece of code example below. The file path determines where required spreadsheets for that specific user is located, which will be used when the full macro is run. I don't want users to change the path directly in Developer.

Any ideas or help will be greatly appreciated.

On Error Resume Next
    xStrPath = "C:\Users\Joe.Bloggs\Desktop\Daily Tag Board Reports\New Tap Events Report\"
    xStrFName = Dir(xStrPath & "*.xlsx")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
    Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
    xStrAWBName = ActiveWorkbook.Name
    For Each xWs In ActiveWorkbook.Sheets
    xWs.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
    Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
    xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
    Next xWs
    Workbooks(xStrAWBName).Close
    xStrFName = Dir()

 

[Fluff]

How about

    xStrPath = Environ("Userprofile") & "\Desktop\Daily Tag Board Reports\New Tap Events Report\"

   With Application.FileDialog(4)
      .AllowMultiSelect = False
      .InitialFileName = xStrPath
      If Not .Show = -1 Then Exit Sub
      xStrPath = .SelectedItems(1) & "\"
   End With

    xStrFName = Dir(xStrPath & "*.xlsx")

 

[Coenieh]

How about

    xStrPath = Environ("Userprofile") & "\Desktop\Daily Tag Board Reports\New Tap Events Report\"

   With Application.FileDialog(4)
      .AllowMultiSelect = False
      .InitialFileName = xStrPath
      If Not .Show = -1 Then Exit Sub
      xStrPath = .SelectedItems(1) & "\"
   End With

    xStrFName = Dir(xStrPath & "*.xlsx")

Hi Fluff,

Thanks for your response. It will work and I have considered this approach, the only issue is that not all users will have the folder named the same. What I forgot to mention is that I have a piece of code that allows users to browse the folder where they store their spreadsheets, which then stores the path in cell B11 on sheet A. That's why I'm looking for away to insert that selected path from cell B11 into the VBA code. See code for folder browser below:

Sub browseFolderPath()
    On Error GoTo err
    Dim fileExplorer As FileDialog
    Set fileExplorer = Application.FileDialog(msoFileDialogFolderPicker)
   
    'To allow or disable to multi select
    fileExplorer.AllowMultiSelect = False
   
    With fileExplorer
        If .Show = -1 Then 'Any folder is selected
            [folderPath] = .SelectedItems.Item(1)
        Else ' else dialog is cancelled
            MsgBox "You have cancelled the dialogue"
            [folderPath] = "" ' when cancelled set blank as file path.
        End If
    End With
err:
    Exit Sub
End Sub

 

[Fluff]

In that case you can use

      .InitialFileName = Sheets("A").Range("B11").Value