[Solved] Save calc document like csv file by macro

Keyboard macros or custom scripts
Post Reply
briglia23
Posts: 155
Joined: Tue Jun 24, 2008 10:09 am

[Solved] Save calc document like csv file by macro

Post by briglia23 »

Ho can i do this?

i have found this link, but i not understand very well

http://user.services.openoffice.org/en/ ... f=9&t=2551

I would to do this Save as --> CSV file --> ans set Field delimeter to ; and text delimeter to ""

Thanks
Last edited by Hagar Delest on Thu Jun 11, 2015 9:43 pm, edited 1 time in total.
Reason: tagged [Solved].
OOo 2.3.X on openSuse 10
FJCC
Moderator
Posts: 8723
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: save calc document like csv file by macro

Post by FJCC »

I think this does the basics of what you want. It takes the current file does a Save As to a CSV format with ; and the delimiter and " to signify text.

Code: Select all

Dim Propval(1) as New com.sun.star.beans.PropertyValue
Propval(0).Name = "FilterName"
Propval(0).Value = "Text - txt - csv (StarCalc)"
Propval(1).Name = "FilterOptions"
Propval(1).Value ="59,34,0,1,1"   'ASCII  59 = ;  34 = "
Doc = ThisComponent
FileName = "D:\Documents and Settings\username\Desktop\TestFile.txt"  'Change to whatever file name you want
FileURL = convertToURL(FileName)
Doc.StoreAsURL(FileURL, Propval())
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
briglia23
Posts: 155
Joined: Tue Jun 24, 2008 10:09 am

Re: Save calc document like csv file by macro

Post by briglia23 »

thanks!
If i want that users can choice where save the file?

Like just a save as but to csv format and where export options already set.

Thank you
OOo 2.3.X on openSuse 10
briglia23
Posts: 155
Joined: Tue Jun 24, 2008 10:09 am

Re: Save calc document like csv file by macro

Post by briglia23 »

SOOLVE THANKYOU VERY MUCH
OOo 2.3.X on openSuse 10
mash
Posts: 1
Joined: Tue Jun 09, 2015 11:21 am

Re: Save calc document like csv file by macro

Post by mash »

Code: Select all

REM  Useful macro to save calc document sheets to csv files hoisted from http://www.oooforum.org/forum/viewtopic.phtml?t=6286

REM Create a new macro in Tools 

REM  *****  BASIC  ***** 

REM  *****  BASIC  ***** 

' Cf http://www.oooforum.org/forum/viewtopic.phtml?t=6286 
' 
'****************************************************************** 
'This is a simple Library to save all Sheets of a Spreadsheet 
'as CSV-File. It fits my needs. But if you improve this library, 
'please make it public (OO-Forum) ! 
' 
'Orig. author: Michael Taupitz 
'email:   mitaco@gmx.net 
'date :     25.2.2004 
' 
'userloser fixed some bugs on Aug 23 2013 
'****************************************************************** 

Option Explicit 

' saves sheets in the doc directory, but 
' without appending the document filename 
Sub Main 
   SaveAllToCSV() 
End Sub 

' example appending a prefix to 
' the csv file name 
Sub Main1 
   SaveAllToCSV("prefix - ") 
End Sub 


' This procedure iterates over the spreadsheets in 
' a Calc file and save each to a new file named 
' $basename/${newFileNamePrefix}$sheet_name.csv 

Sub SaveAllToCSV(Optional newFileNamePrefix as String) 

   Dim i as Integer 
   Dim path as String 
   Dim newFile as String 
   Dim oDocSheets,oSheet as Object 
    
   ' setup the options of the export csv filter 
   ' for details see http://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options 
    
   Dim filterArgs(1) as new com.sun.star.beans.PropertyValue 
   filterArgs(0).Name  = "FilterName" 
   filterArgs(0).Value = "Text - txt - csv (StarCalc)" 
   filterArgs(1).Name  = "FilterOptions" 
   ' field sep(44 - comma), txt delim (34 - dblquo), charset (0 = system, 76 - utf8), first line (1 or 2) 
   filterArgs(1).Value = "44,34,76,1" 

   ' get the document path from the name 
   path = ConvertFromUrl(Dirname) 
    
   'Iterate through the spreadsheets in a file and save each to a 
   ' $filename/$sheet_name.csv 
   oDocSheets = StarDesktop.CurrentFrame.Controller.Model.Sheets 
   For i = 0 To oDocSheets.Count-1 
        'retrieve sheet reference, set as active sheet 
        oSheet = oDocSheets(i) 
        ThisComponent.CurrentController.setActiveSheet(oSheet) 
        
        ' make the filename 
        if ismissing(newFileNamePrefix) then 
              newFile = ConvertToUrl(path & oSheet.Name & ".csv") 
        else 
              newFile = ConvertToUrl(path & newFileNamePrefix & oSheet.Name & ".csv") 
        end if 

        ' save csv to disk file newFile 
          ThisComponent.storeAsUrl(newFile, filterArgs) 
   Next 

End Sub 

' stolen from the forums, works like a buggy dirname(1) 
' but does not take argument, works with 
' the url of the currently open document instead 

Function Dirname 

   Dim odoc as object 
   Dim fileName As String 
   Dim n As Long 

    odoc = ThisComponent 
    fileName = odoc.url 

    For n = Len( fileName ) To 1 Step -1 
      If Mid( fileName, n, 1 ) = "/" Then Exit For 
    Next n 

   ' assign var to func name to return value 
   ' oh my 
   Dirname = Left( fileName, n ) 

End Function 
LibreOffice 4.4.2.2 40m0(Build:2) on Ubuntu 15.04
FrustratedInFresno
Posts: 3
Joined: Mon Aug 15, 2016 2:21 am

Re: [Solved] Save calc document like csv file by macro

Post by FrustratedInFresno »

I was able to get around the error and Save the file as a csv by making the following change:

Code: Select all

Sub OpenFileSaveAsCSV (sourceURL, csvURL, csvName)
	'MsgBox(sourceURL)
	'MsgBox(csvURL)
	'MsgBox(csvName)

	Dim document   as object
	Dim Dummy() 'An (empty) array of PropertyValues
	rem ----------------------------------------------------------------------
	rem open the source file
	document = StarDesktop.loadComponentFromURL(sourceURL, "_blank", 0, Dummy)
	
	rem ----------------------------------------------------------------------
	rem save the source file as a csv by the same name
	Dim dispatcher as object
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	dim args1(2) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "URL"
	rem --this value represents the target name/csv path/file name
	args1(0).Value = csvURL
	args1(1).Name = "FilterName"
	args1(1).Value = "Text - txt - csv (StarCalc)"
	args1(2).Name = "FilterOptions"
	args1(2).Value = "44,34,ANSI,1,,0,true,true,true"
	
	'dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())
	FileURL = convertToURL(csvURL)
	document.StoreAsURL(FileURL, args1())


End Sub
OpenOffice 3.1 on Windows 7
Post Reply