Generate Txt File VBA Question

[Ruppert23]

Hello All! I was curious if anyone was able to assist with the VBA process for the below desired outcome.

I am looking to have a text file generated after clicking the "Generate Txt File" button in the below Excel snippet. The text file would then format like the second snippet below and take the current date & total daily settlement amount and plug it into the text file. (I would even be okay with it generating a blank txt file where I can type the info in)

Cheers!

 

[rlv01]

So your desired (csv) text file would only contain one line as shown?

 

[Ruppert23]

So your desired (csv) text file would only contain one line as shown?

Hi, yes that is correct!

Thanks

 

[rlv01]

Public Sub GenerateTextFile()
    Dim DateStr As String, Outfile As String, TemplateStr As String, TextLine As String
    Dim DailySettlement As Variant
    Dim WS As Worksheet

    Set WS = ActiveSheet

    Outfile = "C:\Temp\Daily.csv"
    DailySettlement = Format(WS.Range("B1").Value, "0.00")
    DateStr = Format(Date, "yyyy-mm-dd")
    TemplateStr = "ABC COLLATERAL,5000000.00,2023-03-30,,,,0,John Doe,000-00-0000"

    TextLine = Replace(TemplateStr, "2023-03-30", DateStr)
    TextLine = Replace(TextLine, "5000000.00", DailySettlement)

    Open Outfile For Output Access Write As #1        ' Open text file for write
    Print #1, TextLine                                ' Write to output file
    Close #1                                          ' Close file.
End Sub

 

[JEC]

Where is the rest of the text coming from? Like John Doe?

This could be done with a one liner

 

[Ruppert23]

View attachment 88943

Public Sub GenerateTextFile()
    Dim DateStr As String, Outfile As String, TemplateStr As String, TextLine As String
    Dim DailySettlement As Variant
    Dim WS As Worksheet

    Set WS = ActiveSheet

    Outfile = "C:\Temp\Daily.csv"
    DailySettlement = Format(WS.Range("B1").Value, "0.00")
    DateStr = Format(Date, "yyyy-mm-dd")
    TemplateStr = "ABC COLLATERAL,5000000.00,2023-03-30,,,,0,John Doe,000-00-0000"

    TextLine = Replace(TemplateStr, "2023-03-30", DateStr)
    TextLine = Replace(TextLine, "5000000.00", DailySettlement)

    Open Outfile For Output Access Write As #1        ' Open text file for write
    Print #1, TextLine                                ' Write to output file
    Close #1                                          ' Close file.
End Sub

This helps a lot, thank you for assisting! The only issue I am running into is the file path error. For reference, this is where I need the text file saved below after generating it via the macro button.

 

[rlv01]

That defines a folder path. You need variable Outfile to define a file path.