此帖为示例:
------------------------------------------------
因为本人日常要处理大量的数据,为了提高工作效率,本人经常自己编写一些批处理和vbs脚本进行数据的
批量操作。因为这些批处理和vbs脚本在工作中的加入和使用,使得个人的数据处理能力和效率大幅提升,更是
为本人节省了大量的工作时间和精力。下面这段代码就是本人用vbs代码编写的excel自动分表工具,只要你的
excel表数据中最后一列为需要分表的字段,那么将文件拖放到vbs工具上即可实现将原表按分表字段分解成
n个以原文件名+下划线+分表名命名的分表:- If WScript.Arguments(0) = "" Then WScript.Quit
- Set oexcel = CreateObject("excel.application")
- Set fso = CreateObject("scripting.filesystemobject")
- name = fso.GetFile(WScript.Arguments(0)).name
- ext = fso.GetExtensionName(WScript.Arguments(0))
- path = fso.GetFile(WScript.Arguments(0)).parentfolder & "\" & Replace(name, "." & ext, "_")
- oexcel.Visible = False
- oexcel.Workbooks.Open(WScript.Arguments(0))
- oexcel.ActiveWorkbook.sheets(1).activate
- For Each str In oexcel.ActiveSheet.rows(1).value
- If str <> "" Then
- oco = oco + 1
- head = head & oexcel.Cells(1, oco).value & vbTab
- End If
- Next
- arr = Split(head, vbTab)
- For i = o To UBound(arr) - 2
- str = str & arr(i) & vbTab
- Next
- head = str
- For Each str In oexcel.ActiveSheet.columns(1).value
- If str <> "" Then oro = oro + 1
- Next
- For i = 2 To oro
- file = oexcel.Cells(i, oco).value & ".xls"
- If Not fso.FileExists(path & file) Then fso.CreateTextFile(path & file, True, False).Write head & vbCrLf
- For j = 1 To oco - 1
- vbstr = vbstr & oexcel.Cells(i, j) & vbTab
- Next
- fso.OpenTextFile(path & file, 8, True).Write vbstr & vbCrLf : vbstr = ""
- Next
- oexcel.Workbooks.Close
- Set oexcel = Nothing
- MsgBox "ok"
复制代码 ------------------------------------------------ |