Project #7: Get Data from Text File

 Project #7: Get Data from Text File

In this project, we will create a VBA macro to read data from a text file and import it into an Excel worksheet.

Here are the steps to achieve this:

Prompt User to Select Text File: We will use the FileDialog object to prompt the user to select the text file they want to import.

Open the Text File: Once the user selects a file, we will use VBA to open the text file for reading.

Read File Contents: We will read the contents of the text file line by line and store them in a variable.

Import Data into Excel Worksheet: We will import the data from the text file into an Excel worksheet.

Here's the VBA code for this project:

Sub ImportTextFileData() 
 Dim filePath As String 
 Dim fileContent As String 
 Dim dataArray() As String 
 Dim dataRange As Range
    Dim i As Long 
 ' Prompt user to select text file
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select Text File"
        .Filters.Clear
        .Filters.Add "Text Files", "*.txt"
        
        If .Show = -1 Then 
' User clicked OK
            filePath = .SelectedItems(1)
        Else 
 MsgBox "No file selected. Import canceled." 
 Exit Sub 
 End If 
 End With 
 ' Open text file for reading
    Open filePath 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).Range("A1").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 
 MsgBox "Text file data imported successfully." 
End Sub

In this macro:

We prompt the user to select a text file using the FileDialog object.

We open the selected text file for reading and read its entire content into a string variable.

We split the file content into an array of lines using the Split function.

We determine the range where the data will be imported into the Excel worksheet.

We loop through each line of the text file content and import it into the specified range in the Excel worksheet.

You can customize this macro further based on your specific requirements, such as adjusting the range where data is imported or adding error handling for file selection and reading.