Office 365 breaks my Excel code?

Hello

My code below, which is working with Excel 2010 and 2013, is giving an exception with Office 365.
Guess I stayed away too long, and here is something new to learn, right?
Please advise. Any pointer is appreciated.

Regards
Lennart

[code] Dim excel As New ExcelApplication
Dim sheet As ExcelWorksheet

Dim fileToOpen as FolderItem
fileToOpen=getFolderItem(“C:\ProgramData\ExcelTest.xlsx”)
fileToOpen.Launch("",False)

Sheet=excel.Worksheets(excel.worksheets.Count) '<— OLEExeption
Dim oleAfter as New OLEParameter
oleAfter.Value=Sheet
oleAfter.Position=2
excel.Worksheets.Add oleAfter

excel.Range(“C1”).value=“Blablabla”

Exception err as OLEException
MsgBox "Oh, why? "+err.message '<— “Member not found (failed on worksheets)”[/code]

Did you try: excel.worksheets.Count - 1 ?

Well, now I have … without success.

Try to open the Application.Workbook first and then consult the Worksheets instead of Launch.

excel.Workbooks.Open “C:\ProgramData\ExcelTest.xlsx”

Msgbox Str(excel.Worksheets.Count) // Worksheets?

Hmmm, seems that we have incompatibilities issues going on… <https://xojo.com/issue/59228>

I tried to file a new case in Feedback (fresh install),
but it keeps telling me that “communication with the feedback server failed”.
( How does one report that? )

Is your app 32bit but Excel 64bit?
I ran into trouble with a VBScript failing when Office was upgraded to 64bit
The OLE objects were different in some way.

Excel is 64 bit.
I tried both 32 and 64 bit in my app (running in the IDE Xojo 2019r3.1) with the same OLEException.
But my Excel 2013 is also 64 bit, and no problem there.

Hello Lennart,

Here is some working code on my Windows 10 machine running Xojo 2019 r.1.1, Excel 2016:

[code]Sub Action() Handles Action
Dim excel As New ExcelApplication
Dim sheet As ExcelWorksheet
excel.Visible = True

Dim fileToOpen as FolderItem
fileToOpen=getFolderItem(“C:\test\ExcelTest.xlsx”)
//fileToOpen.Launch("",True)
excel.Workbooks.Open(fileToOpen.NativePath) //<-updates object

Sheet=excel.Worksheets(excel.worksheets.Count)

Dim oleAfter as New OLEParameter
oleAfter.Value=Sheet
oleAfter.Position=2
excel.Worksheets.Add oleAfter

excel.Range(“C1”).value=“Blablabla”

Exception err as OLEException
MsgBox "Oh, why? "+err.message
End Sub
[/code]

What seems to work is to use the excel.Workbooks.Open command, as this updates the excel OLEObject. The fileToOpen.Launch command was commented. A possible suggestion is to also have excel.Visible to true, as there can be multiple instances of the object added and running in the background of the computer without knowing it.

I am not sure if this works with Excel 365, and maybe give it a try and see… :slight_smile:

Edit: I forgot to mention, ProgramData has file restrictions, and you may need to move the file to another folder. I used c:\test in this example.

Thank you Eugene,

The

excel.Workbooks.Open(fileToOpen.NativePath)

really did the trick.
It works in Xojo 2019r3.1 with Excel in Office 365.

In RealStudio 2011r2 and 2012r2.1 I replaced NativePath with AbsolutePath.

Many thanks!
Lennart

Do yourself a favor, Lennart, and invest in Eugene Dakin’s "I Wish I Knew How To … " series of eBooks on MS Office Automation. He’s got one on MS Word and one on MS Excel (along with a host of others on various Xojo-related topics) … You can find them at xojolibrary.com

In looking at the solution Eugene provided you, I think most, if not all of it, is present in his Excel eBook.

Frankly, I couldn’t do squat until I purchased them. MS Automation documentation is sparse at best (as I’m sure you have found). Within an hour after getting my first book from him, I was writing some pretty complex Xojo MS Word code doing some really neat things that got my customer’s attention. The purchase price is a real “cheap date” … it would cost you more for a meal for two at McDonalds than it does for one of his eBooks. His style of writing along with giving lots of examples makes it an easy read with quick comprehension. I refer back to them frequently , they have become a mainstay of my Xojo reference library.

Bite the bullet and get the eBooks from Eugene … you won’t regret it, I assure you!

Thanks for the good advise Don,

Yes, I noticed that there was quality to Eugene’s post.