VBA点滴备忘录（持续更新中）

1、判断Excel文件是否已打开以及自动打开文件

Function openWorkbook(ByVal fileName, ByVal filePath)
    On Error Resume Next
    If Len(Workbooks(fileName).Name) <= 0 Then
        Workbooks.Open (filePath)
    End If
End Function

  

2、从文件全路径中截取文件名

Dim FilePath As String
FilePath = "C:\xxx\xxxx\review.txt"
Dim FileName As String
FileName = Right(FilePath, Len(FilePath) - InStrRev(FilePath, "\"))

  

3、从文件全路径名中截取文件夹路径

Dim filePath As String
filePath = "C:\xxx\xxxx\review.txt"
Dim forlderPath As String
forlderPath = Left(filePath, InStrRev(filePath, "\"))

 

4、统计某个文件夹下的文件个数

Function getFileCount(ByVal forlderPath, ByVal filePath, index)
    Dim tmpFilePath As String
    tmpFilePath = Dir(forlderPath & "*.*")
    Dim i As Integer
    i = 0
    Do While tmpFilePath <> ""
        i = i + 1
        If tmpFilePath = filePath Then
            index = i
        End If
        tmpFilePath = Dir
    Loop
    getFileCount = i
End Function

  

5、获取文件的最近修改的日期与时间

FileDateTime("filePah")

 

6、判断某一地址单元格是否为合并单元格，如果是，则其为几列几行合并而成

Sub myMerge()
    Set cel = Range("A1")
    If cel.MergeCells Then
        MsgBox cel.Address(0, 0) & "为合并单元格，" & "共有" & cel.MergeArea.Rows.Count & "行" & cel.MergeArea.Columns.Count & "列组成"
    End If
End Sub