VBS 批量读取文件夹内所有的文本到Excel的脚本

'this code is done by kangkang@
option explicit ‘this is optional, but better to use.
dim folderpath,folder
dim fso,file,files
dim filenums
dim filestring()
dim i
dim ii

i=0
folderpath="e:\tddownload\aa\"

'**********************1.to create the filesystemobject object********************************
set fso= createobject("scripting.filesystemobject")'this is the way to create filesystemobjecy
‘这句话在excel vba中也可以如此定义来引用fso!
'scripting是类库的名字,filesystemobject是所引用的对 '象, 说明了此时vba所用的对象不是自带的，而是引用 '外界的。

'**********************2.to create the folder and file object*********************************
if fso.folderexists(folderpath) then
set folder = fso.getfolder(folderpath) 'this set command is neccessary!
set files=folder.files
filenums=files.count
'msgbox filenums
for each file in folder.files
if right(file.name,2)="rm" then
redim preserve filestring(i) 'this is a dynamic array, so we should use the redim command
'be careful of the preserve word, important!!!!
filestring(i)=file.name
'msgbox i & " " & filestring(i)
i=i+1
filenums=i
end if
next
end if

'**********************3.create excel and stroe the file name in it***************************
dim objexcel
dim objworkbook

set objexcel = wscript.createobject("excel.application")
objexcel.workbooks.add
objexcel.visible=true

set objworkbook = objexcel.activeworkbook
for ii=1 to filenums
objworkbook.worksheets(1).cells(ii,1)=filestring(ii-1)
next
objworkbook.worksheets(1).range("a1:a1").columns.autofit
objexcel.displayalerts = false
objworkbook.saveas(folderpath & "xiao.xls")

objworkbook.close()'close the workbook
objexcel.quit()'quit
set fso=nothing

'**********************4.open the files and read the first line.******************************
dim range
dim range_i
dim mfile
dim sline
dim iii

set fso=createobject("scripting.filesystemobject")
set objexcel = wscript.createobject("excel.application")
objexcel.visible=true
objexcel.workbooks.open(folderpath & "xiao.xls")

set objworkbook = objexcel.activeworkbook
set range = objworkbook.activesheet.range("a1:a11")

for range_i=1 to filenums
set mfile=fso.opentextfile(range(range_i).value)
msgbox range_i & " " & range(range_i).value
for iii=1 to 1
sline=mfile.readline
objworkbook.worksheets(1).cells(range_i,2)=sline
next
mfile.close
next
objworkbook.worksheets(1).range("b1:b1").columns.autofit
objexcel.displayalerts = false
objworkbook.saveas(folderpath & "xiao.xls")

objworkbook.close()'close the workbook
objexcel.quit()'quit
set fso=nothing