Project #7: Importing Multiple Text Files with the Get Open Filename Method

Project #7: Importing Multiple Text Files with the Get Open Filename Method

In this project, we'll create a VBA macro to import data from multiple text files into an Excel worksheet using the Application.GetOpenFilename method. This method allows users to select multiple files from a dialog box, making it convenient for importing multiple text files at once.

Here's how we can accomplish this:

Prompt User to Select Text Files: We'll use the Application.GetOpenFilename method to display a file picker dialog that allows users to select multiple text files.

Open and Import Data from Text Files: Once the user selects the text files, we'll loop through each selected file, open it, and import its data into the Excel worksheet.

Handle Errors and Exceptions: We'll implement error handling to gracefully handle any issues that may arise during the import process.

Here's the VBA code for this project:

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 
 ' 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, 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 macro:

We use the Application.GetOpenFilename method to prompt the user to select multiple text files. The selected file names are stored in the fileNames array.

We loop through each selected file, open it for reading, and import its data into the Excel worksheet.

We handle cases where no files are selected or if the user cancels the file selection process.

Each file's data is imported into consecutive rows in the Excel worksheet, with a blank row separating data from different files.

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