Import a text file to Excel using VBA on the same sheet

Question

I want to import a text file into the current Excel Sheet.

Code I found keeps the text as I want, however it opens a new Excel workbook and pastes it there. This isn't ideal since at times I'll have to loop through many of these, and I have VBA/macros running on the tabs on which I'd like these to import. Any references to Sheets, ActiveSheet, etc., do not work with Workbooks, but rather the Workbook object, so I simply cannot manually reference the sheet I want.

Workbooks.OpenText Filename:="C:\textexample.txt", _
  Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
  TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
  Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
  Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
  Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), _
  Array(11, 1), Array(12, 1), Array(13, 1)), _
  TrailingMinusNumbers:=True

Power Query does import everything into my desired sheet with proper delimiters. It adds it into a table, with drop downs, formatting, etc. I want it in plain text, just as it would be in a .txt document. The only idea I have is to paste the table far away from my data, then have VBA copy the text values into where I want it, then delete the table. This also cannot be the only solution.

My version of Excel does not have the Legacy Text option to simply do it with regular text and everything.

Is there a way to import data from a text file into a sheet directly, no styles, tables or formatting?

Answer 1

• OpenText open the text file in a new window, but you can copy the sheet to your workbook and close it.

Sub Demo()
    Dim curWK As Workbook
    Set curWK = ActiveWorkbook ' or ThisWorkbook
    Workbooks.OpenText Filename:="C:\textexample.txt", _
        Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
        Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
          Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
          Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), _
          Array(11, 1), Array(12, 1), Array(13, 1)), _
        TrailingMinusNumbers:=True
    With ActiveWorkbook
        .Sheets(1).Copy after:=curWK.Sheets(curWK.Sheets.Count)
        .Close
    End With
    ' Your code to process the imported data
End Sub

---

• Use QueryTable to import text file

Option Explicit
Sub QueryTablesSample()
    Dim oSht As Worksheet
    Dim oQt As QueryTable
    Dim fileName As String
    ' Specify the file path of the text file
    fileName = "d:\temp\data.csv"
    ' Specify the worksheet where you want to import the data
    Set oSht = ThisWorkbook.Sheets("Sheet2")
    ' Clear existing data (optional)
    oSht.Cells.Clear
    ' Add a QueryTable to the worksheet
    Set oQt = oSht.QueryTables.Add(Connection:="TEXT;" & fileName, Destination:=oSht.Range("A1"))
    ' Specify properties of the QueryTable
    With oQt
        .TextFileParseType = xlDelimited ' Specify the file parse type (e.g., xlDelimited, xlFixedWidth)
        .TextFileCommaDelimiter = True ' Specify delimiter (e.g., comma, tab, semicolon)
        .TextFileColumnDataTypes = Array(2, 1, 1) ' Specify column data types (e.g., xlGeneralFormat)
        .Refresh ' Refresh the QueryTable to import data
    End With
    ' Clean up
    Set oQt = Nothing
    Set oSht = Nothing
End Sub

How to access From Text(legacy)?

• File > Option > From Text(legacy)