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

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.

Tags:

code

examples

excel

microsoft project

programming

vba

Posted on February 5, 2007 2:44 PM | Permalink

Comments (10)

JohnB:

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.

Posted by JohnB | February 6, 2007 7:23 AM

Posted on February 6, 2007 07:23

bruce:

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

Posted by bruce | February 28, 2007 2:31 AM

Posted on February 28, 2007 02:31

Tim:

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.

Posted by Tim | March 29, 2007 3:35 PM

Posted on March 29, 2007 15:35

CK:

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?

Thanks.

Posted by CK | April 28, 2007 8:37 AM

Posted on April 28, 2007 08:37

SM:

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

Macca

Posted by SM | June 20, 2007 2:23 PM

Posted on June 20, 2007 14:23

Ron:

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

Posted by Ron | September 3, 2007 11:04 PM

Posted on September 3, 2007 23:04

Jan DD:

This one is nice! so easy! thx!

Posted by Jan DD | September 14, 2007 1:10 AM

Posted on September 14, 2007 01:10