取得当前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