keemain2
03-16-2007, 01:29 AM
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
----------------------------------------------
I 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
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
----------------------------------------------
I 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