[Solved] Create folder based on form field value

Creating a macro - Writing a Script - Using the API

[Solved] Create folder based on form field value

Postby MHCom » Tue Nov 26, 2019 1:34 pm

Hi,

I am building a Base database that contains various forms for adding records to tables. One requirement is that when a new record is created from one particular form called "Add New Claim", a new folder should be created in a specified directory with a name consisting of the values in two fields "Vehicle Reg" and "Claim reference".

Eg a new record is created with Vehicle Reg aa11 bcd and claim reference 123456 - i need a macro to check if a folder already exists called "aallbcd123456" and if it doesn't, create it! Is this do-able?

Many thanks in advance, I'm fine with vba but new to openoffice!
Last edited by MHCom on Wed Nov 27, 2019 2:19 pm, edited 1 time in total.
OpenOffice 4.1.5 on Windows 7 Professional
MHCom
 
Posts: 16
Joined: Fri Nov 08, 2019 4:55 pm

Re: Macro in Base - create folder based on form field value

Postby JeJe » Tue Nov 26, 2019 2:15 pm

Have a look at the OpenOffice help file that you open from the Basic IDE.

You use the Dir statement to check if a folder exists and MKDir to create one.

(Edit - with things like this VBA/Visual Basic and OOBasic are the same or very similar BTW)
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 1077
Joined: Wed Mar 09, 2016 2:40 pm

Re: Macro in Base - create folder based on form field value

Postby Villeroy » Tue Nov 26, 2019 2:29 pm

I recorded the following code with MRI binding form event "Before Record Action" to MRI and then reading the string value of a field "Identifier". The event is triggered when you insert, update, delete or undo a form record.
Code: Select all   Expand viewCollapse view
Sub Form_Before_Record_Action(e)
Sub Snippet(Optional oInitialTarget As Object)
  Dim nAction As Long
  Dim nRows As Long
  Dim oSource As Variant
  Dim oColumns As Variant
  Dim oObj1 As Variant
  Dim sString As String

REM http://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/ChangeAction.html
  nAction = e.Action
  nRows = e.Rows
  oSource = e.Source
 
  oColumns = oSource.getColumns()
  oObj1 = oColumns.getByName("Identifier")
  sString = oObj1.getString()
 
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28422
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro in Base - create folder based on form field value

Postby MHCom » Tue Nov 26, 2019 3:43 pm

I've taken the following code and put it under "Before Submitting" on the mainform Properties. I get no error messages but nothing happens at all

Code: Select all   Expand viewCollapse view
Sub Form_Before_Record_Action(e)

  Dim nAction As Long
  Dim nRows As Long
  Dim oSource As Variant
  Dim oColumns As Variant
  Dim oObj1 As Variant

  Dim sString As String



  nAction = e.Action
  nRows = e.Rows
  oSource = e.Source
 
  oColumns = oSource.getColumns()
  oObj1 = oColumns.getByName("List Box 6")

  sString = oObj1.getString()
 
  If FileExists("S:\Matthew\DirectoryTest\"&sString) Then
   MsgBox "File Exists"
   Else MkDir ("S:\Matthew\DirectoryTest\"&sString)
ENd if   
   
 
End Sub
OpenOffice 4.1.5 on Windows 7 Professional
MHCom
 
Posts: 16
Joined: Fri Nov 08, 2019 4:55 pm

Re: Macro in Base - create folder based on form field value

Postby Villeroy » Tue Nov 26, 2019 4:04 pm

"List Box 6" is certainly not the name of the rowset column. Use the name of the column that is specified as the list box's linked field.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28422
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro in Base - create folder based on form field value

Postby MHCom » Tue Nov 26, 2019 4:30 pm

I also tried that with the same result: no error messages but nothing has happened, no new folder created.
OpenOffice 4.1.5 on Windows 7 Professional
MHCom
 
Posts: 16
Joined: Fri Nov 08, 2019 4:55 pm

Re: Macro in Base - create folder based on form field value

Postby Villeroy » Tue Nov 26, 2019 10:26 pm

I hate Base macros and I'm far away from understanding the event calls, but this seems to work somehow with LibreOffice 6 and OpenOffice 4.
This has to be a function. Only if the function returns True the form action will be performed. The function is called several times with different source objects. I simply added an exit on error if an object does not fit.
Attachments
directory_on_insert_update.odt
(13.07 KiB) Downloaded 22 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28422
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro in Base - create folder based on form field value

Postby MHCom » Wed Nov 27, 2019 11:55 am

Sorry, not sure if I'm missing something but all I see on the attachment is a text file saying "Insert new records into Bibliography.biblio by entering new text values into the "Identifier" field."
OpenOffice 4.1.5 on Windows 7 Professional
MHCom
 
Posts: 16
Joined: Fri Nov 08, 2019 4:55 pm

Re: Macro in Base - create folder based on form field value

Postby Villeroy » Wed Nov 27, 2019 1:00 pm

Sorry. It is a Writer document with a form and a macro. The form is linked to the "biblio" table example database "Bibliography" (dBase, shipped with the office suite). You an see the table in the data source window (F4). The form is set to "new records only" so it loads in a new record when you open it. Entering some word into the text box adds a new record to the dBase table. You can then navigate back to the recently added records and modify them. Finally you can remove the new records.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28422
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro in Base - create folder based on form field value

Postby MHCom » Wed Nov 27, 2019 1:15 pm

Thank you so much for continuing to try to help me, I get the feeling this just isn't going to happen for me! I have taken the code int the attachment and amended Const cDir = line to = "S:\Matthew\" and when I press the save record button on the writer document, nothing happens.
OpenOffice 4.1.5 on Windows 7 Professional
MHCom
 
Posts: 16
Joined: Fri Nov 08, 2019 4:55 pm

Re: Macro in Base - create folder based on form field value

Postby Villeroy » Wed Nov 27, 2019 1:47 pm

Ah, yes. You have to adapt the path and the document has to be saved to a trusted directory according to menu:Tools>Options>Security>[Macro Security...], tab "Trusted Sources". Otherwise you get a security warning when opening any document with embedded macros. I recomment the highest security level and one or two trusted directories (NOT your "Downloads" folder).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28422
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro in Base - create folder based on form field value

Postby MHCom » Wed Nov 27, 2019 2:17 pm

:o It worked! Simple as that in the end! You're awesome. Thank you so much for all your help here, I was giving up.
OpenOffice 4.1.5 on Windows 7 Professional
MHCom
 
Posts: 16
Joined: Fri Nov 08, 2019 4:55 pm

Re: [Solved] Create folder based on form field value

Postby MHCom » Wed Nov 27, 2019 4:18 pm

Wait, didn't quite work - the form is now not saving. The directory is created fine but the form does not submit and the table it links to is not updated.
OpenOffice 4.1.5 on Windows 7 Professional
MHCom
 
Posts: 16
Joined: Fri Nov 08, 2019 4:55 pm

Re: [Solved] Create folder based on form field value

Postby MHCom » Wed Nov 27, 2019 5:32 pm

Please ignore, fixed!
OpenOffice 4.1.5 on Windows 7 Professional
MHCom
 
Posts: 16
Joined: Fri Nov 08, 2019 4:55 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests