VBS和VBA初步（一）

vbs

VBS是基于Visual Basic的脚本语言。VBS的全称是：Microsoft Visual Basic Script Edition。

1.基本语法

1.1 所有变量需要显式声明

Option Explicit

1.2 弹出“你好，世界”对话框

Option Explicit
Msgbox "你好,世界"

1.3 定义一个变量

Option Explicit'所有变量需要显式声明
Dim num 
num = 10
Msgbox "你好,世界给你"&num&"个机会"'弹出“你好，世界给你10个机会”对话框

1.4 加减乘除

Option Explicit'所有变量需要显式声明
Dim num,num2 
num = inputbox ("请输入你的数字","乘法")
num2 = inputbox ("请输入你的数字","乘法")
Msgbox "结果是:"&num*num2'弹出“你好，世界”对话框

1.5 On Error Resume Next

语句作用是跳过出错的语句。继续执行程序

Option Explicit'所有变量需要显式声明
Dim num
num = 1
On Error Resume Next
Msgbox "结果是:"&num/0'弹出“你好，世界”对话框

1.6 On Error Goto 0

后面的程序一旦有错误发生就会提示错误，并结束脚本执行。

Option Explicit'所有变量需要显式声明
Dim num
num = 1
On Error Goto 0
Msgbox "结果是:"&num/0'弹出“你好，世界”对话框

1.粘贴sheet内容到其他sheet功能实现

route = createobject("Scripting.FileSystemObject").GetFile(Wscript.ScriptFullName).ParentFolder.Path
waferID = LastOne(route,"\")
Set oFso = CreateObject("Scripting.FileSystemObject")    
Set oFolder = oFso.GetFolder(route)    
Set oSubFolders = oFolder.SubFolders
Set xlsName = CreateObject("Excel.Application")
xlsName.Visible = False
xlsName.DisplayAlerts = False
set excel1 = xlsName.workbooks.open(oSubFolder.Path & "\" & modelStateName)
	set excel2 = xlsName.workbooks.open(route & "Book_1.xlsx")	
	excel1.Worksheets(1).UsedRange.Copy
	excel2.Sheets(1).Activate'此处操作前需要生成一个新的sheet
	excel2.ActiveSheet.Range("A1").PasteSpecial

2.正则匹配

'函数名：RegExpTest
'功能：正则匹配'
'参数说明：patrn为匹配字符，strng为待匹配的字符串'
Function RegExpTest(patrn, strng)
  Dim regEx, Match, Matches        
  Set regEx = New RegExp         
  regEx.Pattern = patrn         
  regEx.IgnoreCase = True       
  regEx.Global = True           
  Set Matches = regEx.Execute(strng) 
	if Matches.Count>0 Then
		RegExpTest=1
	else
		RegExpTest=0
	end if
End Function

3.取字符串分割后的最后一部分

Function LastOne(Str,splitStr)
	'输入字符和分隔符，得到最后一部分
	LastOne = right(Str,len(Str)-InStrRev(Str,splitStr))
End Function

常用正则表达式"B[a-zA-Z]+_[0-9]+.[a-zA-Z]"用于匹配Book_123.xlsx

4.复制字符串到剪贴板

str = "字符串"
Set ws = wscript.Crecteobject(wscript.shell)
ws.run "mshta vbscript:clipboardData.SetData("+""""+"text"+""""+","+""""&str&""""+"")(close),0,true

5.打开一个txt文件，并将内容复制到Excel中

Sub test() 
    Dim objStream, strData, Arr1, i As Long 
    Dim pathX As String, strX As String, N As Long '第一部分选中需要读取的txt文件 
    With Application.FileDialog(msoFileDialogFilePicker) 
        With .Filters 
            .Clear 
            .Add "txt文件", "*.txt" 
        End With .AllowMultiSelect = False 
        If Not .Show Then 
            Exit Sub 
            pathX = .SelectedItems(1) 
     End With '第二部分读取UTF-8格式的txt文件内容保存至strData变量中 
     '创建ADODB.Stream对象 
     Set objStream = CreateObject("ADODB.Stream") 
     '指定字符集为UTF-8 
      objStream.Charset = "utf-8" '使用Open方法打开流对象 
      objStream.Open 
       '加载文本文件 
        objStream.LoadFromFile (pathX) 
        '使用ReadText返回文件内容 
        strData = objStream.ReadText() 
        '第三部分用Split函数按行来分割字符串存至数组中，处理数据，然后通过循环填写至sheet表中 
        Arr1 = Split(strData, vbCrLf) 
        objStream.Close 
        Set objStream = Nothing 
        N = 1 
        For i = 0 To UBound(Arr1) 
            strX = Arr1(i) 
            If strX <> "" Then 
                Range("A" & N) = strX 
            End If 
            N = N + 1 
        Next 
End Sub

vba-PPT

1.插入图片

ppt.Presentations(1).Slides(1).Shapes(1).Fill.UserPicture("C:\Users\Administrator\Desktop\testforAutopadding\" & "red.jpg")

2.向PPT表格中插入图片

With ppt.Presentations(1).Slides(1).Shapes(2).Table 	
	.Cell(1,1).Shape.Fill.UserPicture("C:\Users\Administrator\Desktop\testforAutopadding\" & "red.jpg")
End With

3.表格赋值

'往PPT第二页第二个形状表格的（1，1）单元格中赋值为123
With ppt.Presentations(1).Slides(2).Shapes(2)
	.Table.Cell(1, 1).Shape.TextFrame.TextRange.Text = “123”
End With