How to save as a spreadsheet with macro

marqueslp · June 16, 2020, 12:52am

I’m trying to use the following code to save a file as a calc file (extension “.ods”). It uses a sub to save it (save File sub) but it does as a Write file no matter the properties set in the dialog box. I know how to do it without a dialog, but I need it. All attempts failed, so I’m asking for help.

 sub SalvaComBox
   Dim FPType(0) as integer
   FPType(0) = 10
    oFilePicker = CreateUnoService("com.sun.star.ui.dialogs.OfficeFilePicker")
    oFilePicker.appendFilter("Planilha ODF (*.ods)","*.ods")
    oFilePicker.DisplayDirectory = ConvertToURL("C:\Users\mydocs\Desktop\")
    oFilePicker.setTitle ("Save file as:")
    oFilePicker.initialize(FPType())
   oFilePicker.execute
   if ubound(oFilePicker.Files) < 0 then 
   sFileName =""
  else 
  sFileName=oFilePicker.Files(0)
  end if
  sFilterName=oFilePicker.CurrentFilter
  if sFileName <> "" then
  call saveFile(sFilename)
  MsgBox "file saved"
  end if
  end sub

sub saveFile(sfileName as string)
   oSimpleFileAccess = createUNOService ("com.sun.star.ucb.SimpleFileAccess")
  oOutputStream = createUNOService ("com.sun.star.io.TextOutputStream")
  oOutputStream.setOutputStream(oSimpleFileAccess.openFileWrite(sFileName))
 end sub

JohnSUN · June 16, 2020, 8:50am

What is it ods as second parameter in call saveFile()? Why do you not use storeAsURL() or storeToURL()? Also see here

marqueslp · June 16, 2020, 10:50pm

Sorry there was a little mistake on the code originally posted (no need for the second parameter in call saveFile()). As for your answer. Thanks for the suggestions. But I got to study. I’m not a programmer. I’ve managed to copy a lot from codes i’ve found on the net and believe I’ve used the StoreAsURL before, but can’t find a way to make it work with the dialog (sub SalvaComBox above).

mikekaganski · June 17, 2020, 8:45am

I am sorry, but what is being saved? The export filters available depend on the module; and if the current document is not a spreadsheet, you will not be able to save it as an ODS.

I may of course misunderstand the situation, but IIUC, the code presented is not enough to see the whole picture.

dosreis57 · September 15, 2022, 10:21am

Olá bom dia! Adaptei o exemplo para ODT. Quando tudo estava afinado afinal não funciona! Aparentemente, porque sResult = StoreDocument(ThisComponent, FilterNames, oNmDoc , sPath),
onde o «oNmDoc» é o N.º REF atribuido por progama (é “Último” criado na pasta + 1 = “2022-092”, o Próximo há de).
Ora , o StoreDocument tem + 1 parâmetro optional, que só deverá gravar se o valor=1: Select Case iAddProcedure Case 1 CommitLastDocumentChanges(sPath) End Select
Porém , não grava porque “CommitLastDocumentChanges…” dá o Erro de que o Procedimento não existe!!! Alguma ideia? Alguém pode ajudar ?

JohnSUN · September 15, 2022, 11:29am

@dosreis57 Hello good afternoon! The CommitLastDocumentChanges procedure is located in the FormWizard module in the library with the same name FormWizard.
Usually, the optional parameter 1 is not used in the StoreDocument() function, there is no need. If you want to avoid getting an error with its application, then add the library loading to your code:
GlobalScope.BasicLibraries.LoadLibrary("FormWizard")
But it seems to me that your problem is somewhere else. It will be good if you create a new question and describe your difficulties in detail. And please translate the text into English.

dosreis57 · September 16, 2022, 5:42pm

In you Oriiginal example was about scalc.
I changed this from the original code but something is not OK?!
Dim FilterNames(1,2) As String, sResult As String, sPath As String
FilterNames(0,0) = “ODF Text Document (.odt)" : FilterNames(0,1) = ".odt” : FilterNames(0,2) = sNmDoc ’ “2022-93” …
Now, i wonder if FilterName = FilterNames(0,1) = “*.odt” is ok!?

Because while debugging, after “iAccept = oStoreDialog.Execute()=ok”, it jumps direct into
NOSAVING:
If Err <> 0 Then
’ Msgbox(“Document cannot be saved under '” & ConvertFromUrl(sPath) & “’”, 48, GetProductName())

Is the FilterNames ok? Is there some way of uncomment this Msgbox?
Best regards
PS - MeanWhile i tryied the fallowing code and it saved ok
sub SvFileName(sfileName as string)
oSimpleFileAccess = createUNOService (“com.sun.star.ucb.SimpleFileAccess”)
oOutputStream = createUNOService (“com.sun.star.io.TextOutputStream”)
oOutputStream.setOutputStream(oSimpleFileAccess.openFileWrite(sFileName))
end sub ’ SvFileName
But it 'isnot interactive!
Can you help?

JohnSUN · September 16, 2022, 7:51pm

Replace this with FilterNames(0,2) = ""

dosreis57 · September 22, 2022, 4:47am

Dear Sir
Thank you for your answer, It worked fine.
Meanwhile I created macro that assigns the Last number (+1) of document saved in a given folder and assigns or saves it in a REFA field. The question is, whether before saving to disk (BeforePost) there is a way to intercept the Number (see image, painted blue) in order to guarantee that the No. of the document recorded on disk is not different from the name assigned automatically or at least assign a read only to that field since the Name is assigned and sent to the interface by the macro!?

Any ideas? Can you help?

JohnSUN · September 22, 2022, 5:09am

In other words, do you want the user to be able to specify in the dialog only the folder for saving, and the file name should always be the one that the program generates automatically? That is, so that the dialog before saving prompts the user to select a directory, by default showing something like “Z:\wacra\Correspondencia\Enviada\Ano_2022\Cartas”, and then in the specified folder create a file with a name generated from the contents of the form field “REFERENCIA”? Yes, it can be done, you just need to use the dialog type com.sun.star.ui.dialogs.FolderPicker instead of com.sun.star.ui.dialogs.FilePicker. But then it will be impossible to specify whether to save the file with the extension .odt or without the extension. However, a macro can create both files at the same time…

dosreis57 · September 25, 2022, 11:09am

Dear Sir,
Sorry i did not make my self cler. No, i do not want the user make any changes at all. The writen file to the disk, must be the one prvided by parameter as it it’s now, But, i can’t allow him to change.
So, or the input text must be put in readonly mode, or when writting
done to the disk i must be able to validate like this:
If REFERENCIA<>FileNameToWriteDown then
Error…
Else
Proceed and write/flush to disk (no changes allowd)
End If
Best regards
Mário

JohnSUN · September 25, 2022, 11:14am

Sorry but I do not understand. No changes at all? Even the folder in which you want to save a document with a hard-coded name cannot be changed? Then why all these difficulties? Just form the name from the “REFERENCIA” field, prefix it with the path and save.

dosreis57 · September 28, 2022, 9:10am

Good Morning Sir,
I’m building a template. So it is true, no changes allwed. Only that one provided by program macro or Quit.
As far as i realise there is no other way to garantee order/discipline on disk write and the integrety.
I’ve no need to have the letter on disk called “2022-098.odt” (REFA=098/SG/2022 to some guy Mr. Anthony) with the text that belongs Mrs. Francis written on disk in a “box” called (REAFA=“2022-096.odt”) or any other name that only God know’s what! wich written text respect’s to Mr. Antony, because the user overwrited the initial text, or Vice-Versa!
When using this LetterA,ott a new number is writen down to disk acordding with the new REFA provided by the macro. Latter, at the very end, i must ensure that data on disk and REFA are still
the same, and write it to a database table (kind of registration electronic/digital book):

JohnSUN · June 17, 2020, 8:19am

You can make it a lot easier. The standard library Tools in the module ModuleControls has a StoreDocument() function that does just that. Just call this function with the correct parameters and analyze the result of execution.

Option Explicit

Sub saveAsODS()
Dim FilterNames(1,2) As String, sResult As String, sPath As String
	FilterNames(0,0) = "Planilha ODF" : FilterNames(0,1) = "*.ods" : FilterNames(0,2) = "calc8"
	FilterNames(1,0) = "Any type" : 	FilterNames(1,1) = "*.*" : 	 FilterNames(1,2) = ""
	sPath = ConvertToURL("C:\Users\mydocs\Desktop\")
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
	sResult = StoreDocument(ThisComponent, FilterNames, "", sPath)
	If sResult = "" Then 
		MsgBox "File not saved", MB_ICONEXCLAMATION, "Error"
	Else 
		MsgBox "File saved as " + ConvertFromURL(sResult), MB_OK, "Success" 
	EndIf 
End Sub

marqueslp · June 20, 2020, 12:53am

@johnSun Thanks. It does the job.