VB脚本操作Excel文件

原创
2017/06/16 15:00
阅读数 119
'Convert_To_CSV.vbs
'拖动多个xlsx文件到vbs文件上,自动生成csv文件
Set lstArgs = WScript.Arguments
Set fs = CreateObject("Scripting.FileSystemObject")
CurrentPath = left(wscript.scriptfullname,instrrev(wscript.scriptfullname,"\")-1)

'创建Excel对象
  Set objWS = CreateObject("Excel.application")  
  objWS.application.visible=false
  objWS.application.displayalerts=false

For I = 0 to lstArgs.Count - 1 ' Loop through each file

  Source = lstArgs(I)
  FileName = fs.GetFileName(lstArgs(I))
  FileName = Left(FileName, InStrRev(FileName, ".") )
  Target = CurrentPath & "\" & FileName & "csv"

  set objWB = objWS.Workbooks.Open(Source)

'处理,删除指定列
  objWB.sheets("Sheet1").range("A:A,C:C,D:D,F:F,G:G").delete
  objWB.SaveAs Target, 6
  objWB.Close False

  set objWB = Nothing
    
Next

'销毁Excel对象
  objWS.Application.Quit
  objWS.Quit
  Set objWS = Nothing
'提示处理完毕
if err.number=0 then 
  msgbox "成功",vbinformation
else
  msgbox "失败",vbexclamation
end if

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部