Looking to combine text from cells and output to a filename of my choice in a folder of choice

[peejaygee]

Hey All,

using VBA/Macro is it possible to write to a .vbs (in text) file in a folder of choice. I have a spreadsheet at the moment, which builds blocks of text, using the & then I can change a few cells, the big block of text changes. I then manually open a .vbs file, and copy and paste into it and save it out. With most of the stuff inside the vbs not changing I was wondering if I could just drop a button on excel, and when I've change what I change, hit the button and the files are generated for me? I'm assuming excel would have to check if the file already exists and delete it first?

Is this at all possible, this is a very basic excel screenshot of what I was thinking.

 

[peejaygee]

So I ended up doing it this way, crude, but it works. I just assigned the macro to my button

Public KillFile As String
Public WriteFile As String
Public fso As Object
Public Fileout As Object

Sub OutputTexttoFiles()
Set fso = CreateObject("Scripting.FileSystemObject")
KillFile = Range("Q01"): DelFileIfThere
WriteFile = Range("Q12"): WriteNewFileOut
KillFile = Range("Q02"): DelFileIfThere
WriteFile = Range("Q13"): WriteNewFileOut
KillFile = Range("Q03"): DelFileIfThere
WriteFile = Range("Q14"): WriteNewFileOut
KillFile = Range("Q04"): DelFileIfThere
WriteFile = Range("Q15"): WriteNewFileOut
KillFile = Range("Q05"): DelFileIfThere
WriteFile = Range("Q16"): WriteNewFileOut
KillFile = Range("Q06"): DelFileIfThere
WriteFile = Range("Q17"): WriteNewFileOut
KillFile = Range("Q07"): DelFileIfThere
WriteFile = Range("Q18"): WriteNewFileOut
KillFile = Range("Q08"): DelFileIfThere
WriteFile = Range("Q19"): WriteNewFileOut
KillFile = Range("Q09"): DelFileIfThere
WriteFile = Range("Q20"): WriteNewFileOut
KillFile = Range("Q10"): DelFileIfThere
WriteFile = Range("Q21"): WriteNewFileOut
End Sub

Sub DelFileIfThere()
If Len(Dir$(KillFile)) > 0 Then
SetAttr KillFile, vbNormal
Kill KillFile
End If
End Sub

Sub WriteNewFileOut()
Set Fileout = fso.CreateTextFile(KillFile, True, True)
Fileout.Write WriteFile
Fileout.Close
End Sub