他山之石——使用VBA写入txt文件

这一节基本没用，除了最后一个例子：记录文件的打开和关闭时间。

'Open 文件名 for 打开方式 as 文件编号

  '打开方式:
     'Input ：只能读，不能写
     'Append：允许读也允许写，如果存在文件就追加记录，如果没有就新建文件
     'Output：可以读，也可以写。但总会把原来的同名文件删除，再新建一个
     
  '读取txt文件内容方法
     'input：从文件中读取指定数量的字符。
     'Input #：把数据读出放在变量里，变量用逗号分隔
     'Line Input #：取出完整的一行
     
  '向文件中写入数据
     'write #：向文件中写入值，值用引号引起来。如果想在同一行中继续写入，可以在前一次写时结尾添加“;”号
     'Print #：向文件中写入值，如果想在同一行中继续写入，可以在前一次写时结尾添加“;”
  '字符的间隔符
     'Spc(n)表示输入n个空字符

'一、用Print写入
  '1 分行输入
  Sub t1()
   Dim f As String
     f = ThisWorkbook.path & "\a.txt"
     Open f For Output As #1
     Print #1, "产品名称"
     Print #1, Date
     Close #1
  End Sub
  '2 在同一行输入
  Sub t2()
   Dim f As String
     f = ThisWorkbook.path & "\a.txt"
     Open f For Output As #1
     Print #1, "产品名称";
     Print #1, "A产品"
     Close #1
  End Sub
  '3 输入时添加空格符
  Sub t3()
   Dim f As String
     f = ThisWorkbook.path & "\a.txt"
     Open f For Output As #1
     Print #1, "产品名称"; Spc(5);
     Print #1, "A产品"
     Close #1
  End Sub
 
  '4 在指定的列数输入
  Sub t4()
   Dim f As String
     f = ThisWorkbook.path & "\a.txt"
     Open f For Output As #1
     Print #1, "产品名称"; Tab(8); '在第10列输入下面的，如果为空则插入到下一个打印的位置
     Print #1, "A产品"
     Close #1
  End Sub
  
'二、用Write写入

  Sub t5()
   Dim f As String
     f = ThisWorkbook.path & "\a.txt"
     Open f For Output As #1
     Write #1, "产品名称"
     Write #1, 5
     Close #1
  End Sub
  Sub t6()
   Dim f As String
     f = ThisWorkbook.path & "\a.txt"
     Open f For Output As #1
     Write #1, "产品名称";
     Write #1, 5
     Close #1
  End Sub
  Sub t7()
   Dim f As String
     f = ThisWorkbook.path & "\a.txt"
     Open f For Output As #1
     Write #1, "产品名称"; 5 '这里逗号等同于"; "(分号)"
     Close #1
  End Sub
  
'三、Print和Write区别
     '1 写到到txt文件后，字符会添加“,”(逗号)
     '2 除文本外，日期、逻辑值输入结果不一样,两边会加#号
  Sub t8()
    Dim f As String
    f = ThisWorkbook.path & "\a.txt"
     Open f For Output As #1
     Print #1, Date; 1 = 1; Null
     Write #1, Date; 1 = 1, Null
     Close #1
  End Sub
     
'四 不同类型数值的输入的
   '在用print写入数据时
      '1 日期后要加空格
      '2 数字前后都加空格
      '3 字符前后均不加空格
   Sub t9()
    Dim f As String
    f = ThisWorkbook.path & "\a.txt"
     Open f For Output As #1
     Print #1, Date; 12
     Print #1, Date; "ABC"
     Print #1, Date; "我爱你"
     Print #1, Date; Date
     Print #1, "我爱你"; 12
     Print #1, "我爱你"; "abc"
     Print #1, "我爱你"; Date
     Print #1, "我爱你"; "abc"
     Print #1, 12; "abc"
     Print #1, 12; "我爱你"
     Print #1, 12; 123
     Print #1, 12; "123"
     Close #1
  End Sub


'将sheet2表中的数据写入到文本文本中
  Sub 转换成txt文件()
    Dim f, arr, x, y, k
    f = ThisWorkbook.path & "\ruku.txt"
    arr = Sheets("sheet2").Range("a1:e6")
    Open f For Output As #1
    For x = 1 To UBound(arr)
      For y = 1 To UBound(arr, 2)
        If y = UBound(arr, 2) Then
         Print #1, arr(x, y)
        Else
         If VBA.IsNumeric(arr(x, y)) Then
            k = 12 - Len(arr(x, y)) - 2
         ElseIf arr(x, y) Like "[A-Z]" Or VBA.IsDate(arr(x, y)) Then
            k = 12 - Len(arr(x, y)) - 1
         Else
            k = 12 - Len(arr(x, y)) * 2
         End If
         Print #1, arr(x, y); Spc(k);
        End If
      Next y
      k = 0
    Next x
  Close #1
  End Sub

'示例2 记录打开和关闭时间
' 详见thisworkbook事件
     

代码：记录文件的打开、关闭时间

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim f As String
     f = ThisWorkbook.path & "\filetime.txt"
     Open f For Append As #1
     Print #1, "Close:  "; Now
     Close #1
End Sub

Private Sub Workbook_Open()
   Dim f As String
     f = ThisWorkbook.path & "\filetime.txt"
     Open f For Append As #1
     Print #1, "Open:  "; Now
     Close #1
End Sub