vba 创建txt文件 并且写入字符串
有的时候,我们在操作Excel的时候,
最后常常需要输出一个记录的log,这样可以供后期追溯。
主要使用CreateTextFile方法。
Private Sub CreateTxtFile()
Dim fso As Object
Dim myTxt As Object
Dim MyFName As String
Dim i As Integer
Dim nowDate As String
Dim sht As Worksheet
nowDate = CDate(Now())
nowDate = Replace(nowDate, ":", "") '去除Now()函数的返回的冒号
nowDate = Replace(nowDate, "/", "") '去除Now()函数的返回的斜杠
nowDate = Replace(nowDate, " ", "_") '去除Now()函数的返回的空格
MyFName = ThisWorkbook.path & "/" & nowDate & ".txt" '确定新建的txt文件的路径
Set fso = CreateObject("Scripting.FileSystemObject") '创建文件需要使用Scripting.FileSystemObject对象
Set myTxt = fso.CreateTextFile(fileName:=MyFName, OverWrite:=True) '使用CreateTextFile创建文件
For Each sht In ThisWorkbook.Worksheets '循环每一个sheet
myTxt.Write sht.Name & vbCrLf '使用Write方法写入sheet名,然后插入一个换行符
Next
myTxt.Close
Set myTxt = Nothing
Set fso = Nothing
End Sub
动图演示如下: