Reputation: 137
`I want to use a macro in vba that:
Takes values of the selected cells in given excel sheet. Asks for selection if there are no selected cells.
Checks if a certain text file (comma delimited) exists.
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
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