XL: Put Statement Allows Writing Beyond End of File

ID: Q108385

The information in this article applies to:

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

Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

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.

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

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

Type ABC into the blank Notepad document.

Click Save on the File Menu. In the Save As dialog box, open the My Documents folder. Then, type Test in the File name box, and click Save.

On the File menu, click Exit.

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

Next, create a Visual Basic for Applications procedure to illustrate the behavior discussed in this article.

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

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

On the Insert menu, click Module.

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

Run the PutTest macro.

Open the file Text.txt in Notepad.

Note that random data is written between the entry that you made in the file and Testing, which is the record that the PutTest macro wrote to the file. For additional information about Working with Random Access Files, please see the following article in the Microsoft Knowledge Base:

Q150700 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

and then double-click the selected text to go to the "Put Statement" topic.

Additional query words: xl97

Keywords : kbprg kbdta KbVBA
Version : WINDOWS:5.0,7.0,97
Platform : WINDOWS
Issue type :