Shulou(Shulou.com)06/03 Report--

This article mainly introduces how VBS reads all the text in the folder to Excel in batches. It is very detailed and has certain reference value. Friends who are interested must finish reading it.

The code is as follows:

'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

ITunes 0

FolderPath= "E:\ TDDOWNLOAD\ aa\"

'* * 1.To create the FileSystemObject object**

Set fso= CreateObject ("Scripting.FileSystemObject") 'This is the way to create FileSystemObjecy

This sentence can also be defined in Excel VBA to refer to FSO!

Scripting is the name of the class library, and filesystemobject is the referenced pair, indicating that the object used by VBA at this time is not native, but refers to the outside world.

'* * 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

'The Be careful of the Preserve word, importantless people!

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

The above is all the contents of the article "how to read all the text in a folder to Excel in batch by VBS". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.