CODEWITHSUNDEEP
vbatext-files
danny
danny

Reputation: 1637

How to create and write to a txt file using VBA

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

Upvotes: 145

Views: 676552

Answers (6)

Francis
Francis

Reputation: 857

A more modular approach for writing to a text file in VBA, inspired by Ben and Marcus's answers.

' shows how to use the modules below
Sub test_writeToTextFile()
Dim txtFileObj As Object
Set txtFileObj = openTextFile("C:\my\path\to\logFile.txt")
writeToTextFile txtFileObj, "toitoto"
writeToTextFile txtFileObj, "tatatat"
closeTextFile txtFileObj
End Sub

' creates and returns the file object
Function openTextFile(strPath) As Object
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Dim oFile As Object
Set oFile = fso.CreateTextFile(strPath)
Set openTextFile = oFile
Set fso = Nothing
End Function

' writes to the file object created above
Sub writeToTextFile(oFile As Object, stuffToWrite)
oFile.WriteLine stuffToWrite
End Sub

' tidy up when finished
Sub closeTextFile(oFile As Object)
oFile.Close
Set oFile = Nothing
End Sub

Upvotes: 2

Marcus Mangelsdorf
Marcus Mangelsdorf

Reputation: 3070

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

Upvotes: 70

Bhanu Sinha
Bhanu Sinha

Reputation: 1776

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

More Information:

Upvotes: 80

Zack Brightman
Zack Brightman

Reputation: 1

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

Upvotes: -12

pelos
pelos

Reputation: 1876

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

Upvotes: 37

Ben
Ben

Reputation: 35613

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:

Upvotes: 203

Related Questions