Excel辅助类，VB
Excel辅助类
包含工作薄,单元格,文档属性等操作
代码如下:
'//打开Excel文件函数
'//参数sFilePath:文档全路径+文件名
Sub OpenExcel(ByVal sFilePath As String)
On Error Resume Next
Set oecl = GetObject(, "excel.Application")
If oecl Is Nothing Then
Set oecl = CreateObject("Excel.Application")
End If
'Set oecl = CreateObject("Excel.Application")
oecl.Visible = True
Set xlWork = oecl.Workbooks.Open(sFilePath)
Set xlSheet = oecl.ActiveSheet
Err.Clear
'Set xlSheet = xlWork.Sheets(1)
End Sub
'激活某个工作薄
'参数:sDocName:文档名称(全路径)
Function ActivateWorkBook(ByVal sFullName As String) As Boolean
ActivateWorkBook = False
Dim Wb As Workbook
For Each Wb In oecl.Workbooks
If InStr(1, Wb.FullName, sFullName, 1) Then
Wb.Activate
ActivateWorkBook = True
Exit Function
End If
Next
End Function
'列中最后一行单元格 行号
Function lastRowNo() As Integer
Dim iNum As Integer
Dim rng As Range
Set rng = xlSheet.Range("A65536").End(xlUp)
iNum = rng.row
Set rng = Nothing
lastRowNo = iNum
End Function
'新增文档自定义属性及值
'参数:属性名称,值,类型(4:文本,3:日期,2:是否,1:数字),链接
'like :addCustomProperty "url", "wwwl.80.hk", 4, False
Sub addCustomProperty(ByVal sname As String, ByVal sValue As Variant, ByVal iType As Integer, ByVal bLink As Boolean)
oecl.ActiveWorkbook.CustomDocumentProperties.Add sname, bLink, iType, sValue
End Sub
'取文档自定义属性值
'参数:属性名称
'返回值:属性的值
Function getValueOfCustomProperty(ByVal sname As String) As String
If existCustomProperty(sname) Then
getValueOfCustomProperty = oecl.ActiveWorkbook.CustomDocumentProperties(sname).value
Else
getValueOfCustomProperty = ""
End If
End Function
'修改自定义属性的值
'参数:属性名称,属性值(类型一定要匹配)
Sub updateValueofCustomProperty(ByVal sname As String, ByVal vValue As Variant)
If existCustomProperty(sname) Then
oecl.ActiveWorkbook.CustomDocumentProperties(sname).value = vValue
End If
End Sub
'删除文档自定义属性
'参数:自定义属性的名称
Sub deleteCustomProperty(ByVal sname As String)
If existCustomProperty(sname) Then
oecl.ActiveWorkbook.CustomDocumentProperties(sname).Delete
End If
End Sub
'是否存在自定义属性
'参数:sCustomPropertyName:自定义属性名称
'返回值:True:存在,False:不存
Function existCustomProperty(ByVal sCustomPropertyName As String) As Boolean
Dim myCustomProperty As Variant
existCustomProperty = False
For Each myCustomProperty In oecl.ActiveWorkbook.CustomDocumentProperties
If myCustomProperty.Name = sCustomPropertyName Then
existCustomProperty = True
Exit For
End If
Next
End Function