batman

此帖为示例：
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
　　因为本人日常要处理大量的数据，为了提高工作效率，本人经常自己编写一些批处理和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"
复制代码

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

yuihi

功能：对200W行的TXT进行汇总，计算相同行的数量，可用于去除重复的行。
批处理实现优势：可处理大量数据（EXCEL2010最多200W行）；速度更快；可制成定时任务。
代码如下：

@echo off
set num=-1
cd.>result.txt
>>result.txt echo 小区号	人数
setlocal enabledelayedexpansion
for /f %%i in (vlr11.txt) do (
    set /a num+=1
    set second=!first!
    set first=%%i
    if not "!second!"=="" if !second! neq !first! (>>result.txt echo !second!	!num!&set num=0)
)
set /a num+=1
>>result.txt echo %first%	%num%
ren result.txt "%date%".xls
del /q vlr11.txt
复制代码

代码描述：将VLR11.TXT进行汇总生成当前日期为名字的XLS文件。取第一行赋值FIRST,将FIRST传递SECOND，再取下一行赋值FIRST。将FIRST与SECOND进行比较，即当前行与前一行进比较，相同继续；不同输入至RESULT.TXT。

上面的代码多次使>>，导致速度很慢（前两天在批处理论坛上找到原因，非常感谢论坛的帮助及受益）。做了小小的修改，时间由40多分钟减至3分钟。代码如下：

@echo off
set num=-1
cd.>result.txt
>>result.txt echo 小区号	人数
setlocal enabledelayedexpansion
(for /f %%i in (vlr11.txt) do (
    set /a num+=1
    set second=!first!
    set first=%%i
    if not "!second!"=="" if !second! neq !first! ( echo !second!	!num!&set num=0)
))>>result.txt
set /a num+=1
>>result.txt echo %first%	%num%
ren result.txt "%date%".xls
del /q vlr11.txt
复制代码

cjiabing

这两天我想重新整合我的智能菜单，让它变成一个资源管理器，可以管理文件，也可以管理文件内容。

Batcher

回复 18# cjiabing


期待ing

copp_x

之前写过一个脚本 vbs批量修改文件名的 现在拿出分享
原文见于：http://x0day.sinaapp.com/?post=28

'author:DM_,date:2012-1-27
'blog :http://x0day.sinaapp.com
Dim fso,folderPath,folders,SubFolders
folderPath = "c:\temp" '这里是需要批量修改文件名的文件夹的位置
Set fso = CreateObject("scripting.filesystemobject")

fileName_change folderPath 

Sub fileName_change(folderPath)
Set folders = fso.GetFolder(folderPath)
Set subFolders = folders.SubFolders
Set files = folders.Files

For Each file In files
fileName = file.Name
change_name = InputBox (file.Path,,filename)
If Not change_name = filename Then
     If fso.FileExists(change_name) Then
        file.Name = change_name
     
     MsgBox "已将"&filename&_
            " 修改为："&change_name 
     Else MsgBox "file exists!"
     End If 

End If 

Next
For Each folder In SubFolders'递归搜索
fileName_change(folder.Path)
Next
End Sub
复制代码

代码有些粗糙，未对使用过程中inputbox和msgbox返回值进行处理。见谅

yuihi

语言：VBS
背景：SecureCRT（用于TELET，相信搞网维的人都很熟悉）
功能介绍：该段代码为我日常工作时所用，每天晚上从两台交换机中取出数据，存入表格及保存LOG文件。功能很简单，代码只实现了每晚自动取，懒人会很感兴趣。
流程介绍：对CRT进行指令输入，截取结果，存入表格或LOG；也可以用EXCEL中或TXT中取数据供CTR输入，然后得到结果。如果在公网，配合上飞信接口的VBS代码，实时监控，会给你有意想不到的结果。这段代码首先输入指令，得到结果，截取字符，存入EXCEL，并将所有的LOG，以当天为文件名存入LOG文件。
缺点：代码一次写完，实现了结果，就没有修改，未分模块，可读性差，本想全部加上注释，因工作忙，也没有很大的意义。我将控制CTR的VBS命令附上，及定制VBS计划任务的批处理，希望对大家的日常办公有所帮助。

'网络的价值,在于你我的共享及经验的分享'
'让你我的努力促进网络的发展，从而让自己从中受益。尊重作者，尊重原创……'
'                                                 ---------------yuihi---------------2012-3-12
# $language = "VBScript"
# $interface = "1.0"
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

Sub Main
	dingshi=hour(now)
