How to Create Files From Excel List

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!

How to 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.

Download Practice Workbook

You can download the Excel workbook that we used to prepare this article.

Create Files from List.xlsx

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?

<< Go Back to Create Folder in Excel | Excel Files | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!