Board logo

标题: [原创] VBS实现工作表按指定表头自动分表 [打印本页]

作者: batman    时间: 2012-12-30 17:08     标题: VBS实现工作表按指定表头自动分表

本帖最后由 batman 于 2012-12-30 17:12 编辑

在我们实际工作中经常遇到将工作表按某一表头字段分开的情况,我们一般的做法是先按指定表头排序然后分段复制粘贴出去,不但麻烦还很容易搞错。下面的VBS脚本就是实现的工作表按指定表头(由用户选择)自动分表功能。需要的朋友只要将要操作的工作表拖放到脚本文件上即可轻松实现工作表分表(暂时只适用于xp系统):
  1. '拖动工作表至VBS脚本实现按指定表头自动分表
  2. On Error Resume Next
  3. If WScript.Arguments(0) = "" Then WScript.Quit
  4. Dim objExcel, ExcelFile, MaxRows, MaxColumns, SHCount
  5. ExcelFile = WScript.Arguments(0)
  6. If LCase(Right(ExcelFile,4)) <> ".xls" And LCase(Right(ExcelFile,4)) <> ".xls" Then WScript.Quit
  7. Set objExcel = CreateObject("Excel.Application")
  8. objExcel.Visible = False
  9. objExcel.Workbooks.Open ExcelFile
  10. '获取工作表初始sheet总数
  11. SHCount = objExcel.Sheets.Count
  12. '获取工作表有效行列数
  13. MaxRows = objExcel.ActiveSheet.UsedRange.Rows.Count
  14. MaxColumns = objExcel.ActiveSheet.UsedRange.Columns.Count
  15. '获取工作表首行表头列表
  16. Dim StrGroup
  17. For i = 1 To MaxColumns
  18.   StrGroup = StrGroup & "[" & i & "]" & vbTab & objExcel.Cells(1, i).Value & vbCrLf
  19. Next
  20. '用户指定分表表头及输入性合法判断
  21. Dim Num,  HardValue
  22. Num = InputBox("请输入分表表头的序号" & vbCrLf & StrGroup)
  23. If Num <> "" Then
  24.   Num = Int(Num)
  25.   If Num > 0 And Num <= MaxColumns Then
  26.     HardValue = objExcel.Cells(1, Num).Value
  27.     Else
  28.     objExcel.Quit
  29.     Set objExcel = Nothing
  30.     WScript.Quit
  31.   End If
  32.   Else
  33.   objExcel.Quit
  34.   Set objExcel = Nothing
  35.   WScript.Quit
  36. End If
  37. '获取分表表头值及分表数
  38. Dim ValueGroup : j = 0
  39. Dim a() : ReDim a(10000)
  40. For i = 2 To MaxRows
  41.   str = objExcel.Cells(i, Num).Value
  42.   If InStr(ValueGroup, str) = 0 Then
  43.     a(j) = str
  44.     ValueGroup = ValueGroup & str & ","
  45.     j = j + 1
  46.   End If
  47. Next
  48. ReDim Preserve a(j-1)
  49. '创建新SHEET并以指定表头值命名
  50. For i = 0 To UBound(a)
  51.   If i + 2 > SHCount Then objExcel.Sheets.Add ,objExcel.Sheets("sheet" & i + 1),1,-4167
  52. Next
  53. For i = 0 To UBound(a)
  54.   objExcel.Sheets("sheet" & i + 2).Name = HardValue & "_" & a(i)
  55. Next
  56. '分表写数据
  57. For i = 1 To MaxRows
  58.   For j = 1 To MaxColumns
  59.     objExcel.sheets(1).Select
  60.     str = objExcel.Cells(i,j).Value
  61.     If i = 1 Then
  62.       For k = 0 To UBound(a)
  63.         objExcel.sheets(HardValue & "_" & a(k)).Select
  64.         objExcel.Cells(i,j).Value = str
  65.         objExcel.Cells(1, MaxColumns + 1).Value = 1
  66.       Next
  67.       Else
  68.       objExcel.sheets(HardValue & "_" & objExcel.Cells(i,Num).Value).Select
  69.       If j = 1 Then x = objExcel.Cells(1, MaxColumns + 1).Value + 1
  70.       objExcel.Cells(x ,j).Value = str
  71.       If j = MaxColumns Then objExcel.Cells(1, MaxColumns + 1).Value = x
  72.     End If
  73.   Next
  74. Next
  75. For i = 0 To UBound(a)
  76.   objExcel.sheets(HardValue & "_" & a(i)).Select
  77.   objExcel.Cells(1, MaxColumns + 1).Value = ""
  78. Next
  79. objExcel.ActiveWorkbook.Save
  80. objExcel.Quit
  81. Set objExcel = Nothing
  82. WScript.Echo "提示:对" & ExcelFile & "的分表操作完成"
复制代码





欢迎光临 批处理之家 (http://bbs.bathome.net/) Powered by Discuz! 7.2