Macro to access/edit/save a text file

Hello Chintu Raju,

This macro will copy the data in column "A" of the Active Sheet to a text file. All variables that can be changed are marked in red. You should make all your changes to the code before saving it. You can change your file name, file directory, and starting row. You can call the macro CreateTextFile by attaching it to a command button or by using ALT+F8 to run it from the Macro Dialog.

Place this Code in a Standard VBA Module

Public RunWhen As Double

Sub CreateTextFile()

  Dim fso As Object 
  Dim FileName As String
  Dim LastRow As Long
  Dim MyFile As Object
  Dim Overwrite As Boolean
  Dim R As Long
  Dim StartRow As Long

    Overwrite = True
    FileName = "c:\testfile.txt"    'Be sure to include the directory path
    StartRow = 1
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

      Set fso = CreateObject("Scripting.FileSystemObject")
      Set MyFile = fso.CreateTextFile(FileName, Overwrite)
        For R = StartRow To LastRow
          MyFile.WriteLine(Cells(R, "A").Text)
        Next R
      MyFile.Close

    Set fso = Nothing
    RunSaveToFile

End Sub

Sub RunSaveToFile()

  'Runs every hour - TimeSerial(hours, minutes, seconds)
    RunWhen = Now + TimeSerial(1,0,0)
    Application.OnTime EarliestTime:=RunWhen, Procedure:="CreateTextFile", Schedule:=True

End Sub

Sun StopSaveToFile()

    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen,Procedure:="CreateTextFile", Schedule:=False

End Sub

Place this code in the ThisWorkbook Module

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    StopSaveToFile

End Sub

Sincerely,
Leith Ross