Input Ouput External Files

The capability of reading external files with various structures was the reasons I focused on Excel. Although creating spreadsheets for external files can be useful to collect and display information it has some limitations. The maximum number of rows in a spreadsheet is 65K. The feature to read and/or write a file without creating a spreadsheet removes this limitation and gives more control to the user. Excel can input each record of a file, modify selected records and output these records to another file. Each line can be changed with Excel extensive text editing.

The following VBA code will create a file in the current directory and make a copy. This techniques have many applications.

SAS Fifth Dimension Macro-Input Output

Review the following code, copy it to Excel and execute it.

Sub INPUT_OUTPUT()
    ChDir "C:\"   'Select your directory
 ' Create file
    FILE_NAME = "Test.shtml"
    Open FILE_NAME For Output As #1 
    For II = 1 To 100
       TEXTLINE = "RECORD NUMBER =" & II
       Print #1, TEXTLINE           
    Next
    Close #1
' Copy file just created 
   Open FILE_NAME For Input As #1 
    FILE_NAME = "COPY_of_" & FILE_NAME
    Open FILE_NAME For Output As #2 
    NUM = 0
    Do While Not EOF(1)             
       NUM = NUM + 1
       Line Input #1, TEXTLINE      
       Print #2, TEXTLINE           
    Loop
    MsgBox "Number of records=" & NUM
    Close #1                        ' Close input file
    Close #2                        ' Close output file
End Sub