Files and Directory Processing Using LibreOffice Calc Macro using Basic


This article demontrates how to process/read individual files and directories in 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 file system and directory processings. This tutorial is based on Unix file system which is different than Windoze.

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

Files and Directories inside usr-include

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


Dir (path, attributes) As String

First argument of the Dir function is the path string to be read. Second argument tells Dir function which type of elements in that path to be returned. Pass on “0” for file and “16” for Directories for second argument. Both the 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
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
End Sub


After run, the contents of /usr/include would be shown in Calc. First column would contain the Files and second column would contain the Directories.

List Files and Directories - Using Calc Macro

List Files and Directories – Using Calc Macro

Function References – Used in this article

Dir (path, attributes)

path: optional
attributes: optional
Return Value: String

List of attribute types:

Attribute Description
0 Normal (Default)
1 Read-only
2 Hidden
4 System file
8 Volume label
16 Directory or folder
64 File name is an alias


Looking for Something Else?

If you are looking for something else in LibreOffice macro tutorials, Or, wants to learn more about it, please follow below link for complete Macro Tutorials Index:

LibreOffice Macro Tutorial Index

You may also like...

  • NeckBeard The Pirate

    Hello, thanks for the article. I’m somewhat new to using linux and have been using LibreOffice as of recent as well. This might be a silly question, but would something like this work in linux?

    I’ve got an external hard drive with a ton of digital comics and pdfs, and I would like to create a database with all the different file names as well as directories that they are in. I’m not sure if it would be a 1-1 translation, or if I would have to do it a different way, and don’t want to mess anything up.

    • It really seems you’re a noob in NIX world, cuz you wouldn’t ask that if you knew better.

      The article writer is using some Linux distro with GNOME (my money is on Ubuntu), and officially GNOME doesn’t run on Unix, but it shouldn’t be impossible. Anyway, the answer is: absolutely.

      • NeckBeard The Pirate

        Yeah, I’ve used windows most of my life but just began using Linux last year after my brother showed me how to install it and dual boot it, and all I use now is LibreOffice. I’ve been slowly learning more about using Linux, I’ve got Xubuntu on one laptop and Ubuntu on another, and I’m probably going to try out a different distro on the laptop that I’m on now just for the experience.

    • Yes, you can and it is very much possible in any Linux distro. But I am wondering why you want to do that? Are you trying to build an application?

      • NeckBeard The Pirate

        It is more of a way of getting them organized/staying organized. I somewhat understated the amount of data, I have maybe about 8-10 different external hard drives of sizes ranging from 2-5 terabytes of different stuff I’ve accumulated over the last several years. I have a rough picture in my mind of a couple ways to organize things, but it would be much easier for me to do if I had all the file names and directories accessible. I’ve found some decent deals on 5 tb hard drives, and I plan on getting a few more this summer and backing up everything from my original drives onto them but in a completely organized fashion. I wish I had the expertise to build an app that would allow me to make a virtual hierarchy of exactly how I’d like multiple files from different drives all copied onto a single empty drive, I’m pretty sure it is going to be a long slog just waiting for the files to copy over not even counting the time it will take to get it organized decently.

        • §sudo tree -aU§ is one way to see what you have where, in a tree form with easy-to-understand graphics. It’ll print even hidden files, with everything sorted in the order they are in the folder.

          §gdmap§ is similar to Spacesniffer, but its graphics are totally shit, where Spacesniffer is clear. It’s for Window$, but works perfectly under Wine.

  • Mike Amaris

    Thank you

  • Seriously, ppl need to learn to use 4 spaces instead of 2 or 1. Anything below or above 4 is unreadable.

Translate »