Macro to Browse to C:\extract

[howard]

I have the following code, but code does not select directory "C:\extract. It selects previous Directory that was selected


Kindly amend my code so that C:\extract is selected. If I manually select C:\extract when selecting Macro the rest of the code works. I only the section of code amended so that the directory C:\extract is selected and not the previous folder that was selected

 Sub Open_UV_Workbook()
    'Clear the previous folder selection
    ChDrive ("C:")
    ChDir ("\")

    'Browse to the "C:\extract" folder
    ChDir ("C:\extract")

    'Clear the contents of the "Used Vehicles" sheet
    With Sheets("Used Vehicles")
        .Range("A1:AQ1500").ClearContents
    End With

    'Declare variables
    Dim nb As Workbook, ts As Worksheet, A As Variant
    Dim rngDestination As Range
    Dim filename As Variant
    Dim File As String
    File = Dir("C:\extract")

    'Select the "Used Vehicles" sheet
    With Sheets("Used Vehicles")
        .Select
    End With

    On Error Resume Next
    Set rngDestination = Application.Range("'Used vehicles'!A1")
    On Error GoTo 0

    If rngDestination Is Nothing Then Exit Sub  'User canceled

    'Select the CSV file to open
    MsgBox ("Select Used Vehicle Report")

    With Application.FileDialog(msoFileDialogFilePicker)
        .ButtonName = "Select"
        With .Filters
            .Clear
            .Add "CSV Files", "*.csv"
        End With
        .FilterIndex = 1
        .InitialFileName = "*Used Vehicles*.csv"
        .Title = "Select CSV file"
        If .Show = 0 Then Exit Sub
        filename = .SelectedItems(1)
    End With

    Debug.Print filename
    Application.ScreenUpdating = False

    'Open the CSV file and copy its contents to the "Used Vehicles" sheet
    Dim srcWorkbook As Workbook
    Set srcWorkbook = Workbooks.Open(filename:=filename, local:=True)
    ThisWorkbook.Activate

    srcWorkbook.Sheets(1).Range("A:AQ").Copy
    rngDestination.PasteSpecial Paste:=xlPasteValues
    rngDestination.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False

    srcWorkbook.Close SaveChanges:=False
End Sub

 

[Micron]

Can't see why it does what you say unless you're storing the last path in a module level variable that you're not showing since
a) the dialog is supposed to provide only a starting point if you use that property at all, and
b) the InitialFileName property is supposed to be the complete starting path, not a file name. At least that's how I remember it.
So try .InitialFileName = "C:\extract\"

 

[howard]

thanks for the help. I changed this parts of the code from

 .InitialFileName = "*Used Vehicles*.csv"

to

 .InitialFileName = "C:\extract\*Used Vehicles*.csv"

Code now selects correct folder

 

[Micron]

You're welcome, and thanks for the recognition.