XL: Put Statement Allows Writing Beyond End of File

• Microsoft Excel for Windows version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows

SUMMARY

In Microsoft Excel, when you use the Put statement in a Visual Basic for Applications procedure, there is no limit to where you can start writing data to a file. Therefore, it is possible to write data well beyond the current end of a file.

This behavior is compatible with earlier versions of Visual Basic.

MORE INFORMATION

You can use the Put statement to place data in a file at a specified location. However, when you write data beyond the end of a file, the gap between the end of file and the starting point of new data is filled with random data.

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

In order to see this behavior, you must first create a sample file. Follow these steps to create a sample file:

1. On the Start Menu, point to Programs, point to Accessories, and then click Notepad.

2. Type "ABC" (without the quotation marks) into the blank Notepad document.

3. Click Save on the File Menu. In the Save As dialog box, open the My Documents folder. Then, type "Test" (without the quotation marks) in the File name box, and click Save.

4. On the File menu, click Exit.

   This creates a file called Test.txt in the My Documents folder.

1. Close and save any open workbooks, and then open a new workbook.

2. Start the Visual Basic Editor (press ALT+F11).

3. On the Insert menu, click Module.

4. In the module type the following code:

   Type Test_Record

        Test As String * 7
   

   End Type

      Sub PutTest()
        Dim P As Test_Record
        Dim filenum As Integer
   
        filenum = FreeFile()
   
        'Open the file you created.
        Open "c:\My Documents\Test.txt" For Random As filenum Len = Len(P)
   
          P.Test = "Testing"
   
          'Write a record to the file.
          Put #filenum, 2, P
   
        'Close the file.
        Close #filenum
      End Sub
   
   

5. Run the PutTest macro.

6. Open the file Text.txt in Notepad.

For additional information about Working with Random Access Files, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q150700
   TITLE     : Excel: How to Work with Random Access Files

REFERENCES

For more information about the Put statement, click the Index tab in Microsoft Visual Basic Help, type the following text

   put