Excel VBA- File handlings

By Ajeet Kumar

FILE HANDLING IN VBA

OPEN STATEMENT

OPEN Statement is used to open a file into one of the five modes- INPUT, OUTPUT, APPEND, RANDOM OR BINARY. There are 6 arguments available to OPEN method. These are

1.       File name as string data type

2.       Mode-- Input, Output, Append, Random Or Binary

3.       Access restriction—Read, Write, Read Write

4.       Lock type—Shared, Lock Read, Lock Write, Lock Read Write

5.       File number—any integer number between 1 to 255

6.       Length of records

The first and 5^th arguments are required and others are optional.

Syntax—

OPEN “STRFILENAME” [FOR MODE] [ACCESSRESTRICTION] [LOCKTYPE] AS [#] INTFILENUMBER [LEN=INTRECORDLENGTH]

Example—

Open “myTextFile.txt” as #1

By default, the file is opened in random mode.

Next time, the file is referenced by the file number, not by its name. For example, to close the above file, we code:

Close #1

Freefile() function can be used to assign a file number to the file to be opened.

Input mode is used to read an existing file. Output mode is used to write to a file, no matter whether the file exists or not. If file does not exist, it is created. The old data is overwritten in this mode. To append the records, we use append mode. The sequential files are read from beginning to the end. To read any random record, we should use the random mode.

HOW TO READ OR WRITE A SEQUENTIAL FILE

To read/ write a sequential file INPUT, PRINT and WRITE statements are used. Their syntax is straight forward:

INPUT # INTFILENUMBER, VARIABLES-LIST-COMMA-SEPARATED

PRINT # INTFILENUMBER, VARIABLES-LIST-COMMA-SEPARATED

WRITE # INTFILENUMBER, VARIABLES-LIST-COMMA-SEPARATED

WRITE # is used mainly for comma separated files.

EXAMPLE1

Sub pReadTextFile()

    Dim varData

    Dim strFileName As String

    Dim intFileNumber As Integer

    With Application.FileDialog(msoFileDialogFilePicker)

        .AllowMultiSelect = False

        .Title = "Choose the file"

        .Show

        If .SelectedItems.Count = 0 Then

            MsgBox "Cancelled"

            Exit Sub

        Else

           strFileName = .SelectedItems(1)

        End If

    End With

   

    intFileNumber = FreeFile()

    Open strFileName For Input As #intFileNumber

    Input #intFileNumber, varData

    MsgBox Trim(varData)

    '' read the next line

    Input #intFileNumber, varData

    MsgBox Trim(varData)

End Sub