Excel 2003 importing data from multiple .txt files

hi
i have one folder containing my base.xls file and a variable number (between 1 and 250) of *.txt files
each txt file has a variable number 1-n records (between 1 and 500)
each txt file has the first 3 rows taken up with rubbish; the fourth row contains the column headings; rows 5-n have the data that i need to capture
each row of data has 7 (columns) elements

base.xls file has the appropriate column headings (7) in row 1

i need a macro to import all data from all text files consecutively into Sheet1 of base.xls.
When the data is imported base.xls_sheet1 it needs to occupy columns C:I ; column B in base.xls needs to be the file name of the originating .txt file (excluding the '.txt' suffix)

is anyone able to help please?
dave

Can you upload 2 text files and then the base.xls that is created by importing those two files? In other words show us the BEFORE (2 text files of different lengths) and the AFTER (the filled in sheet1).

Click GO ADVANCED and use the paperclip icon to post up your files.

sub snb()
  c00="E:\OF\"
  c01=dir(c00 & "*.txt")

  do until c01=""
    open c00 & c01 for input as 1
      c02= replace(input(LOF(1),1),vbcrlf,vbcrlf & replace(c01,".txt",Application.International(xlListSeparator))) 
      c03=c03 & vbcrlf & split(c02,split(c02,vbcrlf)(4))(1)
    close
    c01=dir
  loop

  open c00 & "all.csv" for output as 1
    print 1,c03
  close

  workbooks.open c00 & "all.csv"
End sub

Before I spend any time on this, did you try to adapt the code offered above by SNB? I'm sure he'll be happy to explain/expand on it if you try it.

Give this a try:

Option Explicit

Sub ImportTextFiles()
Dim txtPath As String
Dim txtFile As String
Dim txtName As String
Dim wsBase As Worksheet
Dim FR As Long, NR As Long
  
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'Imported to this worksheet
    Set wsBase = ThisWorkbook.Sheets("Sheet1")

'remember the final \ in this string
    txtPath = "C:\2011\Text\"

'clear existing report
    If wsBase.UsedRange.Rows.Count > 1 Then wsBase.UsedRange.Offset(1).ClearContents
    NR = 2

'Start looping through text files in noted folder
    txtFile = Dir(txtPath & "*.txt")

    Do While Len(txtFile) > 0
        FR = NR
        txtName = Replace(txtFile, ".txt", "")
        ActiveWorkbook.Worksheets.Add
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & txtPath & txtFile, Destination:=ActiveSheet.Range("A1"))
            .Name = txtName
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
        
        ActiveSheet.Range("A3").CurrentRegion.Offset(1).Copy wsBase.Range("C" & FR)
        NR = wsBase.Range("C" & wsBase.Rows.Count).End(xlUp).Row + 1
        wsBase.Range("B" & FR & ":B" & NR - 1) = txtName
        ActiveSheet.Delete              'delete temp sheet
        
        txtFile = Dir                   'get next text filename
    Loop

wsBase.Columns.AutoFit                  'cleanup
Application.ScreenUpdating = True
End Sub

FR = First Row (to enter sheet names)
NR = Next Empty Row (at bottom of current data set)

The DIR() function was "started" at the top of the macro. The next time we use it it simply reapplies the earlier function and moves on to the next file that matches the filter.

The macro given will populate the data into columns C:I, and will fill in the sheet name in column B. The only thing left is the column A which you provided no specifications for. I saw 2000 was in that column in the sample, but no absolute indication where that comes from.


If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.