Impossible to close a dialog using .endExcute when Macro saved in the spreadsheet

Hello ,
I used to write my macros libreoffice basic directly to the application library (when you do that you cannot share it to everyone one because it stays in your computer) . I was able to close a dialog box that i’ve created using a sub routine sub closedialog ()
oDialog.endExcute()
end sub and everything was working correctly but when i write my macro in the document library standard i can no longer close the dialog. Can someone help me? I’m using Libreoffice 7.2.7.2 on windows 11 x64

Please upload the problematic .ods file containing the dialog and a macro to close the dialog.

Without seeing the whole game I’d suggest to check, how and if your oDialog gets its value…

Maybe just add it as parameter, so you can call closedialog(somedialog)

the macro deal with various files and folder and the code is pretty long(comments are in french) , you won’t be able to use it i think

the code is long and is commented in French. But The code open a first dialog when the use can select and open folders,files then a lot of treaments are done to those files after that the dialog is close automatically(the first .endExecute works) and then an other dialog is called ( “Call ExecuteFinDialog”). That Dialog has a button linked to the subroutine “Sub CloseDialogFinishedButton” so that when the user cliked on it the dialog is closed. All variables who launch the two dialogs are global. The problem is the last dialog who is supposed to be closed when the user clik on the button. But it doesnt here are some parts of the code which involves the dialog(remember that the same code works when i create it in the standard library of the application just not in the standard library of the spreadsheet :

Global oLancerMacroDialog As Object
Global oFinDialog As Object
 (...) ' a lot of treatements and functions 
                               
Sub LancerDialogBouton
	oLancerMacroDialog = CreateUnoDialog(DialogLibraries.Standard.LancerMacro)
	oLancerMacroDialog.execute()

End Sub
Sub CheminDossierHDGE()
    Dim oFolderPicker As Object
    oFolderPicker = createUnoService("com.sun.star.ui.dialogs.FolderPicker")

    If oFolderPicker.execute() Then
        
        sFolderHDGEURL = oFolderPicker.getDirectory()
        ' Convertissez le chemin en URL
        sFolderHDGEURL = ConvertToURL(sFolderHDGEURL)
        ' Mettez à jour la zone de texte avec l'URL du dossier
        oLancerMacroDialog.getControl("TextFieldCheminDossHDGE").Text = ConvertFromURL(sFolderHDGEURL)
    End If
End Sub
Sub CheminDossierDGE()
    Dim oFolderPicker As Object
    oFolderPicker = createUnoService("com.sun.star.ui.dialogs.FolderPicker")

    If oFolderPicker.execute() Then
        
        sFolderDGEURL = oFolderPicker.getDirectory()
        ' Convertissez le chemin en URL
        sFolderDGEURL = ConvertToURL(sFolderDGEURL)
        ' Mettez à jour la zone de texte avec l'URL du dossier
        oLancerMacroDialog.getControl("TextFieldCheminDossDGE").Text = ConvertFromURL(sFolderDGEURL)
    End If
End Sub
Sub OuvrirFichierRCGR92
	   Dim oFilePicker As Object	
	   oFilePicker = CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
       oFilePicker.initialize(Array(com.sun.star.ui.dialogs.TemplateDescription.FILEOPEN_SIMPLE))
	   oFilePicker.appendFilter("Excel Files", "*.xls")
	   oFilePicker.setTitle("Sélectionnez le fichier RCGr92_cge_national de l'année actuelle")
	
	   If oFilePicker.execute() = 1 Then
	       sFileURL =oFilePicker.getFiles()(0)
	       oLancerMacroDialog.getControl("TextFieldRCGR92").Text = ConvertFromURL(sFileURL)
	   End If
End Sub
Sub LancerMacroBouton
.
.(more  treatements after the user click on the button to launch the macro)
.
    oLancerMacroDialog.endExecute() ' I end the first dialog and its working
    Call ExecuteFinDialog    
End Sub

Sub ExecuteFinDialog
	'Dialogue de fin d'operation
    oFinDialog = CreateUnoDialog(DialogLibraries.Standard.Fin)
	oFinDialog.execute()
End Sub 

'This Sub is linked to the button on oFinDialog but the button doesnt close it, i even put a MsgBox in it but  the button can't even print that 

Sub CloseDialogFinishedButton 
	oFinDialog.endExecute() 
End Sub

Try to upload the file and describe the exact sequence of user actions that leads to the problem (button press is not processed):


Please tell me, is there currently no Fin dialog left in the standard application library? Could it happen that Sub ExecuteFinDialog loads the wrong dialog?

Yes, the right dialog is executed just the button on the dialog who doesn’t work. Because it is supposed to close the dialog.

I can’t upload the macro file because it has confidential data in it and also it won’t work because of the specific files it is using:

  1. First the user click on a button in the spreadsheet which launches the first dialog
    The dialog has command buttons and textboxes that allows to user to open folder and automatically pick data from various files in specifics sheets and cells
    Then the user launches the various treatements on those files (copy and paste, deleting, calculus,etc) by pressing a command button .
  2. After that the first dialog is closed and the second is launched now it is supposed to be closed when the user presses a button

Try to simplify the example as much as possible, removing everything that is not relevant to the error.

For some modal dialog:

REM code halts here while the dialog is open
x = cBool(oDialog.execute()) 
REM dialog has been closed one way or the other
if x then
  do_stuff
else
  clean_up
endif

This requires that you assign button type property “OK Button” to an OK button and “Cancel Button” to any cancel button. Closing the window any other way returns False.

2 Likes

Just close the dialog? Click the button and close the dialog? Why not use the button’s built-in property for this?

image

1 Like

i create another macro very simple to illustrate the problem:

  1. Write a word
  2. Pick an excel files
  3. then Launch the macro. It’s supposed to write your word in the cell “A1” of the file you chose and then when its over another dialog is open then when you click on the button “ok” its supposed to be close but it doesnt. I Upload The macro files ods
    MacroTestCloseDialog.ods (13.0 KB)
1 Like

Thanks man! I didn’t know that that property existed. Its working perfectly.

Thanks for the interesting example!
First a quick note: the dialog library should always be loaded explicitly, even Standard. So add next line at the beginning LaunchDialogUserText

DialogLibraries.loadLibrary("Standard")

I think that the problem with your macros is related to the sequence of actions:

  1. oDialog opens
  2. After clicking the Launch Macro button, the second file opens, changes are made to it, after which the file is closed and the oFinishedDialog opens.

It seems to me that the Basic CreateUnoDialog function does not process correctly when creating oFinishedDialog and “loses” the event handlers of the dialog controls.

In the attached file, I replaced CreateUnoDialog in the ExecuteFinishedDialog macro with the corresponding calls to the UNO API.
The button to close the second dialog should now work.

MacroTestCloseDialog3.ods (14.5 KB)

1 Like

Thanks for the advices and the code. It seems that libreoffice has a problem dealing with complex macros. Here is a very simplified version: The user just writte a word in the textfield and the word it’s written on the first cell of the same sheet. Ive used my same logic and it’s working. Anyway Thanks! Have a nice day
MacroTestCloseDialogsimpleversion - Copie.ods (12.5 KB)

If you open the file from your last message and click on the button, the macro execution ends with an error. Pay attention to my post about LoadLibrary.
The problem with the MacroTestCloseDialog.ods file occurs when opening and closing a second .ods file before creating the second dialog. In your new example, the second file does not open, and, accordingly, there is no problem.

@sokol92 FYI In the second example, Global oFinishedDialog As Object is placed at the beginning of the module; in the previous example, it was declared in each procedure and was not global.

@JohnSUN , I have previously tested this version (see attached file). In my instance of LO, when performing the sequence of actions described by the author of the topic, when clicking on the OK button of finished dialog, CloseMacroFileButtonAction macro is not called.
If in the attached file you comment out the lines associated with opening and closing the second file, then the button to close the second dialog works.
MacroTestCloseDialog_o.ods (14.3 KB)

Here’s another compact example.

  1. Execute the ShowDialog macro. Clicking the Close dialog button closes the dialog.
  2. Now in the ShowDialog2 macro, change the value of the filePath variable and execute this macro. Close dialog button does not work!
Option Explicit
Dim oDialog

' Creating a dialog.
Sub ShowDialog
  oDialog=CreateUnoDialog(ThisComponent.DialogLibraries.getByName("Standard").getByName("Dialog1"))
  oDialog.Execute()
End Sub

' Creating a dialog after opening and closing a file.
Sub ShowDialog2
  Dim oDoc As Object, filePath As String
  
  filePath="C:\temp\test.ods"  ' ???
  oDoc=StarDesktop.LoadComponentFromUrl(ConvertToUrl(filePath), "_blank", 0, array())
  oDoc.close True
  
  oDialog=CreateUnoDialog(ThisComponent.DialogLibraries.getByName("Standard").getByName("Dialog1"))
  oDialog.Execute()
End Sub

Sub OnAction(oEvent)
  CloseDialog()
End Sub

Sub CloseDialog ()
  oDialog.endExecute()
End Sub

TestDialog.ods (11.9 KB)

1 Like