[Macro Calc] Create a copy of the current file to a specific location

chitabbiv · February 1, 2023, 9:39am

Hello,
I would like to create a macro that makes a copy of the current file to a pecific location with a specific name.
So far my code look like this and I cannot figure out what is the problem here.

Sub CreateCopy
	Dim oDoc As Object
	Set oDoc = ThisComponent
	Dim sFileName As String
	sFileName = "file///C:/path/to/my/folder/" & Format(Now, "dd-mm-yyyy") & ".ods"
	Dim dummy(1) as new com.sun.star.beans.PropertyValue
	dummy(0).Name = "FilterName"
	dummy(0).Value = "calc8"
	oDoc.storeToURL(sFileName, dummy)
	ThisComponent.Close
	ThisComponent.CurrentController.Frame.loadComponentFromURL(sFileName, "_blank", 0, dummy())
End Sub

What I get here is a runtime error:

Blockquote
BASIC runtime error.
An exception occurred
Type: com.sun.star.io.IOException
Message: SfxBaseModel::impl_store <file///C:/path/to/my/folder/01-02-2023.ods> failed: 0x81a(Error Area:Io Class:Parameter Code:26).
Blockquote

Of course I have write permission to the folder and space on disk

Thanks for your help.
Cheers.

KamilLanda · February 1, 2023, 10:23am

file:/// and no file///

Or you can use ConvertToUrl

