VBA HOME PAGE

Menu

Recording macros

Looking at the code

Ways of running macros

Where macros are stored

Reasons to write macros

Writing macros

Procedure types

Visual Basic editor (VBE)

Rules & conventions

Excel objects

Range/Selection objects

Object hierarchy

Object browser

Chart objects

Pivot Table objects

Formulas

Visual Basic Functions

Creating Add-Ins

Variables & constants

Object variables

Arrays

Collections

Message Box

VBA Input Box

Excel Input Box

Making decisions (If)

Making decisions (Case)

Looping (Do...Loop)

Looping (For...Loop)

With...End With blocks

User defined functions

Event handling

Error handling

Debugging

Creating User Forms

DAO/ADO Objects

Input/Output Files

 

Other links

Example code snippets

Userform input example

 

 

 

 

 


Input/Output Files

VBA already includes commands to allow data to read or write to external text files. This is more commonly known as I/O (Input / Output) and is used to store files in the formats such as txt, csv and ini files.

 

Example of Output Data:

Sub BuildTextFile()
    Dim fnum

    fnum = FreeFile()
   
Open "C:\vba.txt" For Output As #fnum

   
Write #fnum, "Excel VBA", "Day 1"
   
Write #fnum, "Excel VBA", "Day 2"
   
Write #fnum, "Excel VBA Workshop Q&A", "Day 3"

   
Close #fnum
End Sub

The above example creates an instance of a file using the FreeFile function, which returns a unique number (as its handler). The Open method is used to locate and open the file.

The Output property tells the system that data is to be written to the named file using the pointer #fnum.

The Write method adds line-by-line data to the pointer and then is lost with the Close method.

Even if the file name does not exist, it will create this file in the specified path but the path must exist.

If the filename already exists, this routine will overwrite (no prompt) and the previous file will be  lost.

 

The file generated is a 'txt' file:

 

Example of Input Data:

Sub ReadTextFile()
    Dim fnum
    Dim strField1 As String, strField2 As String
   
    fnum = FreeFile()
   
Open "C:\vba.txt" For Input As #fnum

    Do Until EOF(fnum)
        Input #fnum, strField1, strField2
        Debug.Print strField1 & " : " & strField2
    Loop

   
Close #fnum
End Sub

The above example uses the Input property instead to change the direction of the flow of data (read from).

Using the EOF method, the procedure loops through the delimiter line break until it reaches the end of the file.

 

To view the results, open the Immediate Window (Ctrl + G) before running the above procedure:

 

The above two examples demonstrates how to read and write data to and from external files and will require a little more coding to deal with interaction and variables to make this more flexible (and practical).

 

 



Want to teach yourself Access? Free online guide at About Access Databases

VBA Keywords: FreeFile, Do...Loop, Write, Input, Output, EOF, Dubug.Print & Close.
Home | Terms of Use | Privacy Policy | Contact

copyright 2010 TP Development & Consultancy Ltd, All Rights Reserved.

All trademarks are copyrighted by their respective owners. Please read our terms of use and privacy policy.