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

how to use do while loop

How to Insert File Name into Excel Sheet Using VBA?

Download Workbook

How to use Data Validation in Excel from VBA?

Leave a Reply

Your email address will not be published. Required fields are marked *