DIRECTORY LIST - files or folders to Calc

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
JohnV
Volunteer
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

DIRECTORY LIST - files or folders to Calc

Post by JohnV »

This code is in response to the post post below but thought others might find it useful.
http://user.services.openoffice.org/en/ ... =9&t=41211
There is plenty of room for errors here but I've tried to trap the obvious ones. If the macro does not report "Done" within a second or two then an error has occurred and you should kill the macro.

You decide:
the directory,
sub-directories or files,
if files, what type,
where the list starts and
whether it is in rows or columns.

Code: Select all

Sub ListSubDirectoriesOrFiles
Dim url,sAns,iAns,a,DorF,oDoc,oSheet,Dname,Col,Row
url = "C:\users\cat\" 'Enter your basic path here. You can not list anything from these named directories.
oDoc = ThisComponent
oSheet = oDoc.Sheets(0) 'Sheet1
Directory:
sAns = InputBox("Enter directory name." & Chr(13) & Chr(13) & "Examples: documents or documents\embroidery designs" 
If sAns = "" then MsgBox "Cancel pressed. Quitting." : End
url = ConvertToURL(url & sAns)
If Not FileExists(url) then MsgBox "No such directory. Try again." : Goto Directory
a = "List the names of what?" & Chr(13) & "Yes = sub-directories" & Chr(13) & "No = files"
iAns = MsgBox(a,4,"List what?")
If iAns = 7 then 
 DorF = 0
 Else DorF = 16
EndIf
ext = "*"
If DorF = 0 then
 ext1 = InputBox("Enter the desired file type extention. Examples: odt for Writer files or * for all files."
 If ext1 = "" then MsgBox("Cancel pressed. Quitting.") : End
 ext = ext & "." & ext1 
EndIf
InitialCell:
Cname = InputBox("Enter the name of the cell that should recieve the initial name, e.g., A2.","Start list where?")
If Cname = "" then MsgBox("No entry made. Quitting.") : End
On Error goto E1 
oCell = oSheet.getCellRangeByName(Cname)
a = "List should go in what direction?" & Chr(13) & "Yes = down" & Chr(13) & "No = across"
iAns = MsgBox(a,4,"List down or across?")
If iAns = 7 then 
  DA = "A"
 Else DA = "D"
EndIf   
url = url & "/" & ext 
Col = oCell.CellAddress.Column
Row = oCell.CellAddress.Row
Dname = Dir(url,DorF)
Do While Dname <> ""
 If Dname <> "." And Dname <> ".." then
  oCell = oSheet.GetCellByPosition(Col,Row)  
  oCell.String = Dname
  If DA = "D" then
    Row = Row + 1 
   Else Col = Col +1
  EndIf
 EndIf
 Dname = Dir
Loop
MsgBox "Done"
End
E1:
MsgBox "'" & Cname & "' is an illegal cell name. Try again." : Goto InitialCell  
End Sub
Kees1
Posts: 9
Joined: Mon Dec 05, 2011 11:36 pm

Re: DIRECTORY LIST - files or folders to Calc

Post by Kees1 »

Dear JohnV,

You wrote "This code is in response to the post post below but thought others might find it useful." I am one of the others who find it very useful, because this code whas also fully applicable for my application!
I have tested the code in the debugger mode but initially it did not work, because I firstly had to modify your url C:\users\cat\ into my path and then it was OK.

I like to sort out missing numbers in a long serie of photofilenames and this app. can assist me.

I googled and found also ASAP Utilities wich has a similar function, but that package is based on VBA of Microsoft and will not (yet) work for OpenOffice Calc.
See also their remark at http://www.asap-utilities.com/faq-quest ... .php?m=106

Thanks once more.
Kees1
Open Office 4.1.7
Windows 10 64-bits
JohnV
Volunteer
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: DIRECTORY LIST - files or folders to Calc

Post by JohnV »

Glad to have been a help.
tgkprog
Posts: 2
Joined: Fri Mar 16, 2012 5:29 pm

Re: DIRECTORY LIST - files or folders to Calc

Post by tgkprog »

Thank you this code helped me get started too. ( edited out part of post and deleted what did not belong here)
Last edited by tgkprog on Sun Mar 18, 2012 3:20 pm, edited 1 time in total.
OpenOffice 3.2.1 (9502) Win 7 SP2 http://sel2in.com
niittymaa
Posts: 1
Joined: Fri Aug 23, 2013 10:47 pm

Re: DIRECTORY LIST - files or folders to Calc

Post by niittymaa »

Thanks for this macro. It works great in my case!
OpenOffice 4.0 on Windows 8
SmallMe
Posts: 4
Joined: Sat Feb 07, 2015 10:27 am

[Solved] DIRECTORY LIST - files or folders to Calc

Post by SmallMe »

Doesn't work for smallMe, I only get "No such directory. Try again." regardless of my input...

Any help or clew? :crazy: :roll:
Last edited by SmallMe on Sun Feb 15, 2015 1:06 pm, edited 1 time in total.
OpenOffice 4.1.1 on Windows 7 Ultimate x64
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: DIRECTORY LIST - files or folders to Calc

Post by Hagar Delest »

Have you seen the line:
url = "C:\users\cat\" 'Enter your basic path here.
And updated the "C:\users\cat\" string? So that it points to an existing folder?
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: DIRECTORY LIST - files or folders to Calc

Post by RusselB »

I'm having the same problem as reported by SmallMe
As I understand the code, and I may be incorrect, in the first pop-up box you enter the drive and pathname for the directory that you want to use.
I tried entering C:\downloads for my default downloads directory, which does exist. When I got the message saying No such directory, I thought, OK.. maybe it doesn't need the C:\ part as that's the main drive already specified.
Then I tried just the pathname, without the drive indicator.. no difference
So then I tried just the drive, using c:\ .. again no difference.

<Edit 1> hmm... looking at the code again, it appears that the base directory has to be manually coded into the macro, and changed each time a different directory is to be used.. that's a pain.

<Edit 2> Confirmed... By manually changing the URL entry in the macro to C;\ then entering downloads in the input box, I got the results
This change means that using it for a list of directories or files from a root will not work.

Note that while it does ask for which cell to start the entries in, it defaults to using sheet name of Sheet1 irrelevant as to what sheet you are using.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
SmallMe
Posts: 4
Joined: Sat Feb 07, 2015 10:27 am

Re: DIRECTORY LIST - files or folders to Calc

Post by SmallMe »

Hagar Delest wrote:Have you seen the line:
url = "C:\users\cat\" 'Enter your basic path here.
And updated the "C:\users\cat\" string? So that it points to an existing folder?
Sure, double of course I indeed seen that! However the brain of SmallMe was blonding out on the question if the concept of a Table was invented by a German brain... :crazy:
well when the Blonding session was over I notice that I have no idea what dose "cat" means...! A small mewo escaped SmallMe I'm a blond cat with a fetish to jump little critical sections on my reading list :(

Sorry dude, I must make me smile for some all the time as much as possible ;)
I didn't know what C:\users\cat\ is, I still don't but this forum is good place to be :)
OpenOffice 4.1.1 on Windows 7 Ultimate x64
SmallMe
Posts: 4
Joined: Sat Feb 07, 2015 10:27 am

Re: DIRECTORY LIST - files or folders to Calc

Post by SmallMe »

RusselB wrote: <Edit 1> hmm... looking at the code again, it appears that the base directory has to be manually coded into the macro, and changed each time a different directory is to be used.. that's a pain.

KiloTanxs :super:
Small me see, SmallMe Do :bravo:

About the pain you mentioned: True, however a progress need a reason to be :geek:
OpenOffice 4.1.1 on Windows 7 Ultimate x64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DIRECTORY LIST - files or folders to Calc

Post by Villeroy »

The UNO folder picker is easy to use

Code: Select all

Function getFolder(sTitle AS String, optional sInitDir) AS String
	oPicker = CreateUnoService("com.sun.star.ui.dialogs.FolderPicker")
	oPicker.setTitle(sTitle)
	if not ismissing(sInitDir) then oPicker.setDisplayDirectory(sInitDir)
	if oPicker.execute() then getFolder = oPicker.getDirectory()
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Hagar Delest
Moderator
Posts: 32627
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: DIRECTORY LIST - files or folders to Calc

Post by Hagar Delest »

Very nice!
Here is the tweaked code:

Code: Select all

Sub ListSubDirectoriesOrFiles
Dim url,iAns,a,DorF,oDoc,oSheet,Dname,Col,Row
oDoc = ThisComponent
oSheet = oDoc.Sheets(0) 'Sheet1
url = getFolder("List folder content",)
a = "List the names of what?" & Chr(13) & "Yes = sub-directories" & Chr(13) & "No = files"
iAns = MsgBox(a,4,"List what?")
If iAns = 7 then
   DorF = 0
Else DorF = 16
EndIf
ext = "*"
If DorF = 0 then
   ext1 = InputBox("Enter the desired file type extention. Examples: odt for Writer files or * for all files."
      If ext1 = "" then MsgBox("Cancel pressed. Quitting.") : End
   ext = ext & "." & ext1
EndIf
InitialCell:
Cname = InputBox("Enter the name of the cell that should recieve the initial name, e.g., A2.","Start list where?")
If Cname = "" then MsgBox("No entry made. Quitting.") : End
On Error goto E1
oCell = oSheet.getCellRangeByName(Cname)
a = "List should go in what direction?" & Chr(13) & "Yes = down" & Chr(13) & "No = across"
iAns = MsgBox(a,4,"List down or across?")
If iAns = 7 then
   DA = "A"
Else DA = "D"
EndIf   
url = url & "/" & ext
Col = oCell.CellAddress.Column
Row = oCell.CellAddress.Row
Dname = Dir(url,DorF)
Do While Dname <> ""
   If Dname <> "." And Dname <> ".." then
      oCell = oSheet.GetCellByPosition(Col,Row) 
      oCell.String = Dname
      If DA = "D" then
         Row = Row + 1
      Else Col = Col +1
      EndIf
   EndIf
   Dname = Dir
Loop
MsgBox "Done"
End
E1:
MsgBox "'" & Cname & "' is an illegal cell name. Try again." : Goto InitialCell 
End Sub

Function getFolder(sTitle AS String, optional sInitDir) AS String
   oPicker = CreateUnoService("com.sun.star.ui.dialogs.FolderPicker")
   oPicker.setTitle(sTitle)
   if not ismissing(sInitDir) then oPicker.setDisplayDirectory(sInitDir)
   if oPicker.execute() then getFolder = oPicker.getDirectory()
End Function
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: DIRECTORY LIST - files or folders to Calc

Post by RusselB »

Is it possible to give this an option to include files/directories that are in sub-directories?
As it is now, you just get the entries directly off of the main directory that is selected (love the selection interface, btw)

If you're familiar with DOS, I'm looking for an option that would give the equivalent of including the /s switch for the dir command. As it is now, it's the equivalent of dir /ad or dir /a-d
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
SmallMe
Posts: 4
Joined: Sat Feb 07, 2015 10:27 am

Re: DIRECTORY LIST - files or folders to Calc

Post by SmallMe »

RusselB wrote:Is it possible to give this an option to include files/directories that are in sub-directories?
As it is now, you just get the entries directly off of the main directory that is selected (love the selection interface, btw)

If you're familiar with DOS, I'm looking for an option that would give the equivalent of including the /s switch for the dir command. As it is now, it's the equivalent of dir /ad or dir /a-d
:super: :super: :bravo: :super: :super:
OpenOffice 4.1.1 on Windows 7 Ultimate x64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DIRECTORY LIST - files or folders to Calc

Post by Villeroy »

RusselB wrote:Is it possible to give this an option to include files/directories that are in sub-directories?
In Basic? Difficult. Basic fetches either files OR directories one by one. Write a routine that fetches directories and the files from the init dir and then calls itself recursively for each sub-directory.
Dump the result in some nested data array and implement the whole thing as an array function to be called like this:
=RECURSE(init_path) [Ctrl+Shift+Enter]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: DIRECTORY LIST - files or folders to Calc

Post by RusselB »

I'd be willing to give this a try, but I have no idea as to what commands are available.
If you know of a help where I can see the command options and configuration requirements.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DIRECTORY LIST - files or folders to Calc

Post by Villeroy »

Almost all StarBasic functions are documented in the F1-help. There is a Dir function which takes an initial directory (URL or sys-path) and a flag 0 (default, normal files) or 16 (folders). The especially crude thing is the way how you "initialize" it with one or two arguments and then loop with no arguments until it runs empty.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: DIRECTORY LIST - files or folders to Calc

Post by RusselB »

Thanks.. I had looked in the help file (F1), but I could very well have been searching incorrectly as I wasn't sure what to search for.
I will look again later and see what I can figure out.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DIRECTORY LIST - files or folders to Calc

Post by Villeroy »

 Edit: 2020-12-12: Script is compatible with Python 3 (LibreOffice) and still with Python 2.7 (AOO). Wrapped into text document with Basic installer 
The attached archive contains a Python module which can be extracted to <userprofile>/Scripts/python/
Then you run Tools>Macros>Organize>Python>DirListing>DirListToActiveCell, pick a file from the file picker dialog and get a pretty fast listing in URL notation dumped to the area at the active cell. I thought that URLs are more useful than system files because of the way how Calc builds hyperlinks and other links to external data.
It dumps 50,000 URLs within 10 seconds or so unless your path includes a mounted network folder which may take some time of transfer.
Unix symlinks pointing to other directories won't be expanded.
It lists all files including any "hidden" files.
Any file access errors are ignored silently. It seems to ignore any such files.
The file picker uses the current spreadsheet's path as default path (if any) or the default document path if the new document isn't stored anywhere yet.

The main routine gets the root directory, the active cell and the data array, calculates the size of the target range and finally calls setDataArray.
The routine which generates the data array makes use of Python function os.walk which walks recursively through the subdirs of a given dir.
os.walk is documented here: https://docs.python.org/2/library/os.html
Attachments
DirListing.py.odt
Dump recursive directory listing to active cell
(24.76 KiB) Downloaded 281 times
Last edited by Villeroy on Sun Dec 13, 2020 6:50 pm, edited 5 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: DIRECTORY LIST - files or folders to Calc

Post by RusselB »

OK, downloaded the script and installed it per your directions.
Opened a brand new spreadsheet and ran the code, again, per your directions and got the following error
Capture3.PNG
Capture3.PNG (6.91 KiB) Viewed 29383 times
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DIRECTORY LIST - files or folders to Calc

Post by Villeroy »

RusselB wrote:OK, downloaded the script and installed it per your directions.
Opened a brand new spreadsheet and ran the code, again, per your directions and got the following error
Sorry, I can't reproduce the problem with AOO 4.1.1 on Windows, LO 3.5 and AOO 4.1.1 on Linux.
 Edit: Fixed another problem which prevented the macro to be called from UI elements such as toolbars, shortcuts and menu entries. 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply