To work on multiple files through VBA programming, you need to have VBA code that can list files in a folder.
Here is a simple code for you, this function list files in a folder on an Excel sheet. Note that if there are sub-folders available in the folder then this function will not read the files inside the sub-folders.
'This function lists files in a folder
'Note: It will not read any files inside a sub-folder
Public Sub ListFilesInFolder()
'Variable Declaration
Dim strPath As String
Dim vFile As Variant
Dim iCurRow As Integer
'Clear old data
Sheet1.Range("B9:B1000").ClearContents
'Set the path of the folder
strPath = Sheet1.Range("B4").Value
'Add slash at the end of the path
If Right(strPath, 1) <> "/" And Right(strPath, 1) <> "\" Then
strPath = strPath & "\"
End If
'Set Directory to folder path
ChDir strPath
vFile = Dir(strPath & "*.*") 'Change or add formats to get specific file types
iCurRow = 9
Do While vFile <> "" 'LOOP until all files in folder strPath have been looped through
Sheet1.Cells(iCurRow, 2).Value = vFile
vFile = Dir
iCurRow = iCurRow + 1
Loop
End Sub
To use this code in your Excel file, follow below steps:
1. Open an Excel file
2. Press Alt+F11
3. Insert a Module (Insert>Module) from the menu bar
4. Paste the code in the module
5. Now add a shape to the Excel sheet
6. Give a name to the shape like ‘List Files in Folder’
7. Right-click on the shape and select ‘Assign Macro…’
8. Select ‘ListFilesInFolder’ from the list and click on ‘Ok’ button
9. Done, enter a folder path in cell B4 and click on the ‘List Files in Folder’ button
VBA Code to Sort Data HERE IS A ONE LINE CODE FOR DEVELOPER’S REFERENCE WHICH CAN BE USED TO SORT DATA ‘Sort data in ascending order on Column F (Created At) Sheet1.Range(“A1:G” & Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row).Sort Key1:=Sheet1.Range(“F1”),…
Dummy Data Generator is an MS Excel based tool which has capability of generating 45 types of data which includes numbers, text, date, time, Memo (long text), Boolean etc.
VBA Code to Count Color Cells With Conditional Formatting Have you ever got into situation in office where you need to count the cells with specific color in conditional formatted Excel sheet? If yes then…
Outlook Bulk Email Tool is an Excel and Outlook based tool which helps you to send or draft email in bulk right from Excel. It reads the recipient details from Excel sheet and uses Outlook installed on your system to generate emails. The tool supports To, Cc, Subject, Email Body, Attachment, HTML Table in Email Body.
VBA Code to Read Outlook Emails Reading emails from Outlook and capture them in Excel file is very common activity being performed in office environment. Doing this activity manually every time is quite boring and…
What is the Usage of sheet color in Excel? When we prepare a report or a dashboard it is easy to identify or analyze reports with a change of color sheet tabs. Analysts generally give…
great help and . nicely presented