Solved

OpenOffice Calc Macro

Posted on 2008-10-20
4
3,383 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
: 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…
Viewers will learn how to apply various conditional formatting in Excel 2013.
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.

636 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