In VBA (Visual Basic for Applications), the Write # statement is used for writing data to a sequential file with each value separated by a comma and enclosed in quotes if it’s a string. This is particularly useful for creating CSV (Comma-Separated Values) files or other text files where data needs to be easily parsed.
Here’s how you can use the Write # statement:
Basic Syntax
VB
Write #filenumber, [outputlist]- filenumber: This is the file number used in the Open statement.
- outputlist: This is an optional list of expressions to be written to the file. The expressions in the list can be numeric, string, or date expressions.
Example Usage
Suppose you want to write some data to a CSV file:VB
This code will create a file named Example.csv with three lines of data. The data fields are separated by commas, and string values are enclosed in quotes.
Dim fName As String
fName = "C:Example.csv"
' Open the file for output
Open fName For Output As #1
' Write data to the file
Write #1, "Name", "Age", "City"
Write #1, "Alice", 30, "New York"
Write #1, "Bob", 25, "Los Angeles"
' Close the file
Close #1String Delimiters and Commas
When you use Write #, VBA automatically encloses strings in quotes and separates elements with commas. This is useful for CSV file formatting. If a string itself contains a quote, VBA doubles the quote in the output file to maintain proper CSV format.Handling Dates and Numbers
Dates are written in the standard format (MM/DD/YYYY) unless the regional settings on your computer are different. Numeric values are written without quotes.Difference Between Write # and Print #
Write # inserts delimiters (commas) and encloses strings in quotes automatically. It’s ideal for creating CSV files.
Print # writes data in a more free-form manner, without adding delimiters or quotes unless you explicitly include them.
Error Handling
Always handle possible errors when working with file operations, such as using On Error statements to manage file not found, permission issues, or other I/O errors. The Write # statement is a straightforward way to create formatted text files in VBA, particularly useful for exporting data in a universally readable format like CSV.Unlock Your Potential
Excel
Access
Power BI
