CODEWITHSUNDEEP
vbaexcel
Neha
Neha

Reputation: 137

Append to delimited text file

`I want to use a macro in vba that:

  1. Takes values of the selected cells in given excel sheet. Asks for selection if there are no selected cells.

  2. Checks if a certain text file (comma delimited) exists.

  3. Appends values if the file exists, creates file and adds values if the file does not exist (comma delimited).

Any help will be appreciated.

Sub Wri()

Dim myrng As Range

Set myrng = Range("A1:A22222")

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs, f, ts, s
Dim cellv As String

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile "C:\Users\HP\Documents\fil.txt"
Set f = fs.GetFile("C:\Users\HP\Documents\fil.txt")
Set ts = f.OpenAsTextStream(ForWriting, TristateFalse)

For Each cell In myrng
    cellv = cell.Value
    ts.Write (cellv & Chr(44))
Next cell
End Sub

` I want to use a range the user selects and append

Upvotes: 2

Views: 458

Answers (1)

Kapol
Kapol

Reputation: 6463

Sub MainProc()
    Dim Rng As Range
    Dim Cell As Range

    On Error Resume Next
    Set Rng = Application.InputBox("Select range", Type:=8)
    On Error GoTo 0

    If Rng Is Nothing Then
        MsgBox "No cells selected"
        Exit Sub
    End If

    For Each Cell In Rng.Cells
        WriteToFile Cell, "Hello World!"
    Next Cell
End Sub

Private Sub WriteToFile(Rng As Range, s As String)
    Dim FSO As FileSystemObject
    Dim TS As TextStream

    Set FSO = New FileSystemObject

    Set TS = FSO.OpenTextFile(Rng.Value, ForAppending, True, TristateFalse)
    TS.WriteLine s
End Sub

Upvotes: 1

Related Questions