VBA text box question

[jdotbonk]

Hello All!

I was curious if anyone is familiar with how I can create a “Generate Text File” button/command to this sheet that would spit out this example text file below.

The only piece of data that would change each day is the amount to match the yellow highlighted cell in the spreadsheet, & the date to today’s current date. Any advice is appreciated!
Cheers!

 

[Anthony47]

Maybe this snippet?

Dim TxtFile As String
Dim Msg0 As String, fFile As Long, MSG
'
TxtFile = "C:\PROVA\myTXTFile.Txt"                              '<<< Your output file
Msg0 = "JRB COLLATERAL, ####, @@@@,,,,The other text, hello"    '<<< Your BASE message with "placeholders"
'
fFile = FreeFile
MSG = Replace(Msg0, "####", Format(Range("D2").Value, "0.00"), , , vbTextCompare)   '<<< YOUR Range
MSG = Replace(MSG, "@@@@", Format(Date, "yyyy-mm-dd"), , , vbTextCompare)
'
'Create txt file:
Open TxtFile For Output As #fFile
    Print #fFile, MSG
Close #fFile

The lines marked <<< need to be customized

 

[jdotbonk]

Maybe this snippet?

Dim TxtFile As String
Dim Msg0 As String, fFile As Long, MSG
'
TxtFile = "C:\PROVA\myTXTFile.Txt"                              '<<< Your output file
Msg0 = "JRB COLLATERAL, ####, @@@@,,,,The other text, hello"    '<<< Your BASE message with "placeholders"
'
fFile = FreeFile
MSG = Replace(Msg0, "####", Format(Range("D2").Value, "0.00"), , , vbTextCompare)   '<<< YOUR Range
MSG = Replace(MSG, "@@@@", Format(Date, "yyyy-mm-dd"), , , vbTextCompare)
'
'Create txt file:
Open TxtFile For Output As #fFile
    Print #fFile, MSG
Close #fFile

The lines marked <<< need to be customized

Hi Anthony - appreciate your feedback! I think I should have started out with step 1, which is getting a “Generate Text File” button similar to the already present “Generate Email” button on the worksheet. I think then I would be able to modify what the text file spits out after being able to display it.

Thanks!

 

[Anthony47]

Are you asking to have a button that appears and disappears?
If Yes, then add a commandbutton to your sheet, taking it from the ActiveX controls: Menu /Develop /Insert; select CommandButton from the ActiveX list of controls; draw the commandbutton.

If you want to hide it, execute the instruction Sheets("sheet3").CommandButton1.Visible = False (for example within a Sub Workbook_Open)
When you need the button be visble, execute the instruction Sheets("sheet3").CommandButton1.Visible = True
Adapt sheet name and commandbutton name to your situation