Macro, create folders from sheet columns, error at MkDir

English
,
#1

Hello, I’m trying to get this macro to work in LibreOffice and erroring at the Create Folder section. ‘Shell “mkdir “”” & sFolderPath & “”, 0’

It’s been a long time since I tinkered with macros.
Would appreciate if anyone could point me in the right direction to troubleshoot this.
Much appreciated

Sub CreateFolders()
    Dim oDoc As Object
    Dim oSheet As Object
    Dim oRootFolder As Object
    Dim oFolders As Object
    Dim sRootPath As String
    Dim i As Integer
    
    'Prompt User to Select Root Folder
	oDialog = CreateUnoService("com sun stanui dialogs FolderPicker")
	oDialog.setTitle( "Select Location for 'My Home Root Folder")
	oDialog .execute()

	'Get Selected Path
	sRootPath = oDialog .getDirectory()
    
    ' Check if user canceled folder selection
    If sRootPath = "" Then
        MsgBox "Operation canceled by user.", 48, "Canceled"
        Exit Sub
    End If
    
    ' Loop through rows and create folders
    For i = 0 To oSheet.Rows.getCount() - 1
        ' Construct full path for current row
        Dim sFolderPath As String
        sFolderPath = sRootPath & "/" & oSheet.getCellByPosition(0, i).String & "/"
        sFolderPath = sFolderPath & oSheet.getCellByPosition(1, i).String & "/"
        sFolderPath = sFolderPath & oSheet.getCellByPosition(2, i).String & "/"
        sFolderPath = sFolderPath & oSheet.getCellByPosition(3, i).String
        
        ' Create directory if it doesn't exist
        If (Not FileExists(sFolderPath)) Then
            Shell "mkdir """ & sFolderPath & "", 0
        End If
    Next i
    
    MsgBox "Folders created successfully.", 64, "Done"
End Sub

Function GetRootFolder() As String
    Dim oFileDialog As Object
    Dim sPath As String
    
    oFileDialog = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
    oFileDialog.setTitle("Select Root Folder")
    oFileDialog.setDisplayDirectory("file:///")  ' Start at root directory
    
    ' Show dialog and get result
    If oFileDialog.execute() = 1 Then
        sPath = oFileDialog.getFiles()(0)
    End If
    
    GetRootFolder = sPath
End Function

Function FileExists(sPath As String) As Boolean
    Dim oFile As Object
    
    On Error Resume Next
    oFile = CreateUnoService("com.sun.star.ucb.SimpleFileAccess")
    FileExists = oFile.exists(sPath)
End Function

[erAck: edited to format code as code, see This is the guide - How to use the Ask site? - #6 by erAck]

#3

I doubt whether this service exists?
maybe:

oDialog = CreateUnoService("com.sun.star.ui.dialogs.FolderPicker")`

youre grazy … a Sheet has 1048576 rows (depending on your setting maybe 16 x 1048576)

1 Like
#4

The above service is quite smart and can complete the task immediately, for example:

Sub test
  Dim oSFA as Object
  oSFA=CreateUnoService("com.sun.star.ucb.SimpleFileAccess")
  oSFA.createFolder(ConvertToUrl("/home/YourName/test/test/test"))  
End Sub
1 Like
#6

I would prefer some more robust with python:

from pathlib import Path
from uno import fileUrlToSystemPath as uri2path, getComponentContext
ctx = getComponentContext()
createUnoService = ctx.ServiceManager.createInstance

def create_folders(*_):

    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.Sheets["Tabelle1"] #your sheetname here
    
    dialog = createUnoService("com.sun.star.ui.dialogs.FolderPicker")
    dialog.setTitle( "Select Location for My Home Root Folder")
    dialog.execute()
    baseurl = dialog.getDirectory()
    
    if  not baseurl:
        print('canceled by User')
        return
        
    basepath = Path( uri2path( baseurl ))
    
    print(f'start on {basepath =}')

    source = sheet["A1:D6"].DataArray # youre cellrange here
    for a, b, c, d in source:
        p = basepath / a / b / c / d
        print(f'{p=}')
        p.mkdir(parents=True, exist_ok=True)
3 Likes
#7

Appreciate this, thanks! I’ll give a try in the next few days.

#2

You didn’t say what with “erroring at the Create Folder section” actually happens, but (untried) I see two things:
This

Shell "mkdir """ & sFolderPath & "", 0

should be

Shell "mkdir -p """ & sFolderPath & """", 0

instead. For one, the quoted directories string needs to have closing quotes (your "" was just an empty string appended in BASIC). Then, if a directory inside a parent hierarchy is to be created and a parent does not exist, the -p or --parents option is needed. (assuming you’re not on Windows…).

2 Likes
#5

I’m getting error ‘BASIC runtime error. File not found.’
I’ve got the macro in the file vs ‘My Macros & Dialogs’ not sure this would matter tho

Using windows, appreciate the tip on ‘-p’ for Mac. Just want to use on my windows machine.

#8

Then you must use Windows’ path separator: backslash \ instead of forward slash /. It is important in the command line (yes, many tools on Windows allow both separators, but not cmd.exe).

Now remember, that the mkdir is a command of the cmd.exe command processor. It can’t be run directly (there is no mkdir.exe program in Windows). The Shell Basic function does not start any processor for you, it just passes your command line to the Windows shell (which is not cmd.exe). So, your full call must be:

Shell "cmd.exe /C mkdir ""D:\some\maybe\long\path"""

Note that there is no -p switch to the mkdir on Windows - it creates intermediate directories automatically. Also, on Windows, the canonocal switch character is /, not - - that is, by the way, the reason why forward slashes aren’t allowed in the path there. Yes, some command line tools support - syntax, but not all.

The /C switch to cmd.exe tells it to start, execute the following command, and exit.

It is best to try to do manually, what you intend to automatize. Open cmd.exe, and put the command you created.

It is also best to debug what you do. Put a breakpoint at the failing command, and inspect the variables (how come that you didn’t spot the missing quote?).

2 Likes
#9

Really appreciate the support, thank you. Didn’t even think to test run commands with cmd.exe first.
I got it to run, but it wasn’t creating folders. Will dig in on it more this week.

#10

I would strongly recomment the already given python-solution, because:

  1. no hazzle with sub-shells and different syntaxes on Linux, Windows … whatever
  2. no difficulties with different file-system-encodings
#11

Indeed, the Python solution is nice, and learning Python is generally worth it. However, for completeness, let me mention that Basic has its own MkDir statement, which also has the mentioned benefits (no need to play with system differences); it’s unclear why @Newbie4 decided to use Shell (and the command line’s mkdir) in the first place.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.