VBA Put statement, which is used to write data to a file or device. In this blog post, we will discuss the purpose, syntax, examples, important notes and remarks, and conclude with asking for feedback and views on the Put statement in VBA.
VBA Put Statement
Purpose of the Put statement
The primary purpose of the Put statement is to write data to a sequential file or device. This statement allows you to create, write, and append data to an existing file or device. It is often used in conjunction with the ‘Open’ statement, which opens a file for input or output. The Put statement is especially useful for writing records and fields to a file in a specific format.
Syntax of the Put statement
The syntax for the Put statement is simple and follows the general structure of VBA commands. It is written as follows:
Put [#]fileNumber, [recordNumber], data
- The # symbol indicates the file number or device number to which data is being written.
- FileNumber is a unique identifier for an open file or device. It can be an integer from 1 to 511.
- The recordNumber is an optional argument that specifies the record number in the file to which data will be written. If this argument is omitted, data will be written to the next record in the file.
- The data argument can be a constant, a variable, or an expression that is being written to the file.
Examples of using VBA Put statement
Let’s take a look at some examples of how the Put statement can be used in VBA.
Example 1: Writing data to a text file
In this example, we will use the Put statement to write a string of text to a text file.
Dim fileNumber As Integer fileNumber = FreeFile 'gets the next available file number Open "C:\Users\Username\Desktop\sample.txt" For Output As #fileNumber 'opens the file for output Put #fileNumber, , "This is a sample text file." 'writes data to the file Close #fileNumber 'closes the file
This code will create a file named “sample.txt” on the user’s desktop and write the specified text to the file. If the file already exists, it will overwrite the existing content. The Put statement is used in conjunction with the ‘Open’ statement to specify the file number and mode of operation.
Example 2: Writing a record to a binary file
In this example, we will use the Put statement to write a record to a binary file. A binary file is a file that stores data in a binary format, which consists of 1s and 0s.
Type record
name As String * 15
age As Integer
End Type
Dim fileNumber As Integer
fileNumber = FreeFile 'gets the next available file number
Open "C:\Users\Username\Desktop\sample.dat" For Random As #fileNumber Len = Len(record) 'opens the file for random access
Dim myRecord As record
myRecord.name = "John Smith"
myRecord.age = 30
Put #fileNumber, , myRecord 'writes the record to the file
Close #fileNumber 'closes the file
The Put statement can be used to write user-defined data types, structures, and records to a file, making it a powerful tool for handling complex data.
Example 3: Appending data to an existing file
The Put statement can also be used to append data to an existing file instead of overwriting it. This is done by using the ‘Append’ mode in the ‘Open’ statement.
Dim fileNumber As Integer fileNumber = FreeFile 'gets the next available file number Open "C:\Users\Username\Desktop\sample.txt" For Append As #fileNumber 'opens the file for appending Put #fileNumber, , "This is new data being added to the end of the file." Close #fileNumber 'closes the file
If the file “sample.txt” already exists, the Put statement will append the new data to the end of the file instead of overwriting it.
Example 4: Writing data to a network file
The Put statement is not limited to writing data to local files. It can also be used to write data to a network file using the UNC (Universal Naming Convention) path.
Dim fileNumber As Integer fileNumber = FreeFile 'gets the next available file number Open "\\Server\Share\Folder\sample.txt" For Output As #fileNumber 'opens the network file for output Put #fileNumber, , "This is data being written to a network file." Close #fileNumber 'closes the file
This code will write the specified data to the file “sample.txt” located on the network share. However, the user must have the necessary permissions to access the file.
Example 5: Writing data to a device
The Put statement can also be used to write data to a device, such as a printer or a serial port.
Dim fileNumber As Integer fileNumber = FreeFile 'gets the next available file number Open "LPT1:" For Output As #fileNumber 'opens the printer port for output Put #fileNumber, , "This is data being sent to the printer." Close #fileNumber 'closes the printer port
This code will send the specified data to the printer connected to the LPT1 port. The Put statement can also be used to write data to other devices, such as the COM port or the parallel port.
Important Notes and Remarks
- The Put statement can only be used to write data to text, binary, or device files. It cannot be used to write data to a worksheet or a range in Excel.
- When writing data to a binary file, the file must be opened in the ‘Random’ mode.
- When using the Put statement to write data to a file or device, the file or device must first be opened with the ‘Open’ statement.
- When using the Put statement, the order in which data is written to a file must match the order in which it is read from the file using the ‘Get’ statement.
- If the file or device cannot be opened, an error will occur. Therefore, it is important to handle potential errors when using the Put statement, using the ‘On Error’ statement.
The Put statement is an essential tool for writing data to files and devices in VBA. Its simple syntax and versatility make it a valuable asset for manipulating data. In this blog post, we discussed the purpose and syntax of the Put statement, along with examples of how it can be used. We also highlighted some important notes and remarks to keep in mind when using this statement.
Now, we would love to hear your feedback and views on the Put statement in VBA. Have you used it in your projects? Are there any other examples you would like to share? Let us know in the comments below.
