Excel VBA: Import Text File into Worksheet

Did you know that Excel VBA can automate the process of importing a text file into a worksheet? With just a few lines of code, you can import data from a text file directly into Excel, saving you time and effort.

Whether you’re working with large datasets or simply need to import information from a text file, Excel VBA provides a powerful toolset to streamline your workflow. In this article, we’ll explore different methods to import text files into Excel worksheets using VBA code.

Read on to discover how you can leverage the power of Excel VBA to import text files seamlessly into your worksheets.

Key Takeaways:

• Excel VBA allows you to automate the process of importing text files into worksheets.
• There are various methods, such as using QueryTables, opening the text file in memory, or copying the data to the current sheet, to import text files into specific cells.
• The Application.GetOpenFilename method enables users to choose the text file to import.
• After importing the data, you can manipulate it further or delete the external connections.
• Excel VBA offers flexibility and efficiency when it comes to working with text files in Excel.

Using a QueryTable

One effective method for importing a text file into a specific starting cell in Excel VBA is by utilizing a QueryTable. This powerful feature allows you to define the destination range for the imported data, providing greater control over the import process. By modifying the provided code to include a QueryTable and set the desired starting cell, such as cell D1, you can seamlessly import the text file into the specified location.

Let’s take a closer look at how you can incorporate a QueryTable into your VBA code to achieve this:

1. First, insert the following code snippet at the beginning of your VBA procedure to declare the necessary variables:

Dim ws As Worksheet
Dim qt As QueryTable

2. Create a new QueryTable object using the following syntax:

Set ws = ThisWorkbook.Worksheets("Sheet1")
Set qt = ws.QueryTables.Add(Connection:=textFilePath, Destination:=ws.Range(startCell))

Replace textFilePath with the path to your text file, and startCell with the desired starting cell reference, such as “D1”. This will define the connection to the text file and set the destination range for the imported data.

3. You can then customize additional properties of the QueryTable object, as per your requirements. For example, you can set the text file delimiter, adjust formatting options, or even specify whether to overwrite existing data.

Here’s an example of how you can modify the code to set the text file delimiter to a comma:

qt.TextFileTabDelimiter = False
qt.TextFileCommaDelimiter = True

4. Finally, refresh the QueryTable to import the data from the text file:

qt.Refresh BackgroundQuery:=False

The QueryTable will now import the text file into the specified starting cell, providing a seamless and efficient way to incorporate external data into your Excel workbook.

Example:

Let’s illustrate this process with an example. Suppose we have a text file called “data.txt” and we want to import its contents starting from cell D1 in worksheet “Sheet1”. We would update the code as follows:

Sub ImportTextFile()
    Dim ws As Worksheet
    Dim qt As QueryTable

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set qt = ws.QueryTables.Add(Connection:="TEXT;C:\data.txt", Destination:=ws.Range("D1"))

    ' Set additional properties if required
    qt.TextFileTabDelimiter = False
    qt.TextFileCommaDelimiter = True

    ' Refresh the QueryTable to import data
    qt.Refresh BackgroundQuery:=False
End Sub

By incorporating a QueryTable into your VBA code, you can easily import a text file into a specific starting cell, leveraging the flexibility and customization offered by this powerful Excel feature.

Open the text file in memory

Another way to import a text file into a specific cell is by opening the text file in memory using Excel VBA. By doing so, you can store the file’s contents in a variable and manipulate them as needed before exporting them to the desired cell in the worksheet. This method provides flexibility in choosing the starting cell for the import.

To open a text file in memory, you can use the following VBA code:

Sub OpenTextFileInMemory()
    Dim FilePath As String
    Dim FileContent As String

    ' Specify the path of the text file
    FilePath = "C:\Path\To\Text\File.txt"

    ' Open the text file and read its contents
    Open FilePath For Input As #1
    FileContent = Input$(LOF(1), 1)
    Close #1

    ' Manipulate the file content as needed
    ' For example, you can replace text or extract specific data

    ' Export the modified content to the desired cell in the worksheet
    Sheet1.Range("D1").Value = FileContent
End Sub

In the above code, you need to replace “C:\Path\To\Text\File.txt” with the actual file path of the text file you want to import. After opening the file and storing its content in the FileContent variable, you can perform any required manipulations on the data. Finally, you can export the modified content to the desired cell in the worksheet by specifying the cell range (in this case, cell D1).

Example:

Below is an example of how the code can be used to open a text file in memory and import its contents into Excel:

Sub OpenTextFileInMemoryExample()
    Dim FilePath As String
    Dim FileContent As String

    ' Prompt the user to select a text file
    FilePath = Application.GetOpenFilename("Text Files (*.txt), *.txt")

    ' Exit if no file was selected
    If FilePath = "False" Then Exit Sub

    ' Open the selected text file and read its contents
    Open FilePath For Input As #1
    FileContent = Input$(LOF(1), 1)
    Close #1

    ' Export the file content to cell D1 in the active sheet
    ActiveSheet.Range("D1").Value = FileContent
