Help with MSOfficeAutomation.rbx (Excel multi sheet)

I’ve used MSOfficeAutomation.rbx some times, not regularly, during the last years. No problem until now.

Currently I try to do something more complex tan before. I have a window with a tabpanel, with several panels.
Each panel has a listbox, and I pretend to save all these listboxes in an Excel file.
So I’ve created a Workbook with several Sheets and in each Sheet I’ve saved a Listbox.
Up to here no problem. But at the end I use this code:

excel.ActiveWorkbook.SaveAs(f.NativePath, 56) 'xlExcel8 f.Open
excel has been defined

Var excel As New ExcelApplication

In fact I’ve tried many different formats (56 is a format for xlExcel8), but no progress!

With the first line of the code a file is created (“t.xls” for example). Even I can open it with LibreOffice without any problem.
But if I pretend to open it with Excel (manually or with the second line of code, it doesn’t matter) something weird happens. Excel begins to open but freezes immediately and you can not close it at all. I need to use the TaskManager (CTRL+SHIFT+Esc) to close it.

Has anyone any idea about what is happening?
Obviously it is a problem of Excel, Microsoft and MSOfficeAutomation.rbx, but I would appreciate any help to solve this problem.

Hello Prof Ramon,

I hope you are doing well.

After working with some code, it appears that *.xlsx files are for Open XML workbooks, and Office.xlExcel8 is for Excel 97-2003 workbooks that have the old *.xls format.

Maybe change the extension to *.xls for this format?

When the Excel suffix name is changed to .xls it works. When I attempt to open and xlsx that is saved in the old xls format, I get the error:

Excel cannot open the file 'TestSaveAsWorkbook.xlsx because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

It looks like the last section of the error describes the issue “…the file extension matches the format of the file.” Below is working code on my machine that has: Windows 10, Excel 2016, Xojo 2019 r3.1.

[code]Sub Action() Handles Action
Var excel as new ExcelApplication
excel.Workbooks.Add
excel.visible = true

Var f as new FolderItem(“C:\test\TestSaveAsWorkbook.xls”, FolderItem.PathModes.Native)

excel.Range(“A1”).Value = “A new workbook is added, and this information is using the ‘SaveAs’ command’”
excel.ActiveWorkbook.SaveAs(f.NativePath,56)

excel = Nil

Exception err as OLEException
MsgBox err.message
End Sub
[/code]

Does this work for you?

Edit: Fixed a spelling mistake.

Thank you Eugene.
Although I’ve tried many different combinations (Debugging, compiled, formats…) I will try again your recommendations. And I will try also another computer (I’ve thought about a system problem).
I will tell you the results.

I think I found the solution. Not sure but it works up to now.
As I said in my first message, after creating the file (manually clicking on it, out of the application or internally with “folderitem.open”) I got an Excel application frozen. I had to close it through the Task Manager. But then I realized that I had to close two items: the Excel Application and the Excel process in the list of background processes. So there were two “things” open. So I thought I had to close two things as well in my application. So before opening the file I added these two lines:

Excel.Quit Excel = Nil
And it works! Let’s hope it will continue working.