CODEWITHSUNDEEP
excelvbatext-filesdelimiterreadfile
Michael
Michael

Reputation: 11

excel vba macro reading text files, one line in separate cell

Text file looks like below

a, John, "2014-2", ...

d, Will, "2016-7" , ...

I want to put element a in row 1, col 1, John in row 1 col 2, d in cell row 2, col 1, etc. Please help. Thanks. below are the code I have

Sub Importdata()
Open "C:\Users\apple\desktop\12345.txt" For Input As #1
r = 0
  Do Until EOF(1)
    Line Input #1, Data
    ActiveCell.Offset(r, 0) = Data
    r = r + 1
  Loop
Close #1
End Sub

Upvotes: 0

Views: 3253

Answers (2)

Stupid_Intern
Stupid_Intern

Reputation: 3450

You can split each line using Split and , as delimiter

Try this it works fine:

Option Explicit

Sub Importdata()

Dim cet
Dim r As Long
Dim Data
Dim wk AS worksheet

Set wk = sheet1

Open "C:\Users\apple\desktop\12345.txt" For Input As #1
r = 1

  Do Until EOF(1)
    Line Input #1, Data
    cet = Split(Data, ",")

    if len(join(cet)) > 0 then        
     wk.Cells(r, 1) = cet(0)
     wk.Cells(r, 2) = cet(1)
    ENd if

     r = r + 1
  Loop
Close #1
End Sub

Upvotes: 2

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

You could use the QueryTables property, importing and parsing the lines in one step. Easiest method is to do this using the Macro Recorder (Using the Data ► Get External Data ► From Text option from the Excel menu), then tweak to suit. In Excel, that would bring up the Text Import wizard, but you can also do it in VBA. Below is an example where I browse for the file, but you can easily hard-code it as you have in your original macro. Also note that I have explicitly declared the workbook and worksheets; you can easily change this if you wish.

EDIT Minor tweaks added for clarification

Option Explicit
Sub ImportData()
    Dim sMyFile As Variant
    Dim WS As Worksheet, WB As Workbook

Set WB = ThisWorkbook
Set WS = WB.Worksheets("sheet1")

sMyFile = Application.GetOpenFilename("Text Files(*.txt), *.txt")
If sMyFile <> False Then

    With WS.QueryTables.Add(Connection:= _
        "TEXT;" & sMyFile, _
        Destination:=WS.Range("$A$1"))
        .Name = "TestText"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

End If

End Sub

Upvotes: 0

Related Questions