Page 1 of 1
[Solved] Save calc document like csv file by macro
Posted: Fri Jun 12, 2009 3:49 pm
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
Re: save calc document like csv file by macro
Posted: Fri Jun 12, 2009 11:02 pm
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())
Re: Save calc document like csv file by macro
Posted: Mon Jun 15, 2009 10:05 am
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
Re: Save calc document like csv file by macro
Posted: Mon Jun 15, 2009 11:20 am
by briglia23
SOOLVE THANKYOU VERY MUCH
Re: Save calc document like csv file by macro
Posted: Tue Jun 09, 2015 6:28 pm
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
Re: [Solved] Save calc document like csv file by macro
Posted: Tue Aug 16, 2016 4:45 am
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