Ask Your Question
0

Macro: save as xls

asked 2016-08-19 19:12:34 +0200

seicaso gravatar image

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2016-08-19 19:36:21 +0200

mark_t gravatar image

updated 2016-08-20 18:13:43 +0200

I think you spelt this correctly. List of filter names is here: https://wiki.openoffice.org/wiki/Docu...

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
edit flag offensive delete link more

Comments

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 gravatar imageseicaso ( 2016-08-19 19:54:23 +0200 )edit

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

seicaso gravatar imageseicaso ( 2016-08-19 20:39:29 +0200 )edit

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/1...

Lupp gravatar imageLupp ( 2016-08-20 09:52:32 +0200 )edit

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 gravatar imagemark_t ( 2016-08-20 17:47:09 +0200 )edit

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.

mark_t gravatar imagemark_t ( 2016-08-20 18:08:23 +0200 )edit
0

answered 2016-08-20 11:05:26 +0200

Lupp gravatar image

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 .

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-08-19 19:12:34 +0200

Seen: 1,604 times

Last updated: Aug 20 '16