Sub CreateCopy
	Dim oDoc As Object
	Set oDoc = ThisComponent
	Dim sFileName As String
	'sFileName = "file:///C:/path/to/my/folder/" & Format(Now, "dd-mm-yyyy") & ".ods"
	sFileName = ConvertToUrl("C:\path\to\my\folder\")  & Format(Now, "dd-mm-yyyy") & ".ods"
	Dim dummy(0) as new com.sun.star.beans.PropertyValue
	dummy(0).Name = "FilterName"
	dummy(0).Value = "calc8"
	oDoc.storeToURL(sFileName, dummy)
	ThisComponent.Close(true)
	StarDesktop.loadComponentFromURL(sFileName, "_blank", 0, array())
End Sub

Or simply use the command FileCopy.

chitabbiv · February 1, 2023, 11:26am

Hello Kamil,
Thanks for your answer.
Of course I made a typo when I posted, but this was not the issue.
I actually solved the problem ith this macro, which does almost everything I want except for opening the newly generated file after closing the template file.

Sub CreateCopy
Dim oDoc As Object
Set oDoc = ThisComponent
Dim sFileName As String
sFileName = "C:/path/not/important/" & Format(Now, "dd-mm-yyyy") & ".ods"
Dim args1(1) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
    args1(0).Value = "file:///" & sFileName
args1(1).Name = "FilterName"
args1(1).Value = "calc8"

Dim dispatcher as object
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(oDoc.CurrentController.Frame, ".uno:SaveAs", "", 0, args1())

ThisComponent.Close(true)
End Sub

And I am 90% satisfied. The only issue is that I would like that the new file would be open at the end.

Can you help me with that?

KamilLanda · February 1, 2023, 12:05pm

Add the line for open to file to the end.

ThisComponent.Close(true)

StarDesktop.loadComponentFromURL(ConvertToUrl(sFileName), "_blank", 0, array())

karolus · February 1, 2023, 12:05pm

Exactly… so you need to …storeAsUrl and your done!

Sub CreateCopy
	Dim oDoc As Object
	Set oDoc = ThisComponent
	Dim sFileName As String
	sFileName = ConvertToUrl("C:\path\to\my\folder\")  & Format(Now, "dd-mm-yyyy") & ".ods"
	Dim dummy(0) as new com.sun.star.beans.PropertyValue
	dummy(0).Name = "FilterName"
	dummy(0).Value = "calc8"
	oDoc.storeAsURL(sFileName, dummy) '#important: storeAsUrl instead storeToUrl
End Sub

mikekaganski · February 1, 2023, 1:56pm

Please do not try to create URLs like that. You will inevitably end up with problems at some point. URLs are not simply paths with slashes replaced and file:/// appended. They URL-encode things where needed. So please just use the proper function ConvertToUrl, which @KamilLanda suggested.

KamilLanda · February 1, 2023, 1:58pm

It is also possible to open the source file as template → it will create a copy of source file, and then save this new file to ODS via storeAsUrl. The example is with FilePicker, there are the filters for ODS and CSV in function chooseFile, you can change it.

Sub openConvertSave 'open template and save it as ODS
	dim oDoc as object, sFileName$, sTemplateUrl$
	
	rem open file as template
	'sTemplateUrl="d:\template.ods" 'your constant source file
	sTemplateUrl=chooseFile("d:\mydoc") 'File Picker in initial directory
	dim args1(0) as new com.sun.star.beans.PropertyValue
		args1(0).Name="AsTemplate"
		args1(0).Value=true 'new file from Template
	oDoc=StarDesktop.loadComponentFromURL( ConvertToUrl(sTemplateUrl), "_blank", 0, args1() ) 'open the copy of file
	
	'sFileName=ConvertToUrl("C:\path\to\my\folder\")  & Format(Now,  "dd-mm-yyyy") & ".ods"
	sFileName=ConvertToUrl("d:\")  & Format(Now,  "dd-mm-yyyy") & ".ods" 'output file
	dim dummy(0) as new com.sun.star.beans.PropertyValue
		dummy(0).Name="FilterName"
		dummy(0).Value="calc8"
	oDoc.storeAsURL(sFileName,  dummy)
End Sub

Function chooseFile(optional sInitDir$) as string 'dialog for FilePicker opened in initial directory
	dim oFileDlg as object, oFileAccess as object, oFiles as object, sFile$, bDir as boolean
	rem check init directory
	if NOT isMissing(sInitDir) then 'parameter sInitDir exists
		if FileExists(sInitDir) then bDir=true 'directory exists
	end if	
	if bDir=False then sInitDir=CreateUnoService("com.sun.star.util.PathSettings").Work 'initial directory from menu: Tools/Options -> LibreOffice/Paths -> My Documents
		
	oFileDlg=CreateUnoService("com.sun.star.ui.dialogs.FilePicker")
	with oFileDlg 'set the filter for shown files in filepicker
		.AppendFilter("All files (*.*)", "*.*")
		.AppendFilter("Calc (*.ods)", "*.ods")
		.AppendFilter("CSV (*.csv)", "*.csv")
		.SetCurrentFilter("Calc (*.ods)") 'default filter in dialog
	end with
	oFileAccess=CreateUnoService("com.sun.star.ucb.SimpleFileAccess")

	oFileDlg.SetDisplayDirectory( ConvertToUrl( sInitDir ) )
	if oFileDlg.execute() then 'file picker dialog
		oFiles=oFileDlg.getFiles()
		if ubound(oFiles)>=0 then
			sFile=oFiles(0)
		end if
	end if
	if sFile="" then stop 'no file selected
	chooseFile=sFile
End Function

chitabbiv · March 7, 2023, 10:12pm

thanks all of you for your answers, this is a hobby project and I could keep it up only after one month.
I solved in this way it may be useful for others in the future:

Sub CreateCopy
	Dim oDoc As Object
	Set oDoc = ThisComponent
	Dim sFileName As String
	sFileName = "C:/path/to/my/file/" & Format(Now, "dd-mm-yyyy") & ".ods"
	Dim args1(1) as new com.sun.star.beans.PropertyValue
	args1(0).Name = "URL"
    args1(0).Value = "file:///" & sFileName
	args1(1).Name = "FilterName"
	args1(1).Value = "calc8"

	Dim dispatcher as object
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	dispatcher.executeDispatch(oDoc.CurrentController.Frame, ".uno:SaveAs", "", 0, args1())

	ThisComponent.Close(true)
End Sub

erAck · March 9, 2023, 5:20pm

A suggestion: use "yyyy-mm-dd" instead, that way when listing the directory the files are in date order (unless you want them grouped by day of month…).

chitabbiv · March 9, 2023, 5:46pm

Thanks, for the suggestion, but it is what I want in this case.