VBS操作Excel粘贴，复制

基本操作命令

Excel获取上级文件夹名

route = createobject("Scripting.FileSystemObject").GetFile(Wscript.ScriptFullName).ParentFolder.Path'获取VBS当前路径'
w = LastOne(route,"\")'得到上级目录的文件夹名'
msgbox w'显示分割出来的文件夹名'

Function LastOne(Str,splitStr)
	'输入字符和分隔符，得到最后一部分
	LastOne = right(Str,len(Str)-InStrRev(Str,splitStr))
End Function
1
2
3
4
5
6
7
8

Excel 获取当前路径文件夹名

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
For Each oSubFolder In oSubFolders
	WScript.Echo oSubFolder.Path
Next
1
2
3
4
5
6
7
8

匹配Excel文件名

链接: 代码来源

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
1
2
3
4
5
6
7
8
9
10
11
12
13

使用"[a-zA-Z]+_[0-9]+.[a-zA-Z]"来匹配形如book_123.xlsx这类的文件名，用法如下：

RegExpTest("[a-zA-Z]+\_[0-9]+.[a-zA-Z]", oSubFile.name)
1

Excel的基本操作命令

1、打开一个Excel文件book1.xlsx

Dim oEecel,oWb
Set oExcel= CreateObject("Excel.Application") 
Set oWb = oExcel.Workbooks.Open("D:\book1.xlsx") 
1
2
3

2、选择sheet1

Set oSheet = oWb.Sheets("sheet1")
1

3、取表格B2的值

oSheet.Range("B2").Value
1

Set oExcel= CreateObject("Excel.Application") 
Set oWb = oExcel.Workbooks.Open("E:\其他\新装电话表.xls") 
Set oSheet = oWb.Sheets("Sheet1") 
MsgBox oSheet.Range("B2").Value '#提取单元格B2内容 
1
2
3
4

创建txt,并写入一行文字，然后关闭

dim fso,txtfile
set fso = createobject("scripting.filesystemobject")  '定义一个对象
set txtfile = fso.createtextfile("d:\log.txt",true) '创建文件
txtfile.writeline("hello,world")  '写入一行文字
txtfile.close
1
2
3
4
5