Solved

OpenOffice Calc Macro

Posted on 2008-10-20
4
3,341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 22757544
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
ID: 22761692
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
ID: 22761718
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
ID: 22762740
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Viewers will learn various types of data validation for different data types in Excel 2013.
Viewers will learn the basics of printing in Excel 2013 and how to adjust some common settings.

730 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