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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值