By danny


2012-07-16 11:20:20 8 Comments

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

5 comments

@Bhanu Sinha 2017-10-26 20:25:26

Open ThisWorkbook.Path & "\template.txt" For Output As #1
Print #1, strContent
Close #1

More Information:

@Mohammed Noureldin 2017-10-26 20:55:38

Pleas write the answer with some explanations and details.

@phrebh 2018-02-15 19:52:52

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.

@chiliNUT 2018-02-21 18:10:26

this works great. I've never seen the Open somePath For Output As #1 syntax before, this documents it: msdn.microsoft.com/en-us/vba/language-reference-vba/articles‌​/…

@richardtallent 2018-06-01 13:31:39

I prefer this approach as well for mundane text file writing. These statements have been part of the BASIC language since at least 1981.

@George Birbilis 2018-08-14 19:44:26

Regarding comment from @phrebh about using FreeFile instead of a hardcoded #1 see wellsr.com/vba/2016/excel/vba-freefile-for-foolproof-file-IO

@Marcus Mangelsdorf 2018-04-05 14:16:20

To elaborate on Ben's answer (since improving it doesn't seem to be allowed):

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 "test"

    ' 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

@Portland Runner 2018-08-14 17:42:32

Thanks for writing a complete answer with helpful comments to code.

@Marcus Mangelsdorf 2018-08-17 05:59:39

I'm more than happy if you learned something from my post! :)

@Zack Brightman 2017-02-10 13:16:33

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()

@Zack Brightman 2017-02-10 13:16:51

This can help with Writing and Reading a text file

@M. Adeel Khalid 2017-02-10 13:45:04

I think you don't read what the question is and also you don't like to explain what you are going to attempt which isn't a good thing while helping others.

@BDL 2017-02-10 14:52:08

And you are not even formatting your answer properly.

@Marcus Mangelsdorf 2018-03-28 07:48:03

Unfortunately, the code you posted is not VBA. There is no My.Computer.FileSystem object in VBA by default and so you can't use the WriteAllText method, either.

@pelos 2016-02-23 17:01:11

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

@rrs 2017-02-03 17:13:52

Is it possible to use the file chooser to set the path?

@paolov 2018-09-25 01:04:01

This creates a file which is UCS2 encoded. Is it possible to create one that is ANSI?

@Ben 2012-07-16 11:27:11

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:

@TmTron 2015-06-16 15:10:18

when you reference the Scripting Runtime directly, you could use the correct types: Dim oFs As New FileSystemObject Dim oFile As TextStream

@Rick Henderson 2016-06-07 16:40:17

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.

@Ben 2016-06-08 08:59:17

@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.

@Marcus Mangelsdorf 2018-09-10 12:02:47

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.

@Ben 2018-09-10 12:07:12

@MarcusMangelsdorf you already made this comment.

@Marcus Mangelsdorf 2018-09-11 08:34:48

@Ben: You're right, then I read this answer on Meta 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 :(

@Ben 2018-09-11 09:07:44

@MarcusMangelsdorf I have heard you, but I don't want to have a debate.

Related Questions

Sponsored Content

30 Answered Questions

[SOLVED] How to append text to an existing file in Java

18 Answered Questions

[SOLVED] Why should text files end with a newline?

1 Answered Questions

[SOLVED] Create and Write to a text file using an excel macro and VBA

2 Answered Questions

[SOLVED] How to create and write to a txt document from a batch executable

3 Answered Questions

[SOLVED] To write the value from resultset to text file (.txt file)

1 Answered Questions

1 Answered Questions

[SOLVED] How to create an input and output file based on user input?

  • 2015-12-17 22:24:43
  • SamB
  • 87 View
  • -1 Score
  • 1 Answer
  • Tags:   java text-files

3 Answered Questions

[SOLVED] How to change file extensions in VBA

1 Answered Questions

[SOLVED] Automating vba code window

3 Answered Questions

[SOLVED] How to create char[][] from txt file in C?

Sponsored Content