Importing Data from a Text File

The Import Workflow: From Text File to Excel Sheet

The core challenge in importing text files is that Excel needs to understand their structure. Are values separated by commas, tabs, or something else? Are there header rows to be skipped? Our VBA code will need to handle these details.

We will build our import macro in logical stages:

1. The Basic Import: Open a text file and get its contents into Excel.
2. Adding Control: Specify delimiters and skip headers for a cleaner import.
3. Making it Interactive: Let the user select the file to import instead of using a fixed path.
4. Creating a Professional Tool: Handle potential errors and make the process smooth and user-friendly.

Step 1: The Core Import Logic

The most direct way to open a text file in VBA is with the Workbooks.OpenText method. This method is specifically designed to handle structured text files. A key detail is that it opens the text file's contents into a new, separate Excel workbook. Our first task, then, is to open the file and then copy the data from that temporary workbook into our main workbook.

The following video provides an excellent, clear demonstration of this entire process.

Let's put this together. Open a new Excel workbook, press Alt + F11 to open the VBA Editor, and insert a new module. You'll also need a sample text file to import. Create a simple text file (e.g., in Notepad) named SampleData.txt with the following content, making sure the items on each line are separated by a Tab, not spaces. Save it to a known location, like C:\Temp\.

OrderID	Product	Amount
101	Gadget A	500
102	Widget B	1200
103	Gadget A	450
104	Thing C	780

Now, enter the following code into your module. Remember to change "C:\Temp\SampleData.txt" to the actual path where you saved your file.

Sub BasicImport()
    
    ' --- Declare Variables ---
    Dim filePath As String
    Dim wbImport As Workbook
    Dim wsMaster As Worksheet
    
    ' --- Set Objects & File Path ---
    filePath = "C:\Temp\SampleData.txt" ' IMPORTANT: Change to your file's path
    Set wsMaster = ThisWorkbook.Worksheets("Sheet1") ' Destination sheet
    
    ' Turn off screen flicker for a smoother run
    Application.ScreenUpdating = False
    
    ' --- Import the Text File ---
    ' This opens the text file into a new, temporary workbook
    Workbooks.OpenText FileName:=filePath
    
    ' Get a reference to the new workbook that was just opened
    Set wbImport = ActiveWorkbook
    
    ' --- Copy Data to Master Workbook ---
    ' Copy all contiguous data from the first sheet of the imported workbook
    wbImport.Worksheets(1).Range("A1").CurrentRegion.Copy
    
    ' Paste the data into our destination sheet
    wsMaster.Range("A1").PasteSpecial xlPasteValues
    
    ' --- Clean Up ---
    ' Close the temporary workbook without saving changes
    wbImport.Close SaveChanges:=False
    
    ' Turn screen updating back on
    Application.ScreenUpdating = True
    
    MsgBox "Basic import complete!", vbInformation
    
End Sub

Run this macro. You'll see the data from your text file appear in Sheet1. This code forms the foundation of our tool.

Step 2: Taking Control with OpenText Arguments

Our basic import worked well because the sample file was simple. Real-world files often have quirks, like extra header rows or different delimiters (e.g., commas instead of tabs). Workbooks.OpenText has several arguments that give us precise control.

The most useful arguments are:

• StartRow: An integer specifying which row to start importing from. Set this to 2 to skip a single header row.
• DataType: Typically xlDelimited, meaning the columns are separated by a character.
• Comma, Tab, Semicolon, Space: Set these to True or False to tell Excel which delimiters to use.

Let's return to the video to see how these are used.

Let's modify our Workbooks.OpenText line to incorporate these arguments. By skipping the header during the import, we no longer need to worry about copying it over.

' --- Import the Text File (with more control) ---
Workbooks.OpenText FileName:=filePath, _
                   StartRow:=2, _
                   DataType:=xlDelimited, _
                   Tab:=True

Notice the use of the line continuation character (_) to keep the code readable. If your file was comma-separated (a .csv file), you would set Tab:=False and Comma:=True.

Step 3: Making the Macro Interactive

Hardcoding a file path is inflexible. A professional tool should let the user choose the file they want to import. We can achieve this with the Application.GetOpenFilename function. This function displays the standard "Open" file dialog and returns the full path of the file the user selects.

Let's integrate this and also add two critical pieces of user-friendly logic:

1. Handling Cancellation: If the user clicks "Cancel", GetOpenFilename returns the Boolean value False. We must check for this and exit the sub gracefully to avoid an error.
2. Filtering Files: We can tell the dialog to only show specific file types, guiding the user to select the correct kind of file.

The final segments of the TeachExcel video cover these exact points.

The Final, Polished Macro

Now we can assemble all these pieces into our final, professional-grade import macro. This version is dynamic, robust, and user-friendly.

Sub ImportTextFile_Final()

    ' --- Declare Variables ---
    Dim filePath As Variant ' Use Variant to handle the "False" return on cancel
    Dim wbImport As Workbook
    Dim wsMaster As Worksheet
    
    ' --- Set Objects ---
    On Error Resume Next ' In case the sheet doesn't exist
    Set wsMaster = ThisWorkbook.Worksheets("Imported Data")
    If wsMaster Is Nothing Then
        Set wsMaster = ThisWorkbook.Worksheets.Add
        wsMaster.Name = "Imported Data"
    End If
    On Error GoTo 0
    
    wsMaster.Cells.Clear ' Clear any old data
    
    ' --- Prompt User to Select File ---
    filePath = Application.GetOpenFilename( _
        FileFilter:="Text Files (*.txt; *.csv),*.txt;*.csv", _
        Title:="Select a Text File to Import", _
        MultiSelect:=False)
        
    ' --- Check if User Cancelled ---
    If filePath = False Then
        MsgBox "No file selected. Import cancelled.", vbExclamation
        Exit Sub
    End If
    
    ' --- Start Import Process ---
    Application.ScreenUpdating = False
    
    ' Import the text file, skipping the header and specifying a tab delimiter
    Workbooks.OpenText FileName:=filePath, _
                       StartRow:=1, _
                       DataType:=xlDelimited, _
                       Tab:=True, _
                       Comma:=True ' Also check for commas

    Set wbImport = ActiveWorkbook
    
    ' Copy data to master workbook
    wbImport.Worksheets(1).Range("A1").CurrentRegion.Copy
    wsMaster.Range("A1").PasteSpecial xlPasteValues
    
    ' Clean up
    wbImport.Close SaveChanges:=False
    wsMaster.Columns.AutoFit ' Fit columns to the new data
    
    Application.ScreenUpdating = True
    
    wsMaster.Activate ' Show the user the result
    MsgBox "Data imported successfully!", vbInformation

End Sub

Assign this ImportTextFile_Final macro to a button on a main worksheet, and you have a powerful, reusable tool for yourself or a client.

Other Import Methods

While Workbooks.OpenText is often the best tool for the job, you should be aware of other methods for specific scenarios:

• QueryTables: Using ActiveSheet.QueryTables.Add is the VBA equivalent of using the "From Text/CSV" button on the Data ribbon. This creates a refreshable connection to the source file, which can be ideal for reports that need to be updated frequently.
• FileSystemObject: For very complex files where you might need to read the file line-by-line and perform custom logic on each line before writing it to Excel, the Scripting.FileSystemObject provides the most granular control. This is a more advanced, manual method reserved for non-standard import tasks.

Conclusion

You have now mastered a fundamental automation skill: importing data from external text files. This process is the starting point for countless data analysis and reporting tasks. By moving from a simple hardcoded script to an interactive and robust tool, you've seen how to apply programming principles to create truly valuable freelance solutions.

Key Takeaways:

• Workbooks.OpenText is the primary VBA method for importing delimited text files.
• The import process typically involves opening the text file into a temporary workbook, copying the data, and then closing the temporary workbook.
• Use arguments like StartRow, DataType, and delimiter flags (Tab, Comma) for precise control over the import.
• Application.GetOpenFilename provides a user-friendly way to let the user select a file.
• Always check if the user canceled the file dialog to prevent errors and ensure your macro is robust.

In our next lesson, we'll complete the data handling cycle by learning to do the reverse: automating the process of exporting a worksheet range to a new CSV file.