How to export a cell from Open Office Calc as a file name?

FMPC
FMPC used Ask the Experts™
on
I am routinely naming saved files from a template as YYYY MM DD - XXX, where xxx are the invoice number. Is there a way to automate this process by constructing Open Office Calc's file name from a cell?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ALT F11 to access Macros


Some of this depends on exactly what you want.  For instance, do you want this option for any calc document?  Is the file name always in the same cell?  Are you saving the file in the same location every time?  We can do some things in a macro based on answers to those questions.

For instance if you save to the same location every time and the xxx part is in the same cell every time, then macro below will work.  It is set to save to the c:\ drive and use Cell A1.  To add this macro to Calc so it is available whenever you open calc:

Open Calc
Press Alt-F11 to open Macros
In Macro from, expand My Macros
Select Standard
Click New on the right
Enter a module Name (like MyModules or FMPCModules)
Copy and past the Macro below over Sub Main
Save (Cntrl - S)
Close the Editor Window

To add this macro as a button on your toolbar:
Select Menu Tools >> Customize
Click Add
In Category, scroll to the bottom
Expand OpenOffice.org Macros
Expand My Macros
Expand Standard
Select the Module name (Should be what you entered above)
Select the SaveMyFile Macro in the Commands Section
Click Add


If you want to change the cell location change the value in getCellByPosition to have the correct numeric reference.  To change the File location, change

 sFile = ConverttoURL ("C:\" & strFileName & ".ods")
 
 to have the correct info such as
 
 sFile = ConverttoURL ("C:\EEtest\" & strFileName & ".ods")
 
 Let me know if you questions.
 
 -Bear

Sub SaveMyFile()
    Dim oDoc as object
    Dim args() 
    Dim sFile as string
	Dim oSheet as Object
	Dim oCell as Object
	Dim strFileName as string

	' Get Access to This Spreadsheet for Saving
	oDoc = Thiscomponent

	' Get the Active Sheet
	oSheet = thisComponent.getCurrentSelection.getSpreadSheet
	' Get Data from a Cell - position 0, 0 = Cell A1
	oCell =  oSheet.getCellByPosition(0,0)
	strFileName = format(now(), "YYYY MM DD") & "-" & oCell.string

	' Convert File name and path to URL for OOo to Save
	sFile = ConverttoURL ("C:\" & strFileName & ".ods")
	
	' Save The File
	oDoc.StoreAsURL(sFile, args())
End Sub

Open in new window

Author

Commented:
Thank you for the reply, before I run the macro I just wanted to answer your questions, so that assumptions are right.
The template is always located in the same place, documents saved in the same place, the cell number on both axis is always the same too.
Are these the assumptions you made too?
Yes, those are the assumptions I made.

You need to update the line:

sFile = ConverttoURL ("C:\" & strFileName & ".ods")

To have the correct path for the file location
Also update line

oCell =  oSheet.getCellByPosition(0,0)

To have the correct Cell Reference A1  = (0,0), C3 = (2,3) and so on.  Let me know if you need assistance in updating those lines of code.

-Bear

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial