首先建立一个JS脚本文件,并保存为helloworld.wsc
脚本内容如下:
<Component>
<registration ProgId=hello.world />
<public>
<property name=dynamicarray />
<method name=CreateVBArray>
<parameter name=x />
<parameter name=y />
<parameter name=z />
</method>
</public>
<SCRIPT LANGUAGE="VBScript">
Function CreateVBArray(excel,y,z)
Dim i,j,a,b
Dim strArr()
a=y
b=z
redim strArr(a,b)
For i=0 to y
For j=0 to z
strArr(i,j) = 5
Next
Next
scol = GetCellColumnNumber( z )
excel.range("a1:" + scol + CStr(y) ) = strArr
End Function
'下面这个函数是转化EXCEL列数字为字母,譬如 27 = "AA"列
Function GetCellColumnNumber(iColumnCount)
iColumnCount = iColumnCount - 1
If iColumnCount < 26 Then
GetCellColumnNumber = Chr(iColumnCount + Asc("A"))
Else
GetCellColumnNumber = Chr(iColumnCount / 26 - 1 + Asc("A")) + Chr(iColumnCount Mod 26 + Asc("A"))
End If
End Function
</script>
</Component>
//保存文件之后,右击文件并点击注册!注册成功之后该脚本已经做为一个组件注册到系统中!
接下来说说PB对它的调用//
OleObject JsComponent
JsComponent = create OleObject
li_conn = JsComponent.connecttonewobject( "hello.world" )
If li_conn = 0 Then
Jscomponent.CreateVBArray( excel,5,53 )
End If
很简单吧 PB就这么实现了动态二维数组粘贴excel区域的操作