Macro: save as xls

[seicaso]

Hi everyone,

I’m trying to save a xls-file, which I generate by a macro. But it just won’t work. This is why I want to ask you which ideas you have about it.

This is a part of my code (“speicherpfad” and “Name” are defined in another part of the code):

dim mFileType(0) as new com.sun.star.beans.PropertyValue
mFileType(0).Name = "FilterName"
mFileType(0).Value="MS Excel 97"
ThisComponent.StoreAsURL( speicherpfad & Name & ".xls", mFileType())

The error message I get is:

BASIC-Laufzeitfehler. Es ist eine Ausnahme aufgetreten
Type: com.sun.star.task.ErrorCodeIOException
Message: sfxBaseModel::impl_store
failed: 0x81a

I have tried different things, nothing worked. On one page I found the information, that this error message might mean I can’t save the file because this kind of document doesn’t exist yet and it’s not possible to generate a new document?! But I also tried to overwrite a manually saved file with the same name, and it wouldn’t save either.

I wonder whether the spelling of “MS Excel 97” is totally correct? I found it often, but it was never on tutorials, but on forum entries from earlier than 2011.

Any ideas? If you need more information, I’ll post it.

Thank you already for your ideas
Julia

[mark_t]

I think you spelt this correctly. List of filter names is here:
https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options

Edited to add: I tried this and it worked for me on Windows 8.1, LO 5.2.0.4.

If your file contains any features that can not be saved as xls then this would give an error. Can you manually save the same file to the same location?

Are you sure “speicherpfad & Name & “.xls”” is producing a valid file path name?

Edited to add:
I was able to get the same error you reported when I removed this line from my test sub.

sDirectory = replace(oDoc.Location, "%20", " " )	'	Allow for spaces in name as %20

Edited again as after reading answer from @Lupp I realise the error message when I remove this line is caused by leaving sDirectory = “”. Corrected in the following code. As I take the directory path from oDoc.Location it is already in the correct URL format and so does not need %20 replaced with " ".

I find the following works:

    Sub SavetoXLS()
    
    	Dim oDoc As Object
    	Dim Args(0) As New com.sun.star.beans.PropertyValue
    	Dim sDirectory As String
    	Dim sFileName As String
    	
    	oDoc = thisComponent
    	
    	sFileName = oDoc.Title
'    	sDirectory = replace(oDoc.Location, "%20", " " )	'	Allow for spaces in name as %20
'    	sDirectory = replace(sDirectory, sFileName, "" )
    	
        sDirectory = replace(oDoc.Location, sFileName, "" )

    	sFileName = left(sFileName, instr(sFileName,".") - 1)
    	
    	'	Save the file as calc .ods format
    	Args(0).Name = "FilterName"
    	Args(0).value = "MS Excel 97"
    	
    	oDoc.storeAsURL( sDirectory & sFileName & ".xls", Args)
    End Sub

[seicaso]

Thank you for your answer. My operating system is Ubuntu 16.04 LTS.

I tried saving it manually, I can - but there’s the standard window popping up which asks me whether I really want to save in xls and not in ods. But I guess this isn’t the problem?!

Yes, I am sure about the file path name, it’s the same error when I enter the complete path manually.

[seicaso]

Thank you so much! Now it works for me, too! That’s great. So it was the space letter.

[Lupp]

Anybody posting the same question in different forums should crosslink the posts.
This is a relevant requirement of netiquette. Otherwise a volunteer trying to help will probably wste his (f/m) time to solve an already solved problem again. A questioner using different names in different forums aggravates the problem.
See http://en.libreofficeforum.org/node/15648#comment-50566

[mark_t]

I agree with you on the cross posting and I should have also included the link from the other site here, as you did above in your comment. As we have had so much trouble with the ask site availability over the last week we probably have to expect that some users will become frustrated and try elsewhere.

[mark_t]

Edited my answer as there was an error in my code when I was able to duplicate your error message. I suspect the answer posted by @Lupp may be your correct answer, using the correct URL format seems to allow %20 instead of a space in the URL. It would be helpfull if you could try @Lupp’s answer instead of removing %20 and if this works flag @Lupp’s answer as correct.

[Lupp]

An additional hint:
Use the ConvertToURL function. It will recognise and translate (e.g.) the stubborn backslash used by Win and other specifica. It will also convert spaces contained in the pathname into the code-notation %20.
Example: ConvertToURL("C:\Users\This User\Documents\Test\My FileName.xls")
will return: file:///C:/Users/This%20User/Documents/Test/My%20FileName.xls .