excel VBA

取得当前Sheet名

ActiveSheet.Name

 

'取得当前所有行数

ActiveSheet.UseRange.Rows.Count

 

'将生成的文本放在同目录下

Dim Path AS String '当前xls路径

Dim FileNameFile AS String '当前xls文件名，不带.xls

Dim FileNameSheet AS String '当前xls文件当前表sheet名称

Dim FileNameTime AS String '当前时间格式化

Dim Filename As String '生成文件名
    Path = ThisWorkbook.Path
    FileNameFile = Replace(ThisWorkbook.Name, ".xls", "")
    FileNameSheet = ThisWorkbook.ActiveSheet.Name
    FileNameTime = Format(Now(), "yyyymmddhhmmss")
    Filename = Path & "/" & FileNameFile & "-" & FileNameSheet & "-" & FileNameTime & ".bat"

 

'取得当前表的内容
Public Function getValue(ByVal row, ByVal column)
    Dim a, b As String
    a = Application.ActiveSheet.Name
    b = Sheets(a).Cells(row, column)
    getValue = b
End Function

 

'给当前表某行某列赋值
Public Sub setValue(ByVal row, ByVal column, ByVal tableValue)
    Dim a, b As String
    a = Application.ActiveSheet.Name
    Sheets(a).Cells(row, column) = tableValue
End Sub

'取得总行数
Public Function getRowNum()
    Dim i, j As Integer
    j = 1
    For i = 2 To 1000
        If getValue(i, 5) = "" Then
            Exit For
        Else
            j = j + 1
        End If
    Next
    getRowNum = j
End Function

 

写入文本

Sub WriteTxt(ByVal Flags As String, ByVal fName As String)
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Dim fs, objFile As Object
    Dim content, fileName As String
    content = Flags
    fileName = "e:/TestV30/" & fName
   
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set objFile = fs.OpenTextFile(fileName, ForAppending, True)
    objFile.Write content & vbCrLf
    objFile.Close
    Set objFile = Nothing
End Sub