How to use FileAttr function in VBA?

In VBA (Visual Basic for Applications), the `FileAttr` function is used to return the file mode for files opened with the `Open` statement. It’s important to note that this function is not commonly used and is considered a legacy function; in modern VBA, you’d typically use the higher-level file handling objects like `Scripting.FileSystemObject`.

However, if you are working with legacy code or have a specific reason to use it, here’s how you do:

First, you must open a file using the `Open` statement to get a file number which you then pass to the `FileAttr` function to get the file mode.

Here is the syntax of the `Open` statement:

Open pathname For mode [Access access] [lock] As [#]filenumber [Len=reclength]

Here is an example showing how to use the `FileAttr` function:

Sub UseFileAttr()

    Dim intFileNumber As Integer
    Dim strFilePath As String
    Dim intFileMode As Integer
    
    ' Specify the file path
    strFilePath = "C:pathtoyourfile.txt"
    
    ' Get an available file number
    intFileNumber = FreeFile
    
    ' Open the file for Binary Access Read
    Open strFilePath For Binary Access Read As #intFileNumber
    
    ' Using FileAttr to get the file mode
    intFileMode = FileAttr(intFileNumber, 2) ' 2 is the argument to get the mode info
    
    ' Print the file mode to the Immediate Window (Debug.Print is mainly for debugging)
    Debug.Print "The file mode is: " & intFileMode
    
    ' Close the file
    Close #intFileNumber
    
End Sub

In the above code, the `FileAttr` function takes two parameters:

`filenumber`: The file number used in the `Open` statement.
`returntype`: For `FileAttr`, this must be `2` to return the mode of the file.

The `intFileMode` returned by `FileAttr` will be a numerical value that represents the file mode:

`1` = Input mode
`2` = Output mode
`4` = Random mode
`8` = Append mode
`32` = Binary mode

Note that these constant values correspond to the way that the file was opened.

Please ensure that you handle any potential errors when dealing with file IO, such as files not being found or permissions issues, by incorporating appropriate error handling in your VBA code.

facebooklinkedin