In the More Windows batch files tutorial we learnt to pipe output to a text file using a batch file. We will now learn how to do something similar using VBA. We need to get a handle on a file, open the file and write to it, close the file and release it.
In this tutorial:
Required knowledge:
01. Open statement
VBA’s Open statement is just one of a number of methods for outputting text to a file.
02. Syntax
The syntax for the command is as follows: [1]
Open pathname For mode [ Access access ] [ lock ] As [ # ] filenumber [ Len = reclength ]
Part | Description |
---|---|
pathname | Required. String expression that specifies a file name; may include directory or folder, and drive. |
mode | Required. Keyword specifying the file mode: Append, Binary, Input, Output, or Random. If unspecified, the file is opened for Random access. |
access | Optional. Keyword specifying the operations permitted on the open file: Read, Write, or Read Write. |
lock | Optional. Keyword specifying the operations restricted on the open file by other processes: Shared, Lock Read, Lock Write, and Lock Read Write. |
filenumber | Required. A valid file number in the range 1 to 511, inclusive. Use the FreeFile function to obtain the next available file number. |
reclength | Optional. Number less than or equal to 32,767 (bytes). For files opened for random access, this value is the record length. For sequential files, this value is the number of characters buffered. |
03. Example
Learn how to Activate the Developer toolbar.
Once the code below is implemented in a Word document it will create and write to a log file the details of every time the Word document is opened. [2]
- Create a new Word document and save it as a .docm: File » Save As » Word Macro-Enabled Document (*.docm)
- Navigate to the Developer menu and click on the Visual Basic button
- In the Project window on the left, double-left-click on the This Document object
- At the top of the code window, select the Document option from the drop-down list (which displays (General) by default)
- The default Procedure is Open() so it need not be changed
- Type
Option Explicit
at the top - The code should now look as follows:
Option Explicit Private Sub Document_Open() End Sub
Add the following code to the procedure:
Dim logname As String Dim logmessage As String Dim thedte As String thedte = CStr(Format(Now(), "yyyy-MM-dd hh-mm-ss")) logname = "log-for-word-doc.txt" logmessage = "Document opened by user on " + thedte Open logname For Append As #1 Print #1, logmessage Close #1
Completed code:
Option Explicit Private Sub Document_Open() Dim logname As String Dim logmessage As String Dim thedte As String thedte = CStr(Format(Now(), "yyyy-MM-dd hh-mm-ss")) logname = "log-for-word-doc.txt" logmessage = "Document opened by user on " + thedte Open logname For Append As #1 Print #1, logmessage Close #1 End Sub
04. Enhancement
We can make the code portable and more useful if we use VBA’s Environ function to create a more meaningful log message:
logmessage = "Document opened on " + VBA.Environ("COMPUTERNAME") + " used by " + VBA.Environ("USERNAME") + " on " + thedte
Full code:
Option Explicit Private Sub Document_Open() Dim logname As String Dim logmessage As String Dim thedte As String thedte = CStr(Format(Now(), "yyyy-MM-dd hh-mm-ss")) logname = "log-for-word-doc.txt" logmessage = "Document opened on " + VBA.Environ("COMPUTERNAME") + " used by " + VBA.Environ("USERNAME") + " on " + thedte Open logname For Append As #1 Print #1, logmessage Close #1 End Sub
References:
- o365devx (no date) Writing data to files (VBA) | Microsoft Learn. Available at: https://learn.microsoft.com/en-us/office/vba/language/concepts/getting-started/writing-data-to-files (Accessed: 13 July 2024).
- o365devx (no date) Open statement (VBA) | Microsoft Learn. Available at: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/open-statement (Accessed: 16 July 2024).