OfficeAutomation

I have an Application on Windows that use OfficeAutomation
In my code I have:

Var excel As New ExcelApplication
excel.Workbooks.Add

After some research I found the origin of the problem. So I put a Break on the second line “excel.Workbooks.Add”. It stops there, therefore I suppose there is no problem with the first line.
When I pretend to continue, I am sent automatically to the “Exception err” at the end, where the information about “err” is:

imatge

I have no idea about what the problem can be.
Any suggestion will be welcome.
Thanks in advance.

Well, your code is assuming that just because the excel variable is valid that the workbooks property has been created. Have you tried checking if workbooks is nil before trying to use it?

Also, I suggest you turn Break on Exceptions back on while debugging. Then you don’t have to search for where the problem is occurring. The debugger will just stop for you.

Doing a Google search for

Excel error "-2147418111"

Makes me believe that excel is outright rejecting your automation request. It looks like the user has to authorize the request.

When i do a Web search with the error number, Microsoft corrupt workbook support page is listed first despite the page not mentioning the error number. Can you do this from within Excel under macro control or is it only a problem with automation?

Thank you Greg.
Probably it is a problem about the version of Office in my computer. I am using a University licence and it should be some problem with it. I will search about it.
I also made a Google search, but I forgot the quotation marks so I got nothing. :crazy_face:
By the way. I inserted the line
If excel.Workbooks = Nil then break
but the program was not even able to read it. It went to the Exception directly.

You might check and see if they allow automation. Sometimes this is locked down to avoid viruses.

Thanks again Greg.
Your last answer generated a question:
Supposing I find the way to solve this problem. should I say the users that they have to check if they have a running version of Office which allows automation?

got you a valid excel object first?
you have a compatible ms office installed?
is there any excel app still running in background?

I’d say that if there’s an exception, you may need to. I don’t know how recently that plug-in was updated either, so it could just be a compatibility issue with the version of office that you have.

Thank you Markus for your time.
Yes, I have Microsoft Office installed and Excel works perfectly well in my computer.
But it is version 2010 and most probably this is the reason of the problems.
However I didn’t find (probably I couldn’t find it although it exists) anywhere information about the minimum version of Office to be used.
Most probably this version doesn’t work with the Automation although it works in my computer.

Hello @Ramon_SASTRE,

I am taking a guess that the reason why VBA code would work on one computer and not the other might be due to the old VBA component dependency.

Could I ask you to check the VBA dependency from this website?

https://www.ispringsolutions.com/blog/installing-vba-component

1 Like

just to verify
you copied
\Xojo 2021r3.1\Extras\Office Automation\MSOfficeAutomation.rbx
into
:\Xojo 2021r3.1\Plugins\MSOfficeAutomation.rbx
?

it seems with office 365 it create a excel instance

Thanks Eugene,
I did what the link you sent says and it seems it works!!
Anyway I will change the version of my Office for a new one, but I will check the VBA dependency.

1 Like

Thanks Markus,

Yes I copied the rbx file into the Plugin folder. I’m sure this is not the problem. But I thank you for this advice.