Project #7: Adding a New Sheet for Imported Data

Project #7: Adding a New Sheet for Imported Data

In this project, we'll enhance the previous macro to create a new worksheet for each imported text file. This will allow us to keep the data from each file separate and organized within the Excel workbook.

Here's how we can modify the macro to achieve this:

Create a New Sheet for Each File: Inside the loop that iterates through the selected files, we'll add a new worksheet for each file.

Import Data into New Sheet: After adding the new worksheet, we'll import the data from the text file into the newly created worksheet.

Here's the updated VBA code with the modification:

Sub ImportMultipleTextFiles() 
 Dim fileNames As Variant 
 Dim fileName As Variant 
 Dim fileContent As String 
 Dim dataArray() As String 
 Dim dataRange As Range 
 Dim newSheet As Worksheet 
 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 
 ' Loop through each selected file
        For Each fileName In fileNames 
 ' Add a new worksheet for each file
            Set newSheet = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
            newSheet.Name = "Imported Data from " & Right(fileName, Len(fileName) - InStrRev(fileName, "\")) 
 ' 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 = newSheet.Cells(1, 1).Resize(UBound(dataArray) + 1, 1) 
 ' Import data into new worksheet
            For i = 0 To UBound(dataArray)
                dataRange.Cells(i + 1, 1).Value = dataArray(i)
            Next i
        Next fileName
        
        MsgBox "Text files imported successfully." 
 Else 
 MsgBox "No files selected. Import canceled." 
 End If 
End Sub

In this modified macro:

For each selected file, we add a new worksheet using the Sheets.Add method and assign a name to the new worksheet indicating the imported data's source file.

We import the data from the text file into the newly created worksheet, keeping the data from each file separate and organized.

This modification enhances the organization and clarity of the imported data by storing it in separate worksheets based on the source files.