Is there a way to attach a file (.txt) in calc open office programmatically (using macro)?

Question

I have a .txt file that I need to attach in a column of my sheet, and i have the path to this file. So I need to read this path and attach the file in another column programmatically. Is there a way to do it?

thanks in advance.

Answer 1

Indeed there is! And using by using macros it is quite easy to do.

Enabling macros

Go to the Tools > Options menu and click on the Security section under OpenOffice.org. Once there, click the Macro Security button. Now on the Security Level Tab, make sure that your settings will allow you to run Macros.

My settings are on low because I'm the author of all the macros I run, if you are not sure that this will be your case you might want to use a higher setting.

Note: Be careful, if you are unlucky or live in the 90's an evil macro can cause serious damage!

---

Creating a new macro

Now that you can run them, you must create a new macro. OpenOffice accepts a wide range of languages including Python, but since you didn't specified any I'll use OO's version of basic here.

Go to Tools > Macros > Organize Macros > OpenOffice.org Basic, and once there add a new module under your file's tree. Give it a meaningful name.

---

The actual macro

Once you create a new module the editor screen will pop up, write this code below:

Sub DataFromFile
    Dim FileNo As Integer
    Dim CurrentLine As String
    Dim File As String
    Dim Msg as String
    Dim I as Integer

    ' Get the filename from the cell, in this case B1.
    currentSheet=ThisComponent.CurrentController.ActiveSheet
    fileName =  currentSheet.getCellRangeByName("B1").getString

    ' Create a new file handler and open it for reading
    FileNo = FreeFile
    Open fileName For Input As #FileNo

    I = 0

    ' Read file until EOF is reached
    Do While not eof(FileNo)
      ' Read line 
      Line Input #FileNo, CurrentLine   

      ' Define the range to put the data in as A4:A999 '
      curentCell = currentSheet.getCellRangeByName("A4:A999").getCellByPosition(0,I)

      ' Select the I-th cell on the defined range and put a line of the file there
      curentCell.String = CurrentLine

      'Increase I by one
      I = I + 1
    Loop

    Close #FileNo
End Sub

To test it, just create a text file and put something in it, then put the path to it on cell B1 and run the macro. You can run the macro in many ways, for test purposes just use the Run button on the same window that you used to create the module. This is the expected result:

Note: If you are unfamiliar with linux, don't be intimidated by that file path, it's just how they are on linux. This would just work the same with windows and it's file path structure.

---

Further improving the macro

I wrote the code above with the goal of making it as easy to understand as possible, therefore the macro have plenty of room for improvement, such as:

• Being able to show the data retrieved on multiple columns/A single column/Something else

Once you have retrieved the data from the file, you can display it on your spreadsheet in nearly anyway you want it. Let me know if the way you initially intended was not addressed and I will edit the answer.

• Having to re-run the macro every time you want the data updated.

This is easily fixed. There are many ways to automatize the macro execution, the one I'm most familiar with consists on making it run on a loop in conjunction with a delay of, say, 5 seconds and making it start as soon as the file loads.

Sub Main
    Do While True
        DataFromFile()
        Wait(5000)
    Loop
End Sub

And from now on you should call the Main sub instead of the DataFromFile.

To make the macro run at start-up go to Tools > Customize on the Events tab and select Open Document from the list then click on the Macro button. On the dialog to select the macro, pick Main. Now close the document, reopen it, and voila!

• Using Cell Ranges

It's easier to keep your code and make changes to it if you name the cell ranges and use their names instead of their absolute address. To name a range (or a single cell) you must first select it then click on Data > Define Range to give it a name, for example B1 could be called 'FilePath' and A4:A999 could be called 'DataRange'. This way if you ever need to change them, you don't have to change the macro, just the defined range name.

Don't forget to update the code to look for the range instead of the address, for example, this bit of code:

getCellRangeByName("A4:A999")

would be rewritten to

getCellRangeByName("DataRange")

• Error checking

It is a good idea to check and deal with error or unexpected events. What if the file doesn't exists? What if it is bigger than the defined range?

---

Further reading

Official reference regarding files for OpenOffice Basic macros.

A guide on different ways to run a macro

A great introduction to macro programming