- VBA Tutorial
- VBA Useful Resources
- Selected Reading
List all filenames in folder and subfolder with VBA code. List all files from a folder including subfolders into a worksheet: Kutools for. The VBA Code (Macro) You need to write the Macro (or the code) in the Master file, as it will extract data from other files and write the data in it. I have a Button on my master file. The button is an ActiveX control. The click event calls a functions to read all the files in a folder.
You can also read Excel File and write the contents of the cell into a Text File using VBA. VBA allows the users to work with text files using two methods −
- File System Object
- using Write Command
File System Object (FSO)
As the name suggests, FSOs help the developers to work with drives, folders, and files. In this section, we will discuss how to use a FSO.
| Sr.No. | Object Type & Description |
|---|---|
| 1 | Drive Drive is an Object. Contains methods and properties that allow you to gather information about a drive attached to the system. |
| 2 | Drives Drives is a Collection. It provides a list of the drives attached to the system, either physically or logically. |
| 3 | File File is an Object. It contains methods and properties that allow developers to create, delete, or move a file. |
| 4 | Files Files is a Collection. It provides a list of all the files contained within a folder. |
| 5 | Folder Folder is an Object. It provides methods and properties that allow the developers to create, delete, or move folders. |
| 6 | Folders Folders is a Collection. It provides a list of all the folders within a folder. |
| 7 | TextStream TextStream is an Object. It enables the developers to read and write text files. |
Drive
Drive is an object, which provides access to the properties of a particular disk drive or network share. Following properties are supported by Drive object −
- AvailableSpace
- DriveLetter
- DriveType
- FileSystem
- FreeSpace
- IsReady
- Path
- RootFolder
- SerialNumber
- ShareName
- TotalSize
- VolumeName
Example
Step 1 − Before proceeding to scripting using FSO, we should enable Microsoft Scripting Runtime. To do the same, navigate to Tools → References as shown in the following screenshot.
Step 2 − Add 'Microsoft Scripting RunTime' and Click OK.
Step 3 − Add Data that you would like to write in a Text File and add a Command Button.
Step 4 − Now it is time to Script.
Output
When executing the script, ensure that you place the cursor in the first cell of the worksheet. The Support.log file is created as shown in the following screenshot under 'D:Try'.
The Contents of the file are shown in the following screenshot.
Write Command
Unlike FSO, we need NOT add any references, however, we will NOT be able to work with drives, files and folders. We will be able to just add the stream to the text file.
Example
Output
Upon executing the script, the 'write.txt' file is created in the location 'D:Try' as shown in the following screenshot.
The contents of the file are shown in the following screenshot.
VBA display files of a folder
In previous example, you had learned VBA code to display subfolders in a folder by using subfolders property. For this example, you will learn VBA code to display files in a folder by using Drives, Subfolders, and Files properties. All files in a folder will bepopulated in the the list box when the folder is double-clicked. Theexample illustrates a FileFolder form that has a combo box(CboDrives), two list boxes(LstFolders,LstFiles). See the figure below:
-When the form loads, the drive C is selected and its subfolders populated in the list box.(See FileFolder form above).
- When you double-click the folder in the folder list(left list box), all files that contain in the folder will display in the file list(right list box).
-If you want to select any drive, click arrow down of combo box and select the drive.
To have a form as the figure above, you have to create the form in Form Design. See the figure below:
- Drag and drop a combo box, and two list boxes to the form.
- Set Name properties of the three controls:
Name: CboDirectory
Name: LstFolers
Name: LstFiles
Then apply the VBA Code below:
Private Sub Form_Load()
CboDrives.RowSource = '
GetDrives
GetFolders (CboDrives.Value)
End Sub
..............................................
Private Sub CboDrives_Change()
GetFolders (CboDrives.Value)
End Sub
..............................................
Private Sub LstFolders_DblClick(Cancel As Integer)
GetFiles (LstFolders.ItemData(LstFolders.ListIndex))
End Sub
..............................................
Sub GetDrives()
' Add drives of local machine to combo box
Dim fs, dr As Variant
CboDrives.RowSourceType = 'Value List'
Set fs = CreateObject('Scripting.FileSystemobject')
Set dr = fs.Drives
For Each X In dr
CboDrives.AddItem X.DriveLetter
Next
CboDrives.Value = CboDrives.ItemData(0)
End Sub
..............................................
Sub GetFolders(dr As String)
' Add subfolders of drive to list box
Dim fs, fl, sf As Variant
LstFolders.RowSourceType = 'Value List'
LstFolders.RowSource = '
Set fs = CreateObject('Scripting.Filesystemobject')
Set fl = fs.GetFolder(dr & ':')
Set sf = fl.SubFolders
For Each Y In sf
LstFolders.AddItem dr & ':' & Y.Name
Next
End Sub
..............................................
Sub GetFiles(fol As String)
' Add files of subfolder to list box
Dim fs, fl, f As Variant
LstFiles.RowSourceType = 'Value List'
LstFiles.RowSource = '
Set fs = CreateObject('Scripting.FileSystemobject')
Set fl = fs.GetFolder(fol)
Set f = fl.Files
Download route editor for msts travnik. For Each X In f
LstFiles.AddItem X.Name
Next
End Sub
..............................................
Sub ShowFileList(folderspec)
' Show files of subfolder in the list box
Dim fs, fl, f As Variant
Set fs = CreateObject('Scripting.FileSystemObject')
Set fl = fs.GetFolder(folderspec)
Set f = fl.Files
For Each z In f
LstFiles.AddItem z.Name
Next
End Sub
Comments
nix I have a specific directory that I would like to select files for. I managed to get to the first level down - my dilemma is I cannot seem to past one level of directories. Suppose there are four of five levels of folders that need to be accessed one level at a time. How would the code be listed? Allow me to apologize, I'm a beginner beginner VBasic programmer. So your help would greatly be appreciated. 2017-01-11 |
fazl gggggg 2016-04-06 |
- Author: admin
- Category: Category