[Solved] Save calc document like csv file by macro

Keyboard macros or custom scripts

[Solved] Save calc document like csv file by macro

Postby briglia23 » Fri Jun 12, 2009 3:49 pm

Ho can i do this?

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

viewtopic.php?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
briglia23
 
Posts: 155
Joined: Tue Jun 24, 2008 10:09 am

Re: save calc document like csv file by macro

Postby FJCC » Fri Jun 12, 2009 11:02 pm

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   Expand viewCollapse view
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())
AOO 3.4 or 4.1 on MS Windows XP ( before 2013-08-03) or Windows 7
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 6617
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Save calc document like csv file by macro

Postby briglia23 » Mon Jun 15, 2009 10:05 am

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

Postby briglia23 » Mon Jun 15, 2009 11:20 am

SOOLVE THANKYOU VERY MUCH
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

Postby mash » Tue Jun 09, 2015 6:28 pm

Code: Select all   Expand viewCollapse view
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
mash
 
Posts: 1
Joined: Tue Jun 09, 2015 11:21 am

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

Postby FrustratedInFresno » Tue Aug 16, 2016 4:45 am

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

Code: Select all   Expand viewCollapse view
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
FrustratedInFresno
 
Posts: 3
Joined: Mon Aug 15, 2016 2:21 am


Return to OpenOffice Basic, Python, BeanShell, JavaScript

Who is online

Users browsing this forum: No registered users and 2 guests