StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Indeed there is! And using by using macros it is quite easy to do.</p> <p><strong>Enabling macros</strong></p> <p>Go to the <em>Tools > Options</em> menu and click on the <em>Security</em> section under <em>OpenOffice.org</em>. Once there, click the <em>Macro Security</em> button. Now on the <em>Security Level</em> Tab, make sure that your settings will allow you to run Macros.</p> <p>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.</p> <p><strong>Note:</strong> Be careful, if you are unlucky or live in the 90's an evil macro can cause serious damage!</p> <hr> <p><strong>Creating a new macro</strong></p> <p>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.</p> <p>Go to <em>Tools > Macros > Organize Macros > OpenOffice.org Basic</em>, and once there add a new module under your file's tree. Give it a meaningful name.</p> <p><img src="https://i.stack.imgur.com/Z90QX.png" alt="New "></p> <hr> <p><strong>The actual macro</strong></p> <p>Once you create a new module the editor screen will pop up, write this code below:</p> <pre><code>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 </code></pre> <p>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:</p> <p><img src="https://i.stack.imgur.com/16Kf7.png" alt="Expected results"></p> <p><strong>Note</strong>: 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. </p> <hr> <p><strong>Further improving the macro</strong></p> <p>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:</p> <ul> <li>Being able to show the data retrieved on multiple columns/A single column/Something else</li> </ul> <p>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.</p> <ul> <li>Having to re-run the macro every time you want the data updated.</li> </ul> <p>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.</p> <pre><code>Sub Main Do While True DataFromFile() Wait(5000) Loop End Sub </code></pre> <p>And from now on you should call the Main sub instead of the DataFromFile.</p> <p>To make the macro run at start-up go to <em>Tools > Customize</em> on the <em>Events</em> tab and select <em>Open Document</em> from the list then click on the <em>Macro</em> button. On the dialog to select the macro, pick <em>Main</em>. Now close the document, reopen it, and voila!</p> <ul> <li>Using Cell Ranges</li> </ul> <p>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 <em>Data > Define Range</em> to give it a name, for example B1 could be called <em>'FilePath'</em> and A4:A999 could be called <em>'DataRange'</em>. This way if you ever need to change them, you don't have to change the macro, just the defined range name.</p> <p>Don't forget to update the code to look for the range instead of the address, for example, this bit of code:</p> <pre><code>getCellRangeByName("A4:A999") </code></pre> <p>would be rewritten to</p> <pre><code>getCellRangeByName("DataRange") </code></pre> <ul> <li>Error checking</li> </ul> <p>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?</p> <hr> <p><strong>Further reading</strong> </p> <p><a href="https://wiki.openoffice.org/wiki/Documentation/BASIC_Guide/Files_and_Directories_%28Runtime_Library%29" rel="nofollow noreferrer">Official reference regarding files for OpenOffice Basic macros.</a></p> <p><a href="https://wiki.openoffice.org/wiki/Documentation/OOoAuthors_User_Manual/Getting_Started/How_to_run_a_macro" rel="nofollow noreferrer">A guide on different ways to run a macro</a></p> <p><a href="http://www.openoffice.org/documentation/manuals/oooauthors/macros_intro_v1.pdf" rel="nofollow noreferrer">A great introduction to macro programming</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload