Write and replace string into txt file down an excel list

Question

I would be incredibly appreciative if someone could point me to the right approach to accomplish this.

I have an excel table that is list of batters for a little league softball team. I would like to write each name into a txt file, one at a time as they come to bat, and replace the previous one. The contents of the txt file will be overlayed onto a video stream. So each time a girl comes up, I would like to do something easy that will allow the next line in the excel table to be written into the text file replacing the previous text.

Thanks!

For reading and writing text files have a seach for FileSystemObject VBA. Or see this SO answer https://stackoverflow.com/questions/11503174/how-to-create-and-write-to-a-txt-file-using-vba — Gordon, May 21 '18 at 14:00

score 0 · Answer 1 · answered May 21 '18 at 13:54

You could do something like this, which will output the value of the (first) selected cell which overlaps a predetermined range to a specified text file.

This is triggered by a selection change, so a new file will be output whenever a new (matching) cell is selected - so the code should go in the appropriate worksheet module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim r As Range
    Dim rFind As Range
    Dim sTxt As String
    Dim sPath As String

    'Change path / name for text file output
    sPath = "C:\Temp\XLText.txt"

    'Change to range continaing the list of values to select / output
    Set rFind = Sheet1.ListObjects(1).ListColumns("Column1").DataBodyRange

    Set r = Intersect(Target, rFind)
    If Not r Is Nothing Then
        sTxt = r.Cells(1).Value
       WriteTextFile sTxt, sPath
    End If

End Sub


Sub WriteTextFile(ByVal s As String, ByVal sPath As String)
    Dim fso As Object
    Dim oFi As Object

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set oFi = fso.CreateTextFile(sPath)

    oFi.WriteLine s
    oFi.Close

    Set fso = Nothing
    Set oFi = Nothing
End Sub

score 0 · Accepted Answer · answered May 21 '18 at 15:00

This set up automatically changes to next batter, and resets at the start of the lineup. Click button every new batter.

Sub saveAsTxt()

    Dim ws As Worksheet
        Set ws = Sheets("Sheet1")

    Dim wsTwo As Worksheet
        Set wsTwo = Sheets("Sheet2")

    Dim NextBatterCounter As String
        NextBatterCounter = ws.Range("C4")

    Dim filePath As String
        filePath = "Z:\New folder\"

    Dim fileName As String
        fileName = "BatterUp"

    ' Go to Tools -> References... and check "Microsoft Scripting Runtime" to be able to use
    ' the FileSystemObject which has many useful features for handling files and folders

    Dim fso As New FileSystemObject
    Dim fileStream As TextStream

    Set fileStream = fso.CreateTextFile(filePath & fileName)

    fileStream.WriteLine ws.Range("A" & NextBatterCounter)

    fileStream.Close

    If ws.Range("C4") < 16 Then
        ws.Range("C4") = ws.Range("C4") + 1
    Else
        ws.Range("C4") = 2
    End If

End Sub

Thanks so much! This is way over my head, but I was able to fumble through it to make it work and even add another macro and list so I have separate Home and Away orders. Can't tell you how much I appreciate this! No thanks to Mod who put this "on hold" for not enough info as the above answer was exactly what I needed! — Chris Foster, May 23 '18 at 17:13
glad that it worked and that you were able to learn from it and hope that it makes the softball game even more fun! i think that it was put on hold because mods like to see some code! i am glad it worked out, keep posting, take care — learnAsWeGo, May 23 '18 at 18:38

Write and replace string into txt file down an excel list

2 Answers2