VBA Excel Loop to import text file

0
votes

I have a very simple vba macro that I'm trying to use to import a text file into excel. The text file has a given name, and only a single column of data with a variable number of rows.

What I need to do is 1) Read the file name from a specific cell in a specific worksheet 2) Make excel import all rows of data from the file into a single column.

This is what I have so far:

Sub Import()

fname = Range("E3")
datadir = Application.ActiveWorkbook.Path
fpath = datadir & "\" & fname

Open fpath For Input As #1

Do Until EOF(1)
    Line Input #1, LineFromFile
    Range("E3") = LineFromFile
Loop

Close #1
End Sub

It DOES open the text file and import data using the file name entered in Cell E3, but it prints only the very last value from the data file. If I run the debugger with the spreadsheet open in the background, I can see the

And raw data and desired out put examples: Text file contents:

5
4.24E-09
15
4.32E-09
25
4.41E-09
35
4.49E-09
45
4.58E-09
55
4.67E-09

and an example of what I'm trying to get the final spreadsheet to look like. Ideally I would like to use a single macro to import several files at a time with just one click of the "Import" button. If I can just get help with the initial import loop though I'll be grateful!

2
excelcompiler-errorsvba

2 Answers

0
votes

In your loop you only ever repeatedly assign the new file line value you have just read, to cell E3. If the file has 100 lines, when it finishes it will have overwritten the value in E3 99 times, leaving you with just the last value it read, in E3

Make some extra code that changes E3 to E4, E5 etc.

Hints:

  • assign a numeric variable outside of the loop, to 3.
  • Inside the loop make sure you increment the variable by one each time.
  • Build the cell reference to assign the file value to, by string concatenation to get your numeric variable onto the end of the string "E". This will give you (on each pass of the loop) a different, rising value like "E3", "E4","E5" ...
0
votes

You are assigning each lines value to the same cell. Simply, increment a variable (x) every time a line is read and use the Range.Offset([Row],[Column]) property to write the new line to offset of Range("E3").

Range("E3").Offset(x) = LineFromFile

Sub Import()
    Dim x As Long

    fname = Range("E3")
    datadir = Application.ActiveWorkbook.path
    fpath = datadir & "\" & fname

    Open fpath For Input As #1

    Do Until EOF(1)
        Line Input #1, LineFromFile
        Range("E3").Offset(x) = LineFromFile
        x = x + 1
    Loop

    Close #1
End Sub