LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Software (http://www.linuxquestions.org/questions/linux-software-2/)
-   -   HOW-TO dynamic filename in spreadsheet formula (http://www.linuxquestions.org/questions/linux-software-2/how-to-dynamic-filename-in-spreadsheet-formula-509923/)

secretlydead 12-13-2006 12:14 AM

HOW-TO dynamic filename in spreadsheet formula
 
A HOW-TO:

This example uses the OpenOffice spreadsheet program.


The purpose of this is to show you how you can have a dynamic filename linked in your spreadsheet.
For example,
you want cell B2 in your spreadsheet to be taken from the spreadsheet /12.ods, sheet 1, cell A1.

But you want to do this everyday, and the filename is going to be whatever day of the month it is.

So, tomorrow, the file would be named 13.ods, because tomorrow is the 13th of December. The next day it would be named 14.ods.



You're going to use this macro to do this:

Sub ExampleSetValue
Dim oDoc As Object, oSheet As Object, oCell As Object
oDoc=ThisComponent
oSheet=oDoc.Sheets.getByName("Sheet1")
oCell = thiscomponent.sheets(0).getcellbyposition(1,1)
oCell.setFormula("='" & DAY(NOW()) & ".ods'#$Sheet1.A1")
End Sub

(one note: the (1,1) in getcellposition refers to B2, (0,0) would be A1)

You're all set.



To learn how to use macros in OOffice by example, read on:

First, open a new spreadsheet. Fill in cell A1 with any number. Save it as today's date.ods. (For example, if today is Dec 15, save the file as 15.ods.)

Second, open a new spreadsheet. Save it as test.org

Tools -> Macros -> Organize Macros -> OpenOffice.org Basic...

Create a new macro under the filename test.ods.

Delete all the other stuff in the macro and copy and paste the macro I gave you before into it.

Save it and bring the sheet test.ods back up.


Tools -> Macros -> Run Macro...

Find your macro under test.ods and run it.

The number you entered into today's date.ods sheet1 A1 should now appear in test.ods sheet1 B2.


If you have any questions, don't hesitate to ask.

If this has helped you, please write a quick thank you note.


All times are GMT -5. The time now is 10:03 AM.