End Sub

Copy text file to current sheet

If you prefer to stick with the method of opening the text file into a separate workbook, you can still copy the contents to the current sheet starting from a specific cell. The provided code demonstrates how to open the text file in a new workbook and then copy the data to the current sheet using the Cells.Copy method. This method allows you to specify the destination range for the copied data, such as starting from cell D1.

Excel VBA provides a straightforward way to copy the contents of a text file and paste it into the current sheet. By utilizing the Cells.Copy method, you can easily define the starting cell and paste the data accordingly. Here’s an example:

    
    Sub CopyTextFileToCurrentSheet()
        Dim filePath As String
        Dim wb As Workbook
        Dim ws As Worksheet

        ' Prompt user to select a text file
        filePath = Application.GetOpenFilename("Text Files (*.txt),*.txt")

        ' Open the selected text file in a new workbook
        Set wb = Workbooks.Open(filePath)

        ' Set the destination worksheet to the current sheet
        Set ws = ThisWorkbook.ActiveSheet

        ' Copy the data from the text file to the destination sheet
        wb.Sheets(1).UsedRange.Copy Destination:=ws.Range("D1")

        ' Close the text file workbook without saving changes
        wb.Close Savechanges:=False
    End Sub
    

This code starts by prompting the user to select a text file using the Application.GetOpenFilename method. After the file is selected, it is opened in a new workbook using Workbooks.Open. Next, the destination worksheet is set to the current sheet using ThisWorkbook.ActiveSheet. The data from the text file is then copied from the first sheet in the text file workbook to the specified range in the current sheet using wb.Sheets(1).UsedRange.Copy Destination:=ws.Range(“D1”). Finally, the text file workbook is closed without saving changes using wb.Close Savechanges:=False.

This method allows you to easily copy the contents of a text file and paste it into the desired location in the current sheet, providing flexibility and control over the imported data.

Use Application.GetOpenFilename to choose file

If you want to give the user the ability to choose the text file to import, you can use the Application.GetOpenFilename method in Excel VBA. This method opens a dialog box for file selection and returns the file path of the selected file. This allows for a more user-friendly experience and greater flexibility in importing specific files.

To implement this functionality, you can modify the code provided in the source to include the Application.GetOpenFilename method. Here’s an example:

Sub ImportTextFile()
    Dim filePath As Variant

    ' Open dialog box for file selection
    filePath = Application.GetOpenFilename("Text Files (*.txt), *.txt")

    ' Check if a file is selected
    If filePath  False Then
        ' Import the text file into the worksheet using the chosen file path
        ' Your import code here
    Else
        ' Handle case when no file is selected
        MsgBox "No file selected."
    End If
End Sub

In this code, the Application.GetOpenFilename method is used to open a dialog box that filters for text files (.txt). The selected file path is stored in the filePath variable. You can then proceed with importing the text file into the worksheet using the chosen file path.

This allows users to easily select the desired text file for import without having to manually type the file path, reducing the chance of errors and improving the overall usability of your application.

Delete external connection after refresh

Once you have refreshed the imported text file data in the worksheet using Excel VBA, it is important to delete the external connection created by the QueryTable. This ensures that there are no unwanted data refreshes in the future and helps optimize the performance of your workbook.

To delete the external connection after refreshing the data, you can simply add the .WorkbookConnection.Delete method to your VBA code. This method removes the connection, preventing any further updates or refreshes.

Here’s an example of how you can incorporate the .WorkbookConnection.Delete method into your code:

' Refresh the QueryTable data
.QueryTables(1).Refresh BackgroundQuery:=False

' Delete the external connection
.QueryTables(1).WorkbookConnection.Delete

By including this code snippet after refreshing the data, you can effectively delete the external connection and ensure that your workbook remains clean and optimized.

Deleting the external connection after refresh is a good practice, especially if you do not intend to update or modify the imported data frequently. It allows you to maintain control over the data sources and prevents any unwanted changes that may occur due to automatic refreshes.

Import text file into single cell with VBA

Excel VBA offers a powerful solution for importing a text file into a single cell. By utilizing VBA code, you can automate the process and save time and effort. In the Third source, you’ll find code that demonstrates how to import a text file into a new cell for each file in a given list.

This solution makes use of the Microsoft Scripting Runtime library to read the text file, and the FileSystemObject to access the file system. The code can be easily modified to suit your specific needs, such as changing the file path and the destination cell for the import.

Whether you want to import data from a CSV file, a log file, or any other text file format, this VBA solution allows you to consolidate it into a single cell, making it easier to analyze and work with the data. With a few tweaks to the code, you can seamlessly integrate this functionality into your Excel workflow.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.