vbs小脚本01-将文件内txt转录到excel

将脚本同文件夹内的txt文件转录到excel文件内

• txt文件需未utf-8编码

• 脚本文件在哪个目录下，就将此文件夹下的txt转录到excel表格内

• 将代码区域内代码粘贴至本地txt文件，并将文件后缀名更改为.vbs

  'author:胖头鱼
  'time:2022/0421
  Dim objExcel, objwExcel, activitySht, files, TextStream, txtContent, i, cpath, fileName, ext
  Dim wShell
  
  i = 1
  fileName = "结果"
  ext = xlsx
  
  '得到当前文件路径
  Set wShell = WScript.CreateObject("WScript.Shell")
  cpath = wShell.currentDirectory
  '得到EXCEL应用
  Set objExcel = CreateObject("Excel.Application")
  objExcel.Visible = False
  objExcel.Workbooks.Add
  
  '转录
  
  set files = getFiles()
  
  For Each mfile In files
  
      If mfile.Name <> "转录.vbs" Then
  
          txtContent = ReadFile(mfile)
          objExcel.cells(i,2).value = txtContent
          objExcel.cells(i,1).value = mfile.name
          i = i + 2
          txtContent = ""
      End If
  Next
  objExcel.ActiveWorkbook.SaveAs (cpath & "\"& fileName & ext)
  objExcel.ActiveWorkbook.close
  objExcel.quit
  set wShell = nothing
  set objExcel = nothing
  
  MsgBox ("任务结束")
  
  '得到文件对象
  Function getFiles()
      dim fsoForFile
      Set fsoForFile = WScript.CreateObject("scripting.filesystemobject")
      Set fs = fsoForFile.getfolder(cpath)
      set getFiles = fs.files
  End Function
  
  function ReadFile(filePath)
      dim str
      set stm = CreateObject("ADODB.stream")
      stm.type = 2
      stm.mode = 3
      stm.charset = "UTF-8"
      stm.Open
      stm.loadFromfile filePath
      str = stm.readtext
      stm.close
      set stm = nothing
      readfile = str
  end function