141

I have a file which is manually added or modified based on the inputs. Since most of the contents are repetitive in that file, only the hex values are changing, I want to make it a tool generated file.

I want to write the c codes which are going to be printed in that .txt file.

What is the command to create a .txt file using VBA, and how do I write to it

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
danny
  • 1,587
  • 2
  • 12
  • 12
  • 1
    Do you want to modify an existing file once it is created? And what is "the c codes" – brettdj Jul 16 '12 at 11:34
  • 1
    If any of the existing answers met your needs, would you mind accepting it as an answer, so your question does no longer show up as unanswered? (If not, please add details on what's missing from the existing answers to solve your problem :)) – Marcus Mangelsdorf Mar 28 '18 at 08:18

5 Answers5

197

Use FSO to create the file and write to it.

Dim fso as Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile as Object
Set oFile = FSO.CreateTextFile(strPath)
oFile.WriteLine "test" 
oFile.Close
Set fso = Nothing
Set oFile = Nothing

See the documentation here:

Ben
  • 34,935
  • 6
  • 74
  • 113
  • 29
    when you reference the Scripting Runtime directly, you could use the correct types: `Dim oFs As New FileSystemObject Dim oFile As TextStream` – TmTron Jun 16 '15 at 15:10
  • Is using the Scripting Runtime preferred over the older channel method? I'd like some reasons to tell my students with info backed up by other experience. – Rick Henderson Jun 07 '16 at 16:40
  • @RickHenderson, I prefer it, if that's what you mean. The advantage is encapsulation. Once you object (set oFile = Nothing|), or it goes out of scope, the file is automatically closed. – Ben Jun 08 '16 at 08:59
  • 5
    Please note that this answer **promotes bad coding practice**: The problem is that _not explicitly defining the correct variable types_ as well as _creating an object by a string reference to its name_ can cause you very hard to debug problems in the future (for example if you misspell parts of the name). Also, by not typing the variables, you have no way to learn about the other amazing methods `FileSystemObject` has to offer. @Ben: Please **consider updating your answer** to lead beginners in a [better direction](https://stackoverflow.com/a/49674605/2822719). – Marcus Mangelsdorf Sep 10 '18 at 12:02
  • @Ben: You're right, then I read [this answer on Meta](https://meta.stackoverflow.com/a/341995/2822719) and thought I'd point out what the problem is directly. But you're right, I removed the older comment. What I don't understand, though, is how you can keep this answer as is, knowing that most people will just copy & paste the badly written code :( – Marcus Mangelsdorf Sep 11 '18 at 08:34
  • 5
    @MarcusMangelsdorf I have heard you, but I don't want to have a debate. – Ben Sep 11 '18 at 09:07
  • 5
    I disagree that this answer is promoting bad coding practice. Using late binding like this is perfectly acceptable and is useful where you don't know what version of the Microsoft Scripting runtime your user has on their machine. – apeman Feb 04 '21 at 15:18
72
Open ThisWorkbook.Path & "\template.txt" For Output As #1
Print #1, strContent
Close #1

More Information:

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Bhanu Sinha
  • 1,566
  • 13
  • 10
  • 2
    Pleas write the answer with some explanations and details. – Mohammed Noureldin Oct 26 '17 at 20:55
  • 11
    I prefer this method to the FSO method because it doesn't require external references and is quite short. Although I do suggest using FreeFile to get the file number instead of hardcoding it as #1. – phrebh Feb 15 '18 at 19:52
  • 2
    this works great. I've never seen the `Open somePath For Output As #1` syntax before, this documents it: https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/open-statement – chiliNUT Feb 21 '18 at 18:10
  • 7
    I prefer this approach as well for mundane text file writing. These statements have been part of the BASIC language since at least 1981. – richardtallent Jun 01 '18 at 13:31
  • 4
    Regarding comment from @phrebh about using FreeFile instead of a hardcoded #1 see https://wellsr.com/vba/2016/excel/vba-freefile-for-foolproof-file-IO/ – George Birbilis Aug 14 '18 at 19:44
  • +1 for this - trying to use the fso Write / Writeline can be thrown off by some special characters - don't ask me which ones! but there are some responses I get from web requests that fail with the FSO option whereas this works fine every time. – jamheadart Jan 20 '21 at 11:28
67

To elaborate on Ben's answer:

If you add a reference to Microsoft Scripting Runtime and correctly type the variable fso you can take advantage of autocompletion (Intellisense) and discover the other great features of FileSystemObject.

Here is a complete example module:

Option Explicit

' 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
Public Sub SaveTextToFile()

    Dim filePath As String
    filePath = "C:\temp\MyTestFile.txt"

    ' The advantage of correctly typing fso as FileSystemObject is to make autocompletion
    ' (Intellisense) work, which helps you avoid typos and lets you discover other useful
    ' methods of the FileSystemObject
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject
    Dim fileStream As TextStream

    ' Here the actual file is created and opened for write access
    Set fileStream = fso.CreateTextFile(filePath)

    ' Write something to the file
    fileStream.WriteLine "something"

    ' Close it, so it is not locked anymore
    fileStream.Close

    ' Here is another great method of the FileSystemObject that checks if a file exists
    If fso.FileExists(filePath) Then
        MsgBox "Yay! The file was created! :D"
    End If

    ' Explicitly setting objects to Nothing should not be necessary in most cases, but if
    ' you're writing macros for Microsoft Access, you may want to uncomment the following
    ' two lines (see https://stackoverflow.com/a/517202/2822719 for details):
    'Set fileStream = Nothing
    'Set fso = Nothing

End Sub
Marcus Mangelsdorf
  • 2,852
  • 1
  • 30
  • 40
  • 1
    Thanks for writing a complete answer with helpful comments to code. – Automate This Aug 14 '18 at 17:42
  • I'm more than happy if you learned something from my post! :) – Marcus Mangelsdorf Aug 17 '18 at 05:59
  • 4
    there are many advantages to early binding (it's faster as well) However, i feel you did not highlight the fact that **late binding is version independent** and does not require creating a Reference. This is critical for any code being redistributed – gregV Dec 21 '21 at 18:59
36

an easy way with out much redundancy.

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim Fileout As Object
    Set Fileout = fso.CreateTextFile("C:\your_path\vba.txt", True, True)
    Fileout.Write "your string goes here"
    Fileout.Close
pelos
  • 1,744
  • 4
  • 24
  • 34
-12
Dim SaveVar As Object

Sub Main()

    Console.WriteLine("Enter Text")

    Console.WriteLine("")

    SaveVar = Console.ReadLine

    My.Computer.FileSystem.WriteAllText("N:\A-Level Computing\2017!\PPE\SaveFile\SaveData.txt", "Text: " & SaveVar & ", ", True)

    Console.WriteLine("")

    Console.WriteLine("File Saved")

    Console.WriteLine("")

    Console.WriteLine(My.Computer.FileSystem.ReadAllText("N:\A-Level Computing\2017!\PPE\SaveFile\SaveData.txt"))
    Console.ReadLine()

End Sub()
BDL
  • 21,052
  • 22
  • 49
  • 55
Zack Brightman
  • 1
  • 1