Solved

OpenOffice Calc Macro

Posted on 2008-10-20
4
3,287 Views
Last Modified: 2013-12-27
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
Comment
Question by:fastfind1
  • 2
  • 2
4 Comments
 
LVL 20

Expert Comment

by:ltlbearand3
Comment Utility
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
 

Author Comment

by:fastfind1
Comment Utility
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
 

Author Comment

by:fastfind1
Comment Utility
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
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 500 total points
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article explains in simple steps how to renew expiring Exchange Server Internal Transport Certificate.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.
Viewers will learn how to share Excel data with others from desktop Excel, as well as Excel Online via OneDrive, and embed an Excel file on a website.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now