« Taking the PMP Exam - Appendix B - Contract Types | Main | VB / C# robotic car »

VBA Writing to a text file (MS Project, Excel)

One common question is how do I write from an office app like Excel or Project to a text file. I have the code embedded in a few samples here, but this short sample shows it most clearly. There are just a couple of steps. Use FreeFile to get the file number of the next file. Open the file for Output, then write or print into it. Finally close the file. Here is the commented code to do this:

Sub WriteToATextFile
'first set a string which contains the path to the file you want to create.
'this example creates one and stores it in the root directory
MyFile = "c:\" & "whateveryouwant.txt"
'set and open file for output
fnum = FreeFile()
Open MyFile For Output As fnum
'write project info and then a blank line. Note the comma is required
Write #fnum, "I wrote this"
Write #fnum,
'use Print when you want the string without quotation marks
Print #fnum, "I printed this"
Close #fnum
End Sub

There are a few other pieces of the puzzle to clarify. Open for has a number of mode parameters. You can open for input (read only), output (write only), append (write at end - good for logs), binary and random (read/write default). You can also control this by an optional access keyword (read, write, read write) but why bother if you have the mode set. Freefile is used to get the filenumber of the next free file.

  • Analyze Microsoft Project Resource Usage Data In Excel
  • MS Project VBA - Trim Function
  • Microsoft Project Undo Levels and Macros
  • Office VBA for Mac After 2008
  • Using a ComboBox in a Microsoft Project Userform
  • Working with the Project Object
  • Working with the Tasks Collection
  • Setting Microsoft Project Level Custom fields using VBA
  • VBA returns in Excel 2011 for Mac
  • Iterating through Microsoft Project Subprojects

  • Comments (10)


    There's a typo at the end of the Myfile line (remove the last quote mark).

    Also, Freefile does not (I think) create the file but simply provide you with the next available integer that you can use when you open the file and then read from or write to it.


    but this will write the file to the root of the C drive. Is it not better to use a relative address like "..\" & "whateveryouwant.txt" to write the text file to the same folder as the xls file.


    Good point, thanks for the comment. - Jack


    Relative paths are fine, but another good way to do this is to use the app.path property. For example:

    MyPath = app.path & "/somefilename.txt"

    This will place somefilename.txt in the same folder as the spreadsheet. Using the relative path in the previous comment will cause problems if you actually do save the document to your root directory.


    Instead of placing the file in the same folder as the spreadsheet, is there any ways for the user to select the directory of where to save the file?



    CK - you can use Application.Dialogs(xlDialogSaveAs).Show ("WhatFileName") & ".xls")
    to open the file save as dialog and preset the file name. I use it with a formatted date to write files like "61010 June 2007.xls" as an invoice number and date.

    Thanks for the tips, Jack



    How would I be able to save the text file as a diferent name each time by using the date. Such as 090307.txt

    Jan DD:

    This one is nice! so easy! thx!


    I've been hacking around with VBA for a few years now and this is the first time i have seen Freefile.


    Goran Milic:

    This is a nice peace of code. Tested in different situations. Quite robust.


    Works in Access 2003 also, THANKS

    Post a comment

    (Comments are moderated to fight SPAM and will be published after I have a chance to approve them. Thanks for waiting.)


    The previous article is Taking the PMP Exam - Appendix B - Contract Types.

    The next article is VB / C# robotic car.

    Current articles are in the main index page and you can find a complete list of articles in the archives.

    Creative Commons License
    This weblog is licensed under a Creative Commons License.
    Powered by
    Movable Type 3.34