-
VBA自己看的 [2016年05月02日]
2016-05-02 17:09:36
Public WSBatchSet As WorksheetPublic RowsCount As IntegerPublic dicExcuteLine As ObjectConst ForReading = 1Const ForWriting = 2Const ForAppending = 8Sub Main()Application.ScreenUpdating = FalseCall InitApplication.ScreenUpdating = TrueMsgBox "Done~"End SubSub Init()Dim CountRange As RangeSet WSBatchSet = Worksheets("SetFlag")Set CountRange = WSBatchSet.Range("C:C")RowsCount = Application.WorksheetFunction.CountA(CountRange)Set dicExcuteLine = CreateObject("Scripting.Dictionary")End Sub'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''写入文件'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Function writefile(ByVal fPath As String, ByVal pName As String, ByVal pValue As String)'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Dim fso As Object, f As ObjectSet fso = CreateObject("scripting.filesystemobject")Set f = fso.opentextfile(fPath, ForWriting, True)With f.writeline pName.writeline pValue.CloseEnd WithSet f = NothingSet fso = NothingEnd Function'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''检查文件路径是否存在'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Function chkIsFilePathExist(ByVal fPath As String) As Boolean'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''Dim fso As ObjectSet fso = CreateObject("scripting.filesystemobject")If fso.fileexists(fPath) ThenchkIsFilePathExist = TrueElsechkIsFilePathExist = FalseEnd IfSet fso = NothingEnd Function -
日期正则式(测试中……)
2012-12-17 00:04:37
检查日期格式和内容是否正确日期格式: YYYY-MM-DD日期范围: 0000-01-01~9999-12-31思路:将日期进行等价类划分,日期格式加上 ^ 和 $ 进行起始和结束的强制定义一、特殊日期,闰年2月(XXXX-02-29),分为两种:1、小闰年 不以“00”结尾的年份,被4整除的。转化正则式为:((^\d\d[13579][26])|(^\d\d[2468][048])|(^\d\d0[48]))-02-29$2、大闰年 以“00”结尾的年份, 被400整除的。转化正则式为:((^[02468][048])|(^[13579][26]))00-02-29$二、常规日期,(无视年份)分为三种:1、常规大月 1,3,5,7,8,10,12 (31天)。转化正则式为:^\d{4}-((0[13578])|(1[02]))-((0[1-9]$)|([12]\d$)|(3[01]$))2、常规小月 4,6,9,11 (30天)。转化正则式为:^\d{4}-((0[469])|(11))-((0[1-9]$)|([12]\d$)|(30$))3、常规2月 2 (28天)。转化正则式为:^\d{4}-02-((0[1-9]$)|(1\d$)|(2[0-8]$))组合以上五种正则式,便可得到最终正则式:(((^\d\d[13579][26])|(^\d\d[2468][048])|(^\d\d0[48]))-02-29$)|(((^[02468][048])|(^[13579][26]))00-02-29$)|(^\d{4}-((0[13578])|(1[02]))-((0[1-9]$)|([12]\d$)|(3[01]$)))|(^\d{4}-((0[469])|(11))-((0[1-9]$)|([12]\d$)|(30$)))|(^\d{4}-02-((0[1-9]$)|(1\d$)|(2[0-8]$)))当然,这只是简单的拼接而已,未考虑到正则式的运算效率。 -
清理Windows系统垃圾的批处理代码
2011-08-03 22:29:58
======就是下面的文字(这行不用复制)=============================
@echo off
echo 正在清除系统垃圾文件,请稍等......
del /f /s /q %systemdrive%\*.tmp
del /f /s /q %systemdrive%\*._mp
del /f /s /q %systemdrive%\*.log
del /f /s /q %systemdrive%\*.gid
del /f /s /q %systemdrive%\*.chk
del /f /s /q %systemdrive%\*.old
del /f /s /q %systemdrive%\recycled\*.*
del /f /s /q %windir%\*.bak
del /f /s /q %windir%\prefetch\*.*
rd /s /q %windir%\temp & md %windir%\temp
del /f /q %userprofile%\cookies\*.*
del /f /q %userprofile%\recent\*.*
del /f /s /q "%userprofile%\Local Settings\Temporary Internet Files\*.*"
del /f /s /q "%userprofile%\Local Settings\Temp\*.*"
del /f /s /q "%userprofile%\recent\*.*"
echo 清除系统LJ完成!
echo. & pause
=====到这里为止(这行不用复制)============================================== -
[VBS] 获取windows下进程信息 [转]
2010-11-08 21:59:53
没时间验证,
不过可以通过遍历进程的内容来判断哪些进程可以kill掉
获取本机当前所有进程信息,代码如下:
strComputer ="."
Set bjWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery("Select * from Win32_PerfFormattedData_PerfProc_Process",,48)
For Each objItem in colProcess
if objItem.Name <> "Idle" and objItem.Name <> "_Total" then
print objItem.Name & ":"& objItem.PercentProcessorTime
end if
Next -
[VBS] Excel文件内容控制 [转]
2010-11-08 21:54:03
没时间验证,先留着
(一) 使用动态创建的方法
首先创建 Excel 对象,使用ComObj:
oExcel = CreateObject( "Excel.Application" )
1) 显示当前窗口:
oExcel.Visible = True2) 更改 Excel 标题栏:
oExcel.Caption = "应用程序调用 Microsoft Excel"3) 添加新工作簿:
oExcel.WorkBooks.Add4) 打开已存在的工作簿:
oExcel.WorkBooks.Open( "C:\Excel\Demo.xls" )5) 设置第2个工作表为活动工作表:
oExcel.WorkSheets(2).Activate
或
oExcel.WorksSheets( "Sheet2" ).Activate6) 给单元格赋值:
oExcel.Cells(1,4).Value = "第一行第四列"7) 设置指定列的宽度(单位:字符个数),以第一列为例:
oExcel.ActiveSheet.Columns(1).ColumnsWidth = 58) 设置指定行的高度(单位:磅)(1磅=0.035厘米),以第二行为例:
oExcel.ActiveSheet.Rows(2).RowHeight = 1/0.035 ' 1厘米9) 在第8行之前插入分页符:
oExcel.WorkSheets(1).Rows(8).PageBreak = 110) 在第8列之前删除分页符:
oExcel.ActiveSheet.Columns(4).PageBreak = 011) 指定边框线宽度:
oExcel.ActiveSheet.Range( "B3:D4" ).Borders(2).Weight = 3
1-左 2-右 3-顶 4-底 5-斜( \ ) 6-斜( / )12) 清除第一行第四列单元格公式:
oExcel.ActiveSheet.Cells(1,4).ClearContents13) 设置第一行字体属性:
oExcel.ActiveSheet.Rows(1).Font.Name = "隶书"
oExcel.ActiveSheet.Rows(1).Font.Color = clBlue
oExcel.ActiveSheet.Rows(1).Font.Bold = True
oExcel.ActiveSheet.Rows(1).Font.UnderLine = True14) 进行页面设置:
a.页眉:
oExcel.ActiveSheet.PageSetup.CenterHeader = "报表演示"
b.页脚:
oExcel.ActiveSheet.PageSetup.CenterFooter = "第&P页"
c.页眉到顶端边距2cm:
oExcel.ActiveSheet.PageSetup.HeaderMargin = 2/0.035
d.页脚到底端边距3cm:
oExcel.ActiveSheet.PageSetup.HeaderMargin = 3/0.035
e.顶边距2cm:
oExcel.ActiveSheet.PageSetup.TopMargin = 2/0.035
f.底边距2cm:
oExcel.ActiveSheet.PageSetup.BottomMargin = 2/0.035
g.左边距2cm:
oExcel.ActiveSheet.PageSetup.LeftMargin = 2/0.035
h.右边距2cm:
oExcel.ActiveSheet.PageSetup.RightMargin = 2/0.035
i.页面水平居中:
oExcel.ActiveSheet.PageSetup.CenterHorizontally = 2/0.035
j.页面垂直居中:
oExcel.ActiveSheet.PageSetup.CenterVertically = 2/0.035
k.打印单元格网线:
oExcel.ActiveSheet.PageSetup.PrintGridLines = True15) 拷贝操作:
a.拷贝整个工作表:
oExcel.ActiveSheet.Used.Range.Copy
b.拷贝指定区域:
oExcel.ActiveSheet.Range( "A1:E2" ).Copy
c.从A1位置开始粘贴:
oExcel.ActiveSheet.Range.( "A1" ).PasteSpecial
d.从文件尾部开始粘贴:
oExcel.ActiveSheet.Range.PasteSpecial16) 插入一行或一列:
a. oExcel.ActiveSheet.Rows(2).Insert
b. oExcel.ActiveSheet.Columns(1).Insert17) 删除一行或一列:
a. oExcel.ActiveSheet.Rows(2).Delete
b. oExcel.ActiveSheet.Columns(1).Delete18) 打印预览工作表:
oExcel.ActiveSheet.PrintPreview19) 打印输出工作表:
oExcel.ActiveSheet.PrintOut20) 工作表保存:
if not oExcel.ActiveWorkBook.Saved then
oExcel.ActiveSheet.PrintPreview21) 工作表另存为:
oExcel.SaveAs( "C:\Excel\Demo1.xls" )22) 放弃存盘:
oExcel.ActiveWorkBook.Saved = True23) 关闭工作簿:
oExcel.WorkBooks.Close24) 退出 Excel:
oExcel.Quit(二) 使用VBS 控制Excle二维图
1)选择当第一个工作薄第一个工作表
set Sheet=oExcel.Workbooks(1).Worksheets(1)2)增加一个二维图
achart=oSheet.chartobjects.add(100,100,200,200)3)选择二维图的形态
achart.chart.charttype=44)给二维图赋值
set series=achart.chart.seriescollection
range="sheet1!r2c3:r3c9"
series.add range,true5)加上二维图的标题
achart.Chart.HasTitle=True
achart.Chart.ChartTitle.Characters.Text=" Excle二维图"6)改变二维图的标题字体大小
achart.Chart.ChartTitle.Font.size=187)给二维图加下标说明
achart.Chart.Axes(xlCategory, xlPrimary).HasTitle = True
achart.Chart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "下标说明"8)给二维图加左标说明
achart.Chart.Axes(xlValue, xlPrimary).HasTitle = True
achart.Chart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "左标说明"9)给二维图加右标说明
achart.Chart.Axes(xlValue, xlSecondary).HasTitle = True
achart.Chart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "右标说明"10)改变二维图的显示区大小
achart.Chart.PlotArea.Left = 5
achart.Chart.PlotArea.Width = 223
achart.Chart.PlotArea.Height = 108
-
[VBS] Excel文件格式设置
2010-11-08 21:49:01
'''打开已存在文件'''''set ExcelApp = CreateObject("Excel.Application")
set ExcelBook = ExcelApp.Workbooks.Open (filepath)
Set ExcelSheet = ExcelApp.Sheets.Item(1) '''''指定sheet编号'''''设置属性'''''
With ExcelSheet '''''描述性编程 With 与 End With 配对
''''' .Name = "apple" '''''用来指定sheet,也可不用.使用时必须与sheet编号相对应
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''
'''''设置列宽'''''
''''''''''''''''''.Columns("A:A").ColumnWidth = 20
.Columns("B:B").ColumnWidth = 15
.Columns("C:C").ColumnWidth = 10
.Columns("D:D").ColumnWidth = 25
.Columns("E:E").ColumnWidth = 20
.Columns("F:F").ColumnWidth = 10'''''''''''''''''''''''''''''''''''''''''''''''以下设置方式均合法
'
' .Columns("A:f").ColumnWidth = 20 '''''指定从A列至F列的列宽
' .Columns("b").ColumnWidth = 20 '''''指定b列列宽
' .Columns("F").ColumnWidth = 20 '''''指定F列列宽
' .Columns("A:A").ColumnWidth = 20 '''''指定A列列宽
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''设置行高'''''''''很少使用此设置
''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''行高至少>10
.Rows(1).RowHeight = 15
.Rows(2).RowHeight = 20
.Rows(3).RowHeight = 25
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''
'''''设置显示区域的字体类型和大小'''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''字体类型'''''
''''''''''''''''''
.Range("A:A").Font.Name = "Arial"
.Range("B:B").Font.Name = "宋体"
.Range("C:C").Font.Name = "黑体"
.Range("D:D").Font.Name = "新宋体"
.Range("E:E").Font.Name = "Times New Roman"
.Range("F:F").Font.Name = "Times New Roman"'''''''''''''''''''''''''''''''''''''''''''''''以下设置方式均合法
'
' .Range("A:D").Font.Name = "Arial"
' .Range("a:b").Font.Name = "Arial"
' .Range("A:f").Font.Name = "Arial"
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''
'''''字体大小'''''
''''''''''''''''''.Range("A:A").Font.Size = 12
.Range("B:B").Font.Size = 16
.Range("C:C").Font.Size = 20'''''''''''''''''''''''''''''''''''''''''''''''以下设置方式均合法
'
' .Range("A:F").Font.Size = 6
' .Range("a:b").Font.Size = 8
' .Range("A:f").Font.Size = 10
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''
'''''给指定区域赋值'''''
''''''''''''''''''''''''
.Range("A1").Value = "用例名称"
.Range("B1").Value = "测试号码"
.Range("C1").value = "号码类型"
.Range("D1").value = "执行时间"
.Range("E1").value = "检查点描述"
.Range("F1").value = "检查结果"'''''''''''''''''''''''''''''''''''''''''''''''以下设置方式均合法
'
' .Range("a1").Value = "for" '''''A1单元格内容为 for
' .Range("b:b").Value = "test" '''''B列内容都为 test
' .Range("c:A").Value = "test" '''''从A列至C列的内容均为 test
' .Range("a3:F3").Value = "great" '''''第三行,从A至F列均为 great
' .Range("b3:E6").Value = "day" '''''从B3单元格至E6单元格矩形区间范围的值均为 day
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''
'''''合并居中'''''
''''''''''''''''''.Range("B1:C1").Merge '''''B1单元格与C1单元格合并居中
'''''''''''''''''''''''''''''''''''''''''''''''以下设置方式均合法
'
' .Range("a:f").Merge '''''A列至F列合并居中
' .Range("B3:g7").Merge '''''从B3单元格至G7单元格矩形区间合并居中
' .Range("a2:f2").Merge '''''A2,B2,C2,D2,E2,F2单元格合并居中
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''设置文字颜色'''''颜色取值范围:0~56''0为无颜色''
''''''''''''''''''''''''''''''''''''''''''''''''''''.Range("A1").Font.ColorIndex = 5
'''''''''''''''''''''''''''''''''''''''''''''''以下设置方式均合法
'
' .Range("g:G").Font.ColorIndex = 48 '''''G列的字体颜色设为48
' .Range("A:c").Font.ColorIndex = 28 '''''A列至C列的字体颜色设为 28
' .Range("b2:c2").Font.ColorIndex = 3 '''''B2至C2单元格字体颜色设为 3
' .Range("A1:c4").Font.ColorIndex = 9 '''''A1至C4单元格矩形区间字体颜色设为 9
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''
'''''加粗'''''
''''''''''''''
.Range("A1").Font.Bold = True'''''''''''''''''''''''''''''''''''''''''''''''以下设置方式均合法
'
' .Range("g:G").Font.Bold = True '''''G列的字体设为粗体
' .Range("A:C").Font.Bold = True '''''A列至C列的字体设为粗体
' .Range("b3:g5").Font.Bold = True '''''B3至G5单元格矩形区间的字体设为粗体
' .Range("c1:G1").Font.Bold = True '''''C1至G1单元格的字体设为粗体
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''
'''''对齐方式'''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''' 1 常规方式 '''''''''''''''
''''''''''''''' 2 左对齐 '''''''''''''''
''''''''''''''' 3 居中 '''''''''''''''
''''''''''''''' 4 右对齐 '''''''''''''''
''''''''''''''' 5 填充方式 '''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''.Range("A:A").HorizontalAlignment = 4 '''''右边对齐 A列所有行
.Range("B:B").HorizontalAlignment = 1 '''''常规方式
.Range("C:C").HorizontalAlignment = 2 '''''左对齐
.Range("D:D").HorizontalAlignment = 3 '''''居中
.Range("E:E").HorizontalAlignment = 5 '''''填充方式'''''''''''''''''''''''''''''''''''''''''''''''以下设置方式均合法
'
'
'
'
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''
'''''单元格背景'''''取值范围0~56'''0为无颜色''
''''''''''''''''''''''''''''''''''''''''''''''.Range("A1:F1").Interior.ColorIndex = 45
'''''''''''''''''''''''''''''''''''''''''''''''以下设置方式均合法
'
'
'
'
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''' 以下语句可以显示所有颜色对应的数值
''''' For i=1 to 56
''''' A="A"&i
''''' B="c"&i
''''' .Range(A).Interior.ColorIndex = i
''''' .Range(B).Value = i
''''' NEXT
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''单元格边框'''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''' Borders参数 '''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''' 1 左边框 '''''''''''''''
''''''''''''''' 2 右边框 '''''''''''''''
''''''''''''''' 3 上边框 '''''''''''''''
''''''''''''''' 4 下边框 '''''''''''''''
''''''''''''''' 5 左斜线 '''''''''''''''
''''''''''''''' 6 右斜线 '''''''''''''''
''''''''''''''' 不可为0 '''''''''''''''
''''''''''''''' 其余作用未知 '''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''' Linestyle参数 '''''''''''''''
''''''''''''''' 设置边框线条格式 '''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''' 0 不显示 '''''''''''''''
''''''''''''''' 1 实线条显示 '''''''''''''''
''''''''''''''' 其余参数未知 '''''''''''''''
''''''''''''''' 不常用 '''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''.Range("A:F").Borders(1).LineStyle. = 1
.Range("A:F").Borders(2).LineStyle. = 1
.Range("A:F").Borders(3).LineStyle. = 1
.Range("A:F").Borders(4).LineStyle. = 1'''''''''''''''''''''''''''''''''''''''''''''''以下设置方式均合法
'
'
'
'
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End with'''''保存退出'''''
ExcelBook.Save
ExcelBook.Close
Set ExcelBook = nothing
ExcelApp.Quit -
[VBS] Excel文件操作
2010-11-08 21:33:04
'################################################
'## 功 能:打开指定路径的Excel文件 ##
'## 输入参数:Excel路径 ##
'## 输出参数:无 ##
'## 创建日期:2010-01-24 ##
'## 修改日期:2010-01-24 ##
'################################################Function xls_OpenExcelFile(FilePath)
Dim ExcelApp
Dim ExcelBook
Set ExcelApp = CreateObject("Excel.Application") '创建Excel对象
Set ExcelBook = ExcelApp.Workbooks.Open (FilePath) '打开指定路径的Excel表格ExcelApp.Visible = true 'true 为显示excel对象,false为不显示
Set ExcelSheet = ExcelApp.Sheets.Item(1) '选择指定Sheet页'
'单元格操作代码区域
'
'ExcelBook.Save '保存工作表
ExcelApp.Quit '退出Excel对象
Set ExcelApp = nothing '释放Excel对象End Function
'################################################
'## 功 能:创建指定路径的Excel文件 ##
'## 输入参数:Excel文件路径 ##
'## 输出参数:无 ##
'## 创建日期:2010-01-24 ##
'## 修改日期:2010-01-24 ##
'################################################Function xls_CreateExcelFile(FilePath)
Dim ExcelApp
Dim ExcelSheet
Set ExcelApp = CreateObject("Excel.Application") '定义一个excel对象
ExcelApp.Workbooks.Add '新建一个excel实例
ExcelApp.Visible = true '显示excel对象
Set ExcelSheet = ExcelApp.Sheets.Item(1) '获取工作簿的第一个Sheet页
' ExcelApp.Sheets.Item(1).Select '获取工作簿的第一个Sheet页,同样效果''''单元格操作代码区域
'''''例如: ExcelSheet.cells(1,1).value = "abcd"
ExcelApp.ActiveWorkbook.SaveAs FilePath '保存工作表
ExcelApp.Quit '退出Excel对象
Set ExcelApp = nothing '释放Excel对象End Function
-
[BAT] for 语句 纠结啊~~~~
2010-10-30 17:47:05
心寒,自学这么久还是没有掌握 for 的语法
在windows下用的清理CVS目录的批处理脚本
for /r /d %D in (CVS*) do @rmdir /q /s "%D
-
[VBS] 文件操作函数
2010-01-25 00:18:48
'################################################
'## 功 能:获取文件名 ##
'## 输入参数:文件路径 ##
'## 输出参数:文件名 ##
'## 创建日期:2010-01-19 ##
'## 修改日期:2010-01-19 ##
'################################################Function fun_GetBaseName()
Dim FSO
Set FSO=CreateObject("Scripting.FileSystemObject")
fun_GetBaseName=FSO.GetBaseName(fun_GetFileFullPath())
Set FSO=NothingEnd Function
MY GOD,暴雪居然敢让我家的DK骑羊!!!
我的栏目
标题搜索
我的存档
数据统计
- 访问量: 11646
- 日志数: 23
- 图片数: 1
- 文件数: 2
- 书签数: 1
- 建立时间: 2008-12-24
- 更新时间: 2016-05-02