Solved: I want to add hundreds of TXT files into one XLS file

[keemain2]

I need to add hundreds of TXT files into one XLS file


here is my code:

----------------------------------------------
Sub Macro3()
'
' Macro3 Macro
' 宏由 MS User 录制，时间: 2007-3-16
'
'

With ActiveSheet.QueryTables.Add(Connection:="TEXT;G:\_hqew_homepage\www_sz-ic_net\kcss(1).txt", _
Destination:=Range("A1"))
.Name = "kcss(1)"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = 35
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20

End Sub


----------------------------------------------


Ｉ want to

1.add files from kcss(1).txt to kcss(1000).txt

2.Destination:=Range("A1")) 'step 40
like A1,A41,A81,……,A40001

3.ActiveWindow.ScrollRow = 2 to 20 ' this is first file added in
ActiveWindow.ScrollRow = 42 to 60 ' this is second file added in
ActiveWindow.ScrollRow = 82 to 100 ' this is third file added in
ActiveWindow.ScrollRow = 102 to 120 ' this is fourth file added in

……
ActiveWindow.ScrollRow = 40002 to 40020 ' this is the 1000th file added in

[OBP]

I don't know what the ScrollRow command is for, but for the actual addition of the txt files I would use the FileSearch VBA Function to step through each of the files in the Folder(s).
I assume that the files are all the same approximate length.
You could use a For/Next loop but if a File was missing you would have to account for that, which you do not need to do with FileSearch

[Bob Phillips]

It is better to use Dir than FileSearch. It is more stable, and FileSearch has been dropped in 2007.

[keemain2]

thanks OBP & xld.

cause I don't know VBA well, can someone edit my code for three demands I gave.

[mdmackillop]

Try the following (untested) and let us know how you get on. This is limited to 3 files meantime.
Option Explicit
Sub ImportText()
Dim i As Long
For i = 1 To 3 '1000
With ActiveSheet.QueryTables.Add(Connection:="TEXT;G:\_hqew_homepage\www_sz-ic_net\kcss(" & i & ").txt", _
Destination:=Cells(1 + 40 * (i - 1), 1))
.Name = "kcss(" & i & ")"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 936
.TextFileStartRow = 35
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Next
End Sub

[keemain2]

thanks mdmackillop.

I just testing your code.
It is running perfect on my computer. That is really what I need.

ths again.