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