If you want to go straight to the problem skip to the paragraph that starts with “THE PROBLEM”
I am what is generally called a self-taught hobbyist VBA programmer. I have written (not recorded) well over 2,000 lines of VBA code mostly in VBA for Excel but some for Word, Access and Outlook. I consider myself pretty decent at VBA. For reasons I will not go into I embarked on a project that had to be done in LO 4.1. I did this despite the dire warnings of a very steep learning curve for people like me. I am old and retired with lots of time. Why not give it a shot. The code below took me about a week to write from a dead start knowing nothing about LO. The warnings were true. It was an order of magnitude more complicated than I expected. When I got it to run I celebrated but my celebration was premature. It only runs under certain conditions and that is the problem.
THE PROBLEM – The code below is contained (Oops I don’t have enough Karma for that). There is a main routine ‘buildFileList()’ and two subroutines and one function. The function ‘ChooseADirectory()’ I lifted from Andrew Pitonyak’s book. I take no credit for this.
If you are a trusting soul, the quickest way to see my problem is to open ‘FileLister V1.ods. (Oops I don’t have enough Karma to attach a file). You will have to attach the code to a spreadsheet. The macro is ‘buildFilelist()’. This is a very simple macro that builds a list of files in a folder and its subfolders recursively. The file name list is in column 1 and the type of file is in column 2. The starting folder is chosen by the user. Run ‘buildFileList’ and you will get a message window explaining the macro. Following that you will get a dialog window to choose a folder. Select one of your folders with just a few files. It can also have subfolders but just be sure the total files to list is reasonable. I have run the macro on my D drive which contains all my stuff. It took just over 2 minutes to list just over 26,000 files. The problem I am having will occur next. The basic editor window will open on the statement ‘FArray = ReadDirectories(anchorDir, true, true, false)’ with an error message ‘BASIC runtime error. Sub-procedure or function procedure not defined.’ However, if you go to the Object Catalog pane on the left and click the + next to ‘LibreOffice Macros & Dialogs’ and continue opening the view down to ‘LibreOffice Macros & Dialogs’>’Tools’>’UCB’ and then rerun ‘buildFileList’ it will run to completion. If you close the Basic Editor window and run it from Run Macro it will continue to work. Very strange why this is so. I am hoping that there is something simple that I, as a newbie, failed to do or understand.
option explicit
REM ***** BASIC *****
Public RootPath as string
Public rr as integer
Public FileLister as object 'main document
Public FileList as object ' sheet in 'FileLister'
Sub buildFileList()
FileLister = ThisComponent.CurrentController
FileList = ThisComponent.Sheets.getByName("File list")
FileLister.setActiveSheet(FileList)
FileList.ClearContents(com.sun.star.sheet.CellFlags.STRING)'clears string oontents
msgbox "This macro will list all the files in a folder and its subfolders" & chr(13) _
& "The type of file is listed in the second column" & chr(13) _
& "In the next dialog window choose the starting folder"
RootPath = ChooseADirectory()
if RootPath = "" then Exit sub
FileList.getCellByPosition(0, 0).string = convertFromURL(RootPath)
FileList.getCellByPosition(0, 1).string = "File name with path"
FileList.getCellByPosition(1, 1).string = "Type"
ListFiles RootPath 'do main loop of code
autoadjustcol 1, "A", false 'make selected cell A1
MsgBox "Listing of " & rr & " files complete"
End Sub
REM sInPath specifies the initial directory. If the initial directory
REM is not specified, then the user's default work directory is used.
REM The selected directory is returned as a URL.
Private Function ChooseADirectory(Optional sInPath) As String
Dim oDialog As Object
Dim oSFA As Object
Dim s As String
Dim oPathSettings
oDialog = CreateUnoService("com.sun.star.ui.dialogs.FolderPicker")
'xray(oDialog)
' oDialog = CreateUnoService("com.sun.star.ui.dialogs.OfficeFolderPicker")
oSFA = createUnoService("com.sun.star.ucb.SimpleFileAccess")
'xray(oSFA)
If IsMissing(sInPath) Then
oPathSettings = CreateUnoService("com.sun.star.util.PathSettings")
oDialog.setDisplayDirectory(oPathSettings.Work)
ElseIf oSFA.Exists(sInPath) Then
oDialog.setDisplayDirectory(sInPath)
Else
s = "Directory " & sInPath & " Does not exist"
If MsgBox(s, 33, "Error") = 2 Then Exit Function
End If
If oDialog.Execute() = 1 Then
ChooseADirectory() = oDialog.getDirectory()
End If
End Function
sub ListFiles(anchorDir)
' this routine calls the big main function ReadDirectores()
' it then transfer the resulting array to the spreadsheet FileList
dim FArray() as string
dim r as integer
dim shr as integer
dim z as boolean
dim longFname as string
dim coltwo as string
dim maxcoloneLen(1) as integer
dim maxcoltwoLen(1) as integer
Dim LfileCont(0) as String
LfileCont(0) = "Writer_ClarisWorks" ' use to get all files filtered on Writer_ClarisWorks
'use the line below to get only cwk files with type in column 2
' FArray = ReadDirectories(anchorDir, true, true, false, LfileCont())
'use the line below to get only cwk files with short file name in column 2
' FArray = ReadDirectories(anchorDir, true, true, true, LfileCont())
'use the line below to get all files with type in column 2
FArray = ReadDirectories(anchorDir, true, true, false)
rr = 0
maxcoloneLen(0) = 0
maxcoltwoLen(0) = 0
for r = 0 to UBound(FArray)
if FArray(r,0) <> "" And FArray(r,1) <> "" then 'list contents
shr = r + 2 'sheet row
'until I find out how to autofit a column instead of just a cell
longFname = ConvertFromURL(FArray(r,0)
if len(longFname) > maxcoloneLen(0) then
maxcoloneLen(0) = len(longFname)
maxcoloneLen(1) = shr + 1 'remember the longest string in col 1
endif
coltwo = ConvertFromURL(FArray(r,1)
if len(coltwo) > maxcoltwoLen(0) then
maxcoltwoLen(0) = len(coltwo)
maxcoltwoLen(1) = shr + 1 'remember the longest string in col 2
endif
'end of autofit workaround
FileList.getCellByPosition(0, shr).string = longFname 'file name with path
FileList.getCellByPosition(1, shr).string = coltwo 'type or file name
rr = rr + 1
endif
next
autoadjustcol maxcoloneLen(1), "A", true
autoadjustcol maxcoltwoLen(1), "B", true
end sub
'use this until I find out how to autofit a column. This autofits a cell which autofits the column
sub autoadjustcol(shrow as integer, shcol as string, setoptimal as boolean)
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
dim args2(0) as new com.sun.star.beans.PropertyValue
'the next two lines moves the focus to a cell
args1(0).Name = "ToPoint"
args1(0).Value = "$" & shcol & "$" & shrow
'the next two lines set the variables that are in the dialog for Optimal Width
args2(0).Name = "aExtraWidth"
args2(0).Value = 254
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
if setoptimal then
dispatcher.executeDispatch(document, ".uno:SetOptimalColumnWidth", "", 0, args2())
endif
end sub