Macro to open Directory and select File using a wildcard

[howard]

I have a macro to select a directory , but need the code .filers.add amended so as to select a csv file

 With fDialog
    .Filters.Clear
      .Filters.Add "Excel files", "*.csv"
  .InitialFileName = "C:\sales\*Used Sales*Report*.*

 

[HaHoBe]

Hi howard,

I was stunned when testing as when the criteria was met any file with any extension was listed although only csv were wanted (System Windows11, Office 2019). You might be better off creating a UserForm with a ListBox/ComboBox to choose the path and folder, one for for extensions and a Listbox to show the results. Or have the results displayed in a sheet using Dir and i.e. BeforeRight-Click event to open the file wanted.

Holger

 

[howard]

Thanks for the advise Holger

 

[howard]

Hi Holger

I managed to resolve the issue

Sub Open_Workbook()

     With Sheets("Overaged Stock")
     .Range("A1:S1000").ClearContents
     End With
     
    Dim nb As Workbook, ts As Worksheet, A As Variant
    Dim rngDestination As Range
       Dim fDialog As Object, varFile As Variant
    Set ts = ActiveSheet
    Sheets("Demo Vehicles").Select
        
    On Error Resume Next
    Set rngDestination = Application.Range("'Overaged Stock'!A1")
    
    On Error GoTo 0
   If rngDestination Is Nothing Then Exit Sub  'User canceled
           MsgBox ("Select Overaged Report")
   Set fDialog = Application.FileDialog(3)
     
    With Application.FileDialog(msoFileDialogFilePicker)
.InitialFileName = "C:\downloads\*New*Stock*.*"  'change to suit one's needs
If .Show = 0 Then Exit Sub
A = .SelectedItems(1)
End With

     If A = False Or IsEmpty(A) Then Exit Sub
    
    
          Set nb = Workbooks.Open(Filename:=A, local:=True)
    ThisWorkbook.Activate
    
    
   nb.Sheets(1).Range("A:S").Copy
    rngDestination.PasteSpecial Paste:=xlPasteValues
    rngDestination.PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
  
    nb.Close savechanges:=False 'Close the source workbook
     
   
        Application.ScreenUpdating = False
    
End Sub

 

[HaHoBe]

Hi howard,

glad you found a way to meet your request.

You have codeline

   Set fDialog = Application.FileDialog(3)

but continue with

    With Application.FileDialog(msoFileDialogFilePicker)

instead of using fDialog?

Holger

 

[howard]

Thanks for pointing the code to be corrected