if dingshi<23 then
exit sub
end if
   crt.Screen.Synchronous = True 'CRT可见
   Dim fso, file,chengname,dingshi,zmvi,hlr2,hlr3,hlr4,all,app,wb,ws '定义变量

	chengname=Year(now)&"年"&Month(now)&"月"&Day(now)&"日"'取当前日期赋给变量chengname
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set file = fso.OpenTextFile("D:\漫游用户\" &chengname &".txt", ForAppending, True)

	crt.Screen.Send "***SSG2" & chr(13)
	crt.Screen.WaitForString "ENTER USERNAME < " & chr(8) & " "
	crt.Screen.Send "****" & chr(13)
	crt.Screen.WaitForString "ENTER PASSWORD < " & chr(8) & " "
	crt.Screen.Send "************" & chr(13)
	crt.Screen.WaitForString "< " & chr(8) & " "
	crt.Screen.Send "ZMVI;" & chr(13) & Chr(10)



	crt.Screen.WaitForStrings("COMMAND EXECUTED")
        screenrow = crt.screen.CurrentRow - 2
        readline = crt.Screen.Get(screenrow, 14, screenrow, 20 )
	zmvi=readline


	crt.Screen.Send "ZMVF::HLR=861376649000;" & chr(13) & Chr(10)
	crt.Screen.WaitForStrings("COMMAND EXECUTED")
        screenrow = crt.screen.CurrentRow - 2
        readline = crt.Screen.Get(screenrow, 8, screenrow, 14 )
	hlr2=readline

  
	crt.Screen.Send "ZMVF::HLR=861387930000;" & chr(13)
	crt.Screen.WaitForStrings("COMMAND EXECUTED")
        screenrow = crt.screen.CurrentRow - 2
        readline = crt.Screen.Get(screenrow, 8, screenrow, 14 )
	hlr3=readline


	crt.Screen.Send "ZMVF::HLR=861518034000;" & chr(13)
	crt.Screen.WaitForStrings("COMMAND EXECUTED")
        screenrow = crt.screen.CurrentRow - 2
        readline = crt.Screen.Get(screenrow, 8, screenrow, 14 )
	hlr4=readline


	crt.Screen.Send "ZMVF::LAC=ALL,;" & chr(13)
	crt.Screen.WaitForStrings("COMMAND EXECUTED")
        screenrow = crt.screen.CurrentRow - 2
        readline = crt.Screen.Get(screenrow, 8, screenrow, 14 )
	all=readline


  Set app = CreateObject("Excel.Application")
  app.DisplayAlerts=FALSE
  app.visible=FALSE 
  Set wb = app.Workbooks.Open("D:\漫游用户\GS2漫游数每日登记表.xls") 
  Set ws = wb.Worksheets(1)
  app.WorkSheets(1).Activate

	app.range("A2").Value ="GS2"
	app.range("B2").Value =now
	app.range("C2").Value =zmvi
	app.range("D2").Value =hlr2
	app.range("E2").Value =hlr3
	app.range("F2").Value =hlr4
	app.range("G2").Value =all
	app.range("H2").Value ="=G2-F2-E2-D2"
	app.rows(2).insert
app.save
wb.close
app.quit
set app=nothing
set wb=nothing
set ws=nothing

	crt.Screen.Send "Z;"
	crt.Screen.Send chr(13)
	crt.Screen.WaitForString "< " & chr(8) & " "
	crt.Screen.Send "Z;" & chr(13)
	crt.Screen.WaitForString "srwork >"
	crt.Screen.Send "***SSG1" & chr(13)
	crt.Screen.WaitForString "ENTER USERNAME < " & chr(8) & " "
	crt.Screen.Send "****" & chr(13)
	crt.Screen.WaitForString "ENTER PASSWORD < " & chr(8) & " "
	crt.Screen.Send "*******" & chr(13)
	crt.Screen.WaitForString "< " & chr(8) & " "
	crt.Screen.Send "ZMVI;" & chr(13) & Chr(10)
	crt.Screen.Send "ZMVF::HLR=861376649000;" & chr(13)
	crt.Screen.Send "ZMVF::HLR=861387930000;" & chr(13)
	crt.Screen.Send "ZMVF::HLR=861518034000;" & chr(13)
	crt.Screen.Send chr(13)
	crt.Screen.Send "ZMVF::LAC=ALL,;" & chr(13)

  	crt.Screen.WaitForString Chr(10)
	crt.Screen.Send "Z;"
  Dim waitStrs
  waitStrs = Array( Chr(10), "Z;" )
  Dim row, screenrow, readline, items
  row = 1
  Do
      While True
      result = crt.Screen.WaitForStrings( waitStrs )
      If result = 2 Then
        Exit Do
      End If
      screenrow = crt.screen.CurrentRow - 1
      readline = crt.Screen.Get(screenrow, 1, screenrow, 50 )    
      file.Write readline & vbCrLf
    Wend
  Loop

	crt.Screen.Send chr(13)
	crt.Screen.WaitForString "< " & chr(8) & " "
	crt.Screen.Send "Z;" & chr(13)
	'crt.Screen.WaitForString "< " & chr(8) & " "
	crt.Screen.WaitForString "srwork >"
	crt.Screen.Send "***SSG2" & chr(13)
	crt.Screen.WaitForString "ENTER USERNAME < " & chr(8) & " "
	crt.Screen.Send "****" & chr(13)
	crt.Screen.WaitForString "ENTER PASSWORD < " & chr(8) & " "
	crt.Screen.Send "************" & chr(13)
	crt.Screen.WaitForString "< " & chr(8) & " "
	crt.Screen.Send "ZMVI;" & chr(13) & Chr(10)
	crt.Screen.Send "ZMVF::HLR=861376649000;" & chr(13)
	crt.Screen.Send "ZMVF::HLR=861387930000;" & chr(13)
	crt.Screen.Send "ZMVF::HLR=861518034000;" & chr(13)
	crt.Screen.Send chr(13)
	crt.Screen.Send "ZMVF::LAC=ALL,;" & chr(13)
	crt.Screen.Send "ZZZ;"
  	crt.Screen.WaitForString Chr(10)

  waitStrs = Array( Chr(10), "ZZZ;" )

  row = 1
  Do
      While True
      result = crt.Screen.WaitForStrings( waitStrs )
      If result = 2 Then
        Exit Do
      End If
      screenrow = crt.screen.CurrentRow - 1
      readline = crt.Screen.Get(screenrow, 1, screenrow, 50 )    
      file.Write readline & vbCrLf
    Wend
  Loop
 	crt.Screen.Send chr(13)
 	 crt.screen.synchronous = false
crt.Screen.WaitForStringS "srwork >"
crt.session.disconnect
CRT.QUIT
End Sub
复制代码

SecuerCRT的VBS命令集：

TabCount = crt.GetTabCount() ‘获取当前已打开的标签总数
Set tab = crt.GetTab(index)获取相应索引的标签
tab = crt.session.Connectintab("/s IP专网/AR")’打开会话中的标签
tab.Session.Connect标签连接
tab.activate激活标签
tab.Session.Connected = True判断标签会话是否已连接
Name =  tab.Caption获取标签的名称
crt.session.Connect "/telnet 10.178.153.194 8023" TELNET方式连接IP加端口
crt.session.disconnect 中断会话连接
CRT.QUIT 退出CRT
crt.sleep 1000 等待1秒
crt.screen.send "show interface description" &vbCr 向屏幕发送指令加回车
crt.Screen.WaitForString "[JXSHR-MC-CMNET-BRAS01-ME60]" 在屏幕上等待字符串,然后再运行下一条
screenrow = crt.screen.CurrentRow – 1 定义屏幕当前行数
readline = crt.Screen.Get(screenrow, 8, screenrow, 14 ) 截取屏幕上相应的字符
复制代码

用批处理定时打开CRT中指令的脚本

"C:\Program Files\SecureCRT\SecureCRT.EXE"    /T /SCRIPT 取BAS利用率.vbs
复制代码

manhua

语言：VBS+批处理
功能介绍：批量登录并配置交换机
流程介绍：用VBS模拟键盘输入命令行，配置全局变量及vlan之间的通信规则，批处理生成VBS脚本，最后开启telnet功能，并调用VBS脚本。

@echo off
echo set sh=CreateObject("WScript.Shell")>2.vbs
(for /l %%a in (4 1 50) do (
    echo WScript.Sleep 300
    echo sh.SendKeys "open 192.168.16.%%a"
    echo WScript.Sleep 300
    echo sh.SendKeys "{ENTER}"
    echo WScript.Sleep 1000
    echo sh.SendKeys "username{ENTER}"
    echo WScript.Sleep 300
    echo sh.SendKeys "password{ENTER}"
    echo WScript.Sleep 300
    echo sh.SendKeys "con t{ENTER}"
    echo WScript.Sleep 300
    echo sh.SendKeys "no aaa accounting network default{ENTER}"
    echo WScript.Sleep 300
    echo sh.SendKeys "end{ENTER}"
    echo WScript.Sleep 300
    echo sh.SendKeys "write{ENTER}"
    echo WScript.Sleep 300
    echo sh.SendKeys "exit{ENTER}"
    echo WScript.Sleep 300
    echo sh.SendKeys "{ENTER}"
    echo WScript.Sleep 300
    echo sh.SendKeys "{ENTER}"
    echo WScript.Sleep 300
    echo sh.SendKeys "{ENTER}"
))>>2.vbs
start telnet 
cscript //nologo 2.vbs
复制代码

ghl621

现在批处理能用舞的地方太少了，不知道楼主能不能提供把批处理转换成可执行文件就好了。

Batcher

回复 25# ghl621


http://www.bathome.net/thread-3981-1-1.html
最后三个工具都行

ghl621

回复 26# Batcher


    老大真行还是你厉害，谢谢了！

yuihi

今天要截止了吧

Batcher

回复 28# yuihi


是的
要抓紧时间了

cjiabing

原来还有时间截止，看来今晚我也试试~

Batcher

回复 30# cjiabing


做完文件管理篇，还有别的篇要做呢。

cjiabing

回复 31# Batcher


    哈哈，继续加油啊！~昨晚尝试的批处理资源管理器：http://www.bathome.net/thread-16110-1-1.html ，尚未完成，今晚继续。
    话说昨晚论坛突然登陆失败，害得我只好睡觉了。