DIRECTORY LIST - files or folders to Calc

Creating Extension - 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 forum is not for asking questions about writing your own macros.

DIRECTORY LIST - files or folders to Calc

Postby JohnV » Mon May 23, 2011 4:39 pm

This code is in response to the post post below but thought others might find it useful.
viewtopic.php?f=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   Expand viewCollapse view
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
JohnV
Volunteer
 
Posts: 1584
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: DIRECTORY LIST - files or folders to Calc

Postby Kees1 » Mon Dec 05, 2011 11:50 pm

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 3.3.0 (Build 5967)
Windows XP 32-bits
Kees1
 
Posts: 1
Joined: Mon Dec 05, 2011 11:36 pm

Re: DIRECTORY LIST - files or folders to Calc

Postby JohnV » Tue Dec 06, 2011 11:58 pm

Glad to have been a help.
JohnV
Volunteer
 
Posts: 1584
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: DIRECTORY LIST - files or folders to Calc

Postby tgkprog » Fri Mar 16, 2012 8:50 pm

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
tgkprog
 
Posts: 2
Joined: Fri Mar 16, 2012 5:29 pm

Re: DIRECTORY LIST - files or folders to Calc

Postby niittymaa » Fri Aug 23, 2013 10:48 pm

Thanks for this macro. It works great in my case!
OpenOffice 4.0 on Windows 8
niittymaa
 
Posts: 1
Joined: Fri Aug 23, 2013 10:47 pm

[Solved] DIRECTORY LIST - files or folders to Calc

Postby SmallMe » Tue Feb 10, 2015 2:55 pm

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
SmallMe
 
Posts: 4
Joined: Sat Feb 07, 2015 10:27 am

Re: DIRECTORY LIST - files or folders to Calc

Postby Hagar Delest » Fri Feb 13, 2015 11:54 pm

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?
AOO 4.1.4 on Xubuntu 17.10, (Artful Aardvark) and 4.1.3 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 27661
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: DIRECTORY LIST - files or folders to Calc

Postby RusselB » Sat Feb 14, 2015 12:48 am

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.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4161
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: DIRECTORY LIST - files or folders to Calc

Postby SmallMe » Sat Feb 14, 2015 2:54 am

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

Postby SmallMe » Sat Feb 14, 2015 3:24 am

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
SmallMe
 
Posts: 4
Joined: Sat Feb 07, 2015 10:27 am

Re: DIRECTORY LIST - files or folders to Calc

Postby Villeroy » Sat Feb 14, 2015 1:01 pm

The UNO folder picker is easy to use
Code: Select all   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DIRECTORY LIST - files or folders to Calc

Postby Hagar Delest » Sun Feb 15, 2015 12:18 am

Very nice!
Here is the tweaked code:
Code: Select all   Expand viewCollapse view
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
AOO 4.1.4 on Xubuntu 17.10, (Artful Aardvark) and 4.1.3 on Windows 7 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 27661
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: DIRECTORY LIST - files or folders to Calc

Postby RusselB » Sun Feb 15, 2015 1:05 am

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.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4161
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: DIRECTORY LIST - files or folders to Calc

Postby SmallMe » Sun Feb 15, 2015 6:21 am

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
SmallMe
 
Posts: 4
Joined: Sat Feb 07, 2015 10:27 am

Re: DIRECTORY LIST - files or folders to Calc

Postby Villeroy » Sun Feb 15, 2015 4:19 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DIRECTORY LIST - files or folders to Calc

Postby RusselB » Sun Feb 15, 2015 6:34 pm

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.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4161
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: DIRECTORY LIST - files or folders to Calc

Postby Villeroy » Sun Feb 15, 2015 7:13 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DIRECTORY LIST - files or folders to Calc

Postby RusselB » Sun Feb 15, 2015 8:03 pm

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.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4161
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: DIRECTORY LIST - files or folders to Calc

Postby Villeroy » Mon Feb 16, 2015 6:54 pm

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.zip
Dump recursive dir listing to active sheet cell
(1.22 KiB) Downloaded 232 times
Last edited by Villeroy on Tue Feb 17, 2015 12:43 am, edited 2 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: DIRECTORY LIST - files or folders to Calc

Postby RusselB » Mon Feb 16, 2015 10:38 pm

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 11375 times
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
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.
RusselB
Volunteer
 
Posts: 4161
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: DIRECTORY LIST - files or folders to Calc

Postby Villeroy » Tue Feb 17, 2015 12:23 am

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Code Snippets

Who is online

Users browsing this forum: No registered users and 1 guest