Get Info Of Selected Files Excel VBA Macro

This macro example triggers a dialog box to select files in windows explorer and then shows file name, date created, and file size information for each of them in the active worksheet.

Macro/VBA code:

 

  Sub GetInfoOfSelectedFiles()

      Dim selFiles As Variant, uFile As Variant

      With ActiveSheet

          .Cells.Clear

          .Range("A1:C1").Font.Bold = True

          .Range("A1:C1").Value = Array("File Name", "Date Created", "File Size (Kb)")

      selFiles = Application.GetOpenFilename("All Files (*.*),*.*", , "Select Files", , True)

      For Each uFile In selFiles

          r = r + 1

          .Range("A" & r + 1).Value = Dir(uFile, vbNormal)

          .Range("B" & r + 1).Value = FileDateTime(uFile)

          .Range("C" & r + 1).Value = FileLen(uFile)

      Next

      End With

  End Sub

 

Macro explained:

• First we declare two Variant variables to hold selected files and each individual file when looping through that collection. The Variant type can actually hold any type of data.
• We clear all cells in the active worksheet and add a title to each column in bold. The three titles (File Name, Date Created, File Size) are added to columns A through C using the Array function. Note we previously started a With statement to apply all properties to the active sheet.
• Then we use the GetOpenFilename method of the Application object to trigger a dialog box that allows to selected files and set it to the variable selFiles. GetOpenFilename accepts five optional arguments separated by commas. The first argument ("All Files (*.*),*.*") indicates all files can be selected, the second has been omitted here and refers to the index number of the filtering criteria on the first argument. The third argument correspond to the title of the dialog box (“Select Files” in our case). The fourth argument has been omitted and corresponds to the button text but only in Macintosh. The fifth argument specifies if multiple files selection is allowed (True in our case).
• Finally we loop through each file (uFile) within the selected files collection (selFiles) and add the file name (column A), date created (column B), and file size (column C) of each file with Dir, FileDateTime, and FileLen functions respectively. Note that if no files are selected it will generate a debug error, and therefore, error handling should be added to the code. If this is new to you please have a read of Debugging and Error Handling in the Training for Beginners.

This is how we get info of selected files in Excel with VBA macros.

Other examples:

• How to Crop Selection Window with Excel VBA macros
  
• How to create a Simple Digital Clock Timer with Excel VBA macros 
  
• How to Copy Selection To Other Workbook with Excel VBA macros