Open a specific folder using FilePicker

[tigerdel]

Good evening

I have code that opens the FilePicker to allow the user to open a file

What I would like to do is to have the FilePicker open a specific folder in OneDrive using the Environ call so it doesn't matter who is using it it goes to their folder

In this case I want it to open: "Environ$("OneDrive")" & "MJM Services\4.0 Accounts\2.0 Bank Statement Downloads"

The code I have [which works but doesn't go to the folder] is:

Dim fullpath As String, Cn As Variant, lastRow As Long
Call Clear_Raw_Data
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .Title = "Select the file to import..."
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.csv", 1
        .Show
        fullpath = .SelectedItems.Item(1)
    End With
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    With Worksheets("Raw Data").QueryTables.Add( _
        Connection:="TEXT;" & fullpath, _
        Destination:=Worksheets("Raw Data").Range("$A$2"))
        .Name = "Period Data"
        .FieldNames = False
        .TextFileStartRow = 2
        .TextFileCommaDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
    Columns("A:A").Select
    Selection.NumberFormat = "m/d/yyyy"
    End With
    For Each Cn In ThisWorkbook.Connections
        Cn.Delete
    Next Cn
    For Each Cn In ActiveSheet.QueryTables
        Cn.Delete
    Next Cn
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

Any help gratefully received

 

[Domenic]

You can use the InitialFileName property of the FileDialog object to specify the folder to display initially. By the way, you should also handle situations where a user clicks on Cancel. Try the following...

    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .InitialFileName = Environ$("OneDrive") & "\MJM Services\4.0 Accounts\2.0 Bank Statement Downloads\"
        .Title = "Select the file to import..."
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.csv", 1
        If .Show <> -1 Then Exit Sub 'user clicked Cancel
        fullpath = .SelectedItems.Item(1)
    End With

Hope this helps!

 

[tigerdel]

Brilliant thank you works perfectly

 

[Domenic]

Glad I could help, cheers!