Categorised Menu List

Extract File(s) path

Displays file names of a selected folder

Points to note:

When the code is copied and pasted on to code window, please be aware that they are copied as smart quotes (as in Ms.Word or any other rich text editors), so replace them with double quotes in the code window again.

 

All the content that you learn here will not be of any use, until you practice, so I urge you to practice. As and when you practice, you get more doubts as to how to go ahead, these doubts strengthen your knowledge and lead you to experience.

 

Practice those examples that I have placed under the content header Content for Assistance. These will be helpful in day-to-day activities.

The following code is written in Microsoft Excel.

Useful to list files from specified folder, including sub folders., into current spreadsheet.

The following Code retrieves the files from the selected location of your hard disk into the current spread sheet.


Advantage: You need not browse through all the folders and sub folders to know the contents of the folder.
If the computer displays a message saying: Macro Level set too HIGH, Click "Enable Macros" button. 

Procedure to Execute:

  • Open a new work sheet
  • Place a Command Button on the work sheet.
  • Press Alt + F11 (code window is displayed)
  • Copy the following Code and past it in code window.
  • Rename the Name of the Command Button as cmdBrowse (as the code is written for Command Button named cmdBrowse where cmd is abbreviation for Command and Browse is the name of the button.)
  • Also, Please note that Caption and Name are different. Caption is what is seen on the control and Name is with what you refer to that control in the programme.
  • Save the file and Click on Command Button.
  • Observation: when you click on command Button:
  • File Dialog box is displayed:
  • Select the required folder and click OK,

Files are displayed in the spread sheet and also a message box is displayed with the retrieved file count.

gallery/imgextractfilespaths
gallery/img_list-of-folders-and-file-names-and-paths-dir-excel-vba11
gallery/img_filename_paths

Sample Code:

a brief description : This code will help to extract files path from a particular folder.

 

For example: if there exists a folder "C:\Repath\ExcelFiles\", this folder has many files and sub folders. The path of each file is be listed, including sub-folders. This code will help to retrieve the file(s) path into current worksheet. The output would look like:
c:\Repath\ExcelFiles\abc.txt
c:\Repath\ExcelFiles\abcd.exe
c:\Repath\ExcelFiles\sample.xlsx
c:\Repath\ExcelFiles\Client\Jan.doc