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