Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3450
  • Last Modified:

OpenOffice Calc Macro

I have the following macro for Calc in OpenOffice 3.  It converts the open document to csv, and saves the document.

I need to edit this macro so that it saves the file as the original filename with the csv extension.

So if I open a file called hello.xls, the macro saves the file as hello.csv.  Right now it always saves as import.csv because that is what the macro says.  I don't know how to save the file as the original name, but with the csv extension.
sub CSV
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
 
rem ----------------------------------------------------------------------
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///C:/Documents%20and%20Settings/Matthew/Desktop/1/import.csv"
args1(1).Name = "FilterName"
args1(1).Value = "Text - txt - csv (StarCalc)"
args1(2).Name = "FilterOptions"
args1(2).Value = "44,0,ANSI,1"
 
dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())
 
 
end sub

Open in new window

0
fastfind1
Asked:
fastfind1
  • 2
  • 2
1 Solution
 
ltlbearand3Commented:
I have added in a section towards the top of your macro that grabs the current document name and uses that name to save your file.  Give it a shot.

-Bear

sub CSV
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
dim oThisDoc as object
dim sDocURL as string
dim sDocName as string
 
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oThisDoc = ThisComponent
 
rem ----------------------------------------------------------------------
' Find Current Document Name
If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
End If
 
If (oThisDoc.haslocation()) Then
	sDocURL = oThisDoc.getURL()
	sDocName = FileNameoutofPath(sDocURL, "/")
End If
 
rem ----------------------------------------------------------------------
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///C:/Documents%20and%20Settings/Matthew/Desktop/1/" & sDocName
args1(1).Name = "FilterName"
args1(1).Value = "Text - txt - csv (StarCalc)"
args1(2).Name = "FilterOptions"
args1(2).Value = "44,0,ANSI,1"
 
dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())
 
 
end sub

Open in new window

0
 
fastfind1Author Commented:
This seems to almost work.  When I run the macro I get a warning that only the active sheet was saved (this happened in my original macro also -- part of saving the file to csv format).

But after I click ok, there is no file actually saved.

Any thoughts?
0
 
fastfind1Author Commented:
ACtually I was wrong.

The file IS being saved, but with the old extension xls

This is very close to what I need.

What I need is for the file to be saved with the original filename, but new extension.

So if the original file is hello.xls, I need to convert it to csv (which this macro does) and save it as hello.csv.

Right now your code (thanks!) is converting to csv, but saving it as hello.xls, even though it is a csv.

Can we edit the macro to save the final as .csv?
0
 
ltlbearand3Commented:
Not a problem.  Try the attached code.  Will change the extension of the file to .csv

-Bear

sub CSV
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
dim oThisDoc as object
dim sDocURL as string
dim sDocName as string
Dim sNewDoc as string
Dim MaxIndex as Integer
Dim SepList() as String
 
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oThisDoc = ThisComponent
 
rem ----------------------------------------------------------------------
' Find Current Document Name and change extension to .csv
If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
        GlobalScope.BasicLibraries.LoadLibrary("Tools")
End If
 
If (oThisDoc.haslocation()) Then
	sDocURL = oThisDoc.getURL()
	sDocName = FileNameoutofPath(sDocURL, "/")
	SepList() = ArrayoutofString(sDocName,".", MaxIndex)
	sNewDoc = left(sDocName, Len(sDocName)-Len(SepList(MaxIndex))
	sNewDoc = sNewDoc & "csv"
End If
 
rem ----------------------------------------------------------------------
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///C:/Documents%20and%20Settings/Matthew/Desktop/1/" & sNewDoc
args1(1).Name = "FilterName"
args1(1).Value = "Text - txt - csv (StarCalc)"
args1(2).Name = "FilterOptions"
args1(2).Value = "44,0,ANSI,1"
 
dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())
 
 
end sub

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now