VBA Choose a file

There may be many situations in your VBA project when you might need to open up the dialog box and choose a file to carry out a task with.

This simple and quick solution will do just that, open up the dialog box at default C: and allow you to choose a file (fname). You can then choose what happens to fname, open it, record the folder and path in a cell or userform textbox for instance.



Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen
.AllowMultiSelect = True
''Start in
.InitialFileName = "C:\"
.Show
End With

For Each fname In dlgOpen.SelectedItems
msgbox(fname)
Next







Contact us for some advice and guidance on how your Excel development could be created and start helping your business straight away. Contact Us

Datanology

One thought on “VBA Choose a file

  1. Nick says:

    This code will let you choose a file then import the first tab into the data tab of your workbook.

    Dim fileDialog As fileDialog
    Dim strPathFile As String
    Dim strFileName As String
    Dim strPath As String
    Dim dialogTitle As String
    Dim wbSource As Workbook
    Dim rngToCopy As Range
    Dim rngRow As Range
    Dim rngDestin As Range
    Dim lngRowsCopied As Long

    dialogTitle = “Navigate to and select required file.”
    Set fileDialog = Application.fileDialog(msoFileDialogFilePicker)
    With fileDialog
    .InitialFileName = “C:\”
    ‘.InitialFileName = ThisWorkbook.Path & “\” ‘Alternative to previous line
    .AllowMultiSelect = False
    .Filters.Clear
    .Title = dialogTitle

    If .Show = False Then
    MsgBox “File not selected to import. Process Terminated”
    Exit Sub
    End If
    strPathFile = .SelectedItems(1)
    End With

    Set wbSource = Workbooks.Open(Filename:=strPathFile)

    With wbSource.Worksheets(1)
    Set rngToCopy = .Range(.Cells(1, “A”), .UsedRange.SpecialCells(xlCellTypeLastCell))
    For Each rngRow In rngToCopy.Rows
    If WorksheetFunction.CountA(rngRow) = 0 Then
    rngRow.EntireRow.Hidden = True ‘Hides rows with no data
    End If
    Next rngRow

    Set rngDestin = ThisWorkbook.Sheets(“Data”).Cells(1, “A”) ‘Edit “Sheet1” to destination sheet name

    rngToCopy.SpecialCells(xlCellTypeVisible).Copy Destination:=rngDestin

    lngRowsCopied = rngToCopy.Columns(1).SpecialCells(xlCellTypeVisible).Count

    .Rows.Hidden = False ‘Unhides previously hidden rows

    End With

    wbSource.Close SaveChanges:=False

    Set fileDialog = Nothing
    Set rngRow = Nothing
    Set rngToCopy = Nothing
    Set wbSource = Nothing
    Set rngDestin = Nothing

Leave a Reply

Your email address will not be published. Required fields are marked *