Libreoffice Base File Selection Control

[catipads.ccps]

Good day everyone,

I have a LO Base form where I have a text box that is bound to a field in a table. What I would like to do is use the “File Selection” control, choose a text file using that control, and have the contents of the text file “upload” (if you will) to the text box in the form (and the data will save to the field the text box is bound to in the table).

I have some experience in using macros, however, I am not sure if this is possible, or at least possible using the file selection control. Is there any way that I can achieve this, even though the file selection control may not be the way to go?

Thank you. I truly appreciate any help that you may provide.

[Ratslinger]

Hello,

It is likely this can be done. However you do not give much information as to the database you are using, the actual field type/size (text can vary greatly), the file content type (plain text, formatted, etc).

Please add this with a comment or edit original question. Always helpful if you provide specific LibreOffice version and OS.

[catipads.ccps]

Hi,

Thank you for getting back to me. I hope the following information can be helpful.

The OS is Ubuntu 18.04. LibreOffice version is 7.0.1.2.

I am using LibreOffice Base and the database is saved to my computer locally. It is not saved to a server.

The form control that I want to “upload” the text to is a text box that is mapped to a data field in a table. The table’s data field is named “fld_Notes” and it is a text (VARCHAR) field with a 5000 character maximum.

The text files that I want to “upload” for each record are all plain text, no formatting, and are +/- 1500 characters per file.

Please let me know if this helps.

[Ratslinger]

Hello,

Thank you for the response. It may not matter, but you did not provide the database you are using. Educated guess is that this is HSQLDB embedded.

Have done file reading in past and mainly got (and still often get) code samples from Pitonyak’s document → OOME or AndrewBase

In OOME there is info and code in 8.8. Reading and writing data about page 120.

Modifying that code resulted in:

Option Explicit    

Sub ExampleWriteOrPrint(oEvent)
    Dim FileName As String
    Dim n As Integer
    Dim i As Integer
    Dim s As String
    Dim sTemp$
    FileName = oEvent.Source.text
    n = FreeFile()
    Open FileName For Input Access Read As #n
    Seek #n, 1
    Do while NOT EOF(n)
      Line Input #n, sTemp
      s = s & sTemp & CHR$(10)
    Loop
    Dim oForm As  Object
    Dim oField As Object
    Dim sText As  String
    oForm = ThisComponent.DrawPage.Forms.getByName("YOUR_INTERNAL_FORM_NAME")
    oField = oForm.getByName("YOUR_TEXT_FIELD")
    oField.Text = s
    oField.commit()
    Close #n
End Sub

The macro I attached to the Mouse released event of the File Selection control.

Worked fine in testing (not a great deal done) on Ubuntu 20.04.2 LTS with LO v7.1.3.2 using HSQLDB embedded database.

This is just to get you going. You should put other checks in the code for things such as insuring a correct file type was used or error coding for what to do when the file is too large and probably more.

[catipads.ccps]

Thank you very much. This worked perfectly and imported the plain text file into the text box with the proper returns and everything. I appreciate your help.

I have written macros before that write to a text file, and I knew that I could read from a text file as well. I was not sure how the read process worked to place the contents of the text file into the text box. I am still relatively new to macros and basic, but I am learning.

Thank you again for your help. I truly appreciate it.