Files and Directories Processing Using LibreOffice Calc Macro using Basic

This article demonstrates how to process/read individual files and directories in the Unix file system using Basic and LibreOffice Calc Macro and put the file names/directory names in LibreOffice Calc cells.

For LibreOffice automation, it is necessary to know the file system and directory processing. This tutorial is based on the Unix file system, which is different from Windows.

Note: This tutorial assumes you know how to create and run a macro. If you are new, you can check out this tutorial first and return to this page.

Files and Directories processing using LibreOffice Macro

In this article, the macro would read the contents of an entire directory, find the files and directories, and list them in Calc cells. For example, the file system structure is similar to a typical Ubuntu system in the path /usr/include.

There are lots of Directories and Files. We want to list down the entire contents inside Calc cells.
Basic provides function Dir which can be used to read the contents of a Directory.

Syntax

Dir (path, attributes) As String

The first argument of the Dir function is the path string to be read. The second argument tells Dir function which type of elements in that path to be returned. Pass on “0” for files and “16” for Directories for the second argument. Both arguments are optional.

Once the first Dir call is successful, call the function again without argument inside a loop to get the same items from the function resulting listing of entire Directory contents.

Complete Macro

To run, create a new Calc file and Copy and paste this entire code block below in Macro Editor in LibreOffice and run.

Sub list_files()
    Dim i, strFile
    path ="/usr/include/"
    strFile = Dir(path,0)
    i = 1
    while strFile <> ""
        my_cell = ThisComponent.Sheets(0).getCellbyPosition(1,i)
        my_cell.String = strFile
        strFile = Dir ' returns next entry
        i = i + 1
    wend
End Sub

Sub list_directory()
    Dim i, strDir
    path ="/usr/include/"
    strDir = Dir(path, 16)
    i = 1
    while strDir <> ""
        my_cell = ThisComponent.Sheets(0).getCellbyPosition(2,i)
        my_cell.String = strDir
        strDir = Dir ' returns next entry
        i = i + 1
    wend
End Sub

If you are using Windows or mac, then change the path accordingly based on the respective OS’s structure.

Output

After running the macro, the contents of /usr/include should be available in Calc spreadsheet while using the above code in a test file. The First column would contain the Files, and the second column would contain the Directories.

Function References – Used in this article

Dir (path, attributes)

path: optional
attributes: optional
Return Value: String

List of attribute types: