How to Insert File Name into Excel Sheet Using VBA

How to Insert File Name into Excel Sheet Using VBA?

Suppose we have thousands of File in a Folder and we want to import files name into Excel sheet for some work and we also want the work is completed very soon then we need to use VBA code for importing files name. VBA consume less time for doing any types of task in Excel.

In this blog, we will use Dir function and Do While Loop in code. Dir function help to get the file name from the folder. Do While Loop repeats the task as per the number of files. We can also use Do Until Loop and For Each Loop for repeating task but we will use Do While Loop in our Code.

We have to follow some steps for writing code for the solution.

First of all, we should disable Screen Updating and Display Alert. This may help to code run fast. This is not necessary but this is good practice for programming.

Application.ScreenUpdating = False

Application.DisplayAlerts = False

After that, we should also change Calculation option automatic to Manual, if we do not use any function in excel sheet for calculation. This may also help to code run fast.

Application.Calculation = xlCalculationManual

After that, we have to declare some variable as per need. The variable name must unique and meaning full to understand better for me and another person who reads the first time. Rules for Variable Name

Dim Row_Num As Integer

Dim Folder_Path As String

Dim File_Name As String

After that, we have to assign a value for each Variable name.

Row_Num = 1

Folder_Path = “E:\AKASH\Other\” ‘Path always end with Backslash “\”

File_Name = Dir(Path)

Dir function extract single file name from Folder and that name assign as a string value for File_Name Variable.

After that, we use Do While loop for this program. We check the condition beginning of the loop.

Do While File_Name <> vbNullString

We can use two double quotes (“”) instead of vbNullString.

After that, when the condition is true then we get the filename into excel sheet from this line

ActiveSheet.Cells(Row_Num, 1).Value = Folder_Path

ActiveSheet.Cells(Row_Num, 2).Value = File_Name

After that, we add value in Row_Num variable by 1 for getting next row no.

Row_Num = Row_Num + 1

After that, we have to get next file name so we use this code.

File = Dir()

After that, we use Loop keyword for repeating our task.

Loop

After that, we should enable Screen Updating, Display Alert.

Application.ScreenUpdating = True

Application.DisplayAlerts = True

After that, we should also change the Calculation option Manual to Automatic.

Application.Calculation = xlCalculationAutomatic