Apache OpenOffice Community Forum

Posted: **Mon May 23, 2011 4:39 pm**

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

Posted: **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

Posted: **Tue Dec 06, 2011 11:58 pm**

Glad to have been a help.

Posted: **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)

Posted: **Fri Aug 23, 2013 10:48 pm**

Thanks for this macro. It works great in my case!

Posted: **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?

Posted: **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?

Posted: **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.

Posted: **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...

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

Posted: **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

Small me see, SmallMe Do

About the pain you mentioned: True, however a progress need a reason to be

Posted: **Sat Feb 14, 2015 1:01 pm**

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

Posted: **Sun Feb 15, 2015 12:18 am**

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

Posted: **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

Posted: **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

Posted: **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]

Posted: **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.

Posted: **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.

Posted: **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.

Posted: **Mon Feb 16, 2015 6:54 pm**

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

Posted: **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 (6.91 KiB) Viewed 29534 times

Posted: **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.

Apache OpenOffice Community Forum

DIRECTORY LIST - files or folders to Calc

DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

[Solved] DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc

Re: DIRECTORY LIST - files or folders to Calc