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

Coenieh

New Member
Joined
Oct 18, 2018
Messages
16
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.

VBA Code:
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()
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,216
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
    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

New Member
Joined
Oct 18, 2018
Messages
16
How about
VBA Code:
    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:

VBA Code:
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
 

Attachments

  • TinyTake13-07-2021-08-34-12.png
    TinyTake13-07-2021-08-34-12.png
    12.7 KB · Views: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,216
Office Version
  1. 365
Platform
  1. Windows
In that case you can use
VBA Code:
      .InitialFileName = Sheets("A").Range("B11").Value
 

Forum statistics

Threads
1,140,996
Messages
5,703,623
Members
421,306
Latest member
ambuj Thakur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top