Want to create files from an Excel list without having to spend hours doing it by hand? Excel’s VBA (Visual Basic for Applications) allows you to automate this process and finish it quickly. With the help of the robust VBA tool, you may create your own custom code to automate monotonous operations and communicate with other apps. With detailed instructions and code samples, we’ll demonstrate how to use VBA to generate files from Excel lists in this guide. Bid adieu to wasting time manually creating files and welcome to a more productive method of working. Are you prepared to learn how VBA can speed up your workday and save you time? Let’s get going!
Download Practice Workbook
You can download the Excel workbook that we used to prepare this article.
Create Files from Excel List: Step-by-Step Procedure
In this section, we are going to show you a step-by-step procedure to create files according to the Excel list. We have used the dataset given for creating files.
Step 1: Launch VBA Editor
We are going to create a VBA module for the code. First, you need the Developer tab to display on your ribbon. If you don’t have that, you can look for it in how to enable the Developer tab on your ribbon.
- First, we go to the Developer tab.
- Then we will select Visual Basic.
Step 2: Insert a Module
After the previous steps, new window will pop up. After that, we are going to follow these steps,
- First, we will select Insert,
- Then we are going to select Module.
- A new Module will be created.
Step 3: Insert the Code in Module
Now we are going to open a module and write the code below
You can copy the code from here.
Sub createFiles()
Dim path As String, fileName As String, fileType As String, fullName As String
Dim lastRow As Long, i As Long, dotPosition As Long
Dim fso As Object
path = "C:\Users\user\Documents\ShareX\Screenshots\2023-04\files creation\" 'Specify the folder path here
Set fso = CreateObject("Scripting.FileSystemObject")
With ActiveSheet 'Change this to the name of the worksheet with the list
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row 'Assuming the file names and types are in column A
For i = 5 To lastRow 'My files' name starts from 5 th row you can choose your row to satrt
fullName = .Cells(i, "B").Value 'Get the full file name and type
dotPosition = InStrRev(fullName, ".") 'Find the position of the last dot
fileName = Left(fullName, dotPosition - 1) 'Get the file name by taking the characters to the left of the last dot
fileType = Right(fullName, Len(fullName) - dotPosition) 'Get the file type by taking the characters to the right of the last dot
fullName = path & fileName & "." & fileType 'Create the full file name and type
fso.CreateTextFile fullName 'Creates the file
'You can add more code here to write data to the file if needed
Next i
End With
Set fso = Nothing
End Sub
🔎 Code Explanation
Sub createFiles()
This line defines the start of the VBA sub-procedure that will create the files.
Dim path As String, fileName As String, fileType As String, fullName As String
Declares four string variables that will be used to store the path, file name, file type, and full file name of each file to be created.
Dim lastRow As Long, i As Long, dotPosition As Long
This line declares three long integer variables that will be used to store the last row number of the Excel list, the current row number, and the position of the last dot in the file name.
Dim fso As Object
Declares an object variable for the FileSystemObject, which will be used to create the files.
path = "C:\Users\user\Documents\ShareX\Screenshots\2023-04\files creation\"
This line sets the folder path where the files will be created. You should replace this path with the actual path where you want the files to be created.
Set fso = CreateObject("Scripting.FileSystemObject")
Creates an instance of the FileSystemObject.
With ActiveSheet
Starts a With block for the ActiveSheet, which is the worksheet with the list of file names and types.
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
This line sets the value of lastRow to the last non-blank row in column B of the ActiveSheet.
For i = 5 To lastRow
This line starts a loop that will iterate through each row in the list, starting from row 5.
fullName = .Cells(i, "B").Value
Gets the value of the file name and type in column B for the current row.
dotPosition = InStrRev(fullName, ".")
Finds the position of the last dot in the file name and type.
fileName = Left(fullName, dotPosition - 1)
Extracts the file name from the full name and type by taking the characters to the left of the last dot.
fileType = Right(fullName, Len(fullName) - dotPosition)
This line extracts the file type from the full name and type by taking the characters to the right of the last dot.
fullName = path & fileName & "." & fileType
This line creates the full file name and type by concatenating the path, file name, and file type.
CreateTextFile fullName
Creates the file using the full file name and type.
Next i
This line ends the loop.
End With
Ends the With block.
Set fso = Nothing
This line releases the FileSystemObject.
Overall, this code takes a list of file names and types in an Excel worksheet, creates a folder path where it will create the files, and uses VBA to loop through each row of the list and create a file for each row using the file name and type in that row. Here, we used the FileSystemObject to create the files, and the file name and extracted type from the full name using the position of the last dot in the name.
Step 4: Run Code in Module
Now we are going to run the code by Run button like below or pressing F5.
Step 5: Creating The Files in Destined Folder
Now the final result to create files in the destined folder is shown below.
How to Create a Folder From Excel List
Here we are going to create a folder from an Excel List with the help of Notepad. To do so we have used the following dataset and follow the steps given below.
- Now we have to create a Make Directory column using =”MD ” & B5 and drag and drop the fill button, copy the column elements to Notepad after creating a new Notepad, and save it using Ctrl+S.
- Now we have to change the file extension to create a batch file by changing .txt to .bat. Then press the batch. Here the batch file is New Text Document.bat.
- Double-click the file and it will create the folders.
How to Create Subfolders From Excel Lists
In this section, we are going to create subfolders from an Excel list. To do so we have to follow the steps and use the steps in the below dataset.
- First, we have to create a Make Directory Column using the function given below and Copy the Make Directory column elements to Notepad after creating a new Notepad.
- Then turn the notepad text file into a batch file changing the extension .txt to .bat by renaming.
="MD """&B5&"/"&C5&""" """&B5&"/"&D5&""""
- Now after changing it into a batch file, we will double-click the batch file. Here the batch file is New Text Document.bat.
- Now we will see that it has created subfolders.
Formula Explanation
=”MD “””&B5&”/”&C5&””” “””&B5&”/”&D5&””””
The formula in Excel is concatenates values in cells B5, C5, and D5.
Frequently Asked Question
Q: What is VBA, and how can I use it to make files from lists in Excel?
A: A programming language called VBA (Visual Basic for Applications) enables you to create unique Excel code. VBA allows you to communicate with other programs, such as Microsoft Word and PowerPoint, and automate repetitive activities. You can quickly and easily create several files based on data in an Excel list by using VBA in Excel.
Q: Do I need to be an expert programmer to utilize VBA?
A: No, using VBA does not require you to be an expert programmer. However, it’s advised to have some fundamental programming skills. You may find a wide variety of internet tools, such as forums and tutorials, to assist you in learning VBA.
Things to Remember
There are a few crucial considerations to bear in mind when using VBA to create files from Excel lists:
- Test your code: To make sure your VBA code functions as intended, test it on a tiny sample of data before running it. This can assist you in identifying any flaws or problems before you apply the code to a huge amount of data.
- Check for accuracy: Before creating files, make sure that the information in your Excel list is correct and up to date. Verifying your data again will help you avoid file mistakes.
- Pick file names wisely: It’s crucial to use descriptive and relevant file names when producing files from Excel lists. This can make it simpler for you to keep organized and to Backup your data: Be sure to create a backup of your data before editing or creating any new files in your Excel list. In the event that something goes wrong, this can save you from losing crucial data.
- Keep your code organized: It’s critical to maintain organization and documentation when writing VBA code. This may ease future comprehension and modification.
Conclusion
In conclusion, creating files from Excel lists using VBA can be a strong and efficient method. The process of creating several files based on information in an Excel list can be automated with VBA, easing your workflow and allowing you to concentrate on other things. Learning VBA can be a useful ability to have in your toolkit, whether you’re a novice or a seasoned Excel user. You may use VBA to create files quickly and easily, saving you time and increasing your productivity, by following the instructions in this guide and keeping the key points in mind. Why not give it a shot and see how VBA can help you streamline your processes and increase the effectiveness of your work?