Project #7: Creating a Loop to Read Each File

 Project #7: Creating a Loop to Read Each File

In this project, we'll enhance the previous macro to create a loop that reads each selected text file and imports its data into an Excel worksheet. This loop allows us to handle multiple files efficiently without duplicating code.

Here's how we can implement the loop:

Loop Through Selected Files: We'll iterate through each file name in the fileNames array obtained from the file picker dialog.

Open and Import Data from Each File: Inside the loop, we'll open each file, read its content, split it into lines, and import the data into the Excel worksheet.

Update Import Row: We'll use a variable to keep track of the next available row for importing data into the worksheet. After importing data from each file, we'll update this variable to move to the next available row for the next file.

Here's the updated VBA code with the loop:

Sub ImportMultipleTextFiles() 
 Dim fileNames As Variant 
 Dim fileName As Variant 
 Dim fileContent As String 
 Dim dataArray() As String 
 Dim dataRange As Range 
 Dim importRow As Long 
 Dim i As Long 
 ' Prompt user to select multiple text files
    fileNames = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Select Multiple Text Files", , True)
    
    If IsArray(fileNames) Then 
 ' Initialize import row
        importRow = 1 
 ' Loop through each selected file
        For Each fileName In fileNames 
 ' Open text file for reading
            Open fileName For Input As #1
            fileContent = Input$(LOF(1), #1)
            Close #1 
 ' Split file content into lines
            dataArray = Split(fileContent, vbCrLf) 
 ' Determine the range to import data
            Set dataRange = ThisWorkbook.Sheets(1).Cells(importRow, 1).Resize(UBound(dataArray) + 1, 1) 
 ' Import data into Excel worksheet
            For i = 0 To UBound(dataArray)
                dataRange.Cells(i + 1, 1).Value = dataArray(i)
            Next i 
 ' Move importRow to the next available row for next file
            importRow = importRow + UBound(dataArray) + 2
        Next fileName 
 MsgBox "Text files imported successfully."
    Else 
 MsgBox "No files selected. Import canceled." 
 End If 
End Sub

In this updated macro:

We initialize the importRow variable to 1 before starting the loop to track the next available row for importing data into the worksheet.

Inside the loop, we use this variable to determine the starting row for each file's data import and update it after importing data from each file.

This allows us to import data from each file into consecutive rows in the Excel worksheet without overwriting previously imported data.

You can further customize this macro to fit your specific requirements, such as adjusting the import location, handling different file formats, or adding additional error handling.