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
How to Insert File Name into Excel Sheet Using VBA?
This is the full program for importing files name into Excel Sheet.
Sub Get_File_Name_With_Path()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Dim Row_Num As Integer
Dim Folder_Path As String
Dim File_Name As String
Row_Num = 1
Folder_Path = “C:\Users\AKASH\Desktop\Other\”
File_Name = Dir(Folder_Path)
Do While File_Name <> vbNullString
ActiveSheet.Cells(Row_Num, 1).Value = Folder_Path
ActiveSheet.Cells(Row_Num, 2).Value = File_Name
Row_Num = Row_Num + 1
File = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
End Sub
