Accessing a VBA API - need working proof of concept

[Jo D]

Hi all,

I have this VBA API that I need to call: https://docs.microsoft.com/en-us/office/vba/api/excel.application.decimalseparator

Is there a working piece of JNA-using code that accesses such an API, and that I can study?

I know next to nothing about the fiddly details of this kind of API (never been there, done that), so some working code would help me show what parts of JNA I need to touch and what kinds of information I need from the MS documentation.

Regards,

Jo

[Tres Finocchiaro]

Here's an example from the documentation.

It doesn't reference the DecimalSeparator property specifically, but it should be enough to get you started.

https://github.com/java-native-access/jna/blob/a0641dd928776a8e81478015a1f14a39326887f3/contrib/msoffice/src/com/sun/jna/platform/win32/COM/util/office/MSOfficeExcelDemo.java#L75

I would recommend you use ActiveSheet as an Excel property reference/starting point, and create your own similar class with the property that you need.

https://github.com/java-native-access/jna/blob/a0641dd928776a8e81478015a1f14a39326887f3/contrib/msoffice/src/com/sun/jna/platform/win32/COM/office/MSExcel.java#L85

Specifically, clone that class, and add properties that you will need access to.

These examples use the COM object.

There are also dedicated COM projects like JACOB which can do this as well: https://github.com/freemansoft/jacob-project

- Tres.Fin...@gmail.com

--
You received this message because you are subscribed to the Google Groups "Java Native Access" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jna-users+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jna-users/74bc1762-77e5-4d1d-bc91-3c88778350c5n%40googlegroups.com.

[Durchholz, Joachim]

Ah. Line 63 seems to be creating the Application interface, that’s already enough – I need just a setting from Excel itself.

 

That looks dead simple !

I’ll try and see what I get.

 

Thanks a lot!

Regards,

Jo

To view this discussion on the web visit https://groups.google.com/d/msgid/jna-users/CANQs7dDqVyfDAPB1x%3DoB_oBUL1hmFsii5V52jmu6qci9M3UhwA%40mail.gmail.com.

 

Sensitivity: C2 Internal

The content of this e-mail is intended only for the confidential use of the person addressed.
If you are not the intended recipient, please notify the sender and delete this e-mail immediately.
Thank you.

[Jo D]

Hmm... looking at the referenced code, the expected use is that everybody is using their own variant of ComExcel_Application that has just the part of the API that you actually need, is that correct?

Regards,

Jo

[Matthias Bläsing]

Hi Jo,

Am Mittwoch, den 07.04.2021, 09:02 -0700 schrieb Jo D:
> Hmm... looking at the referenced code, the expected use is that
> everybody is using their own variant of ComExcel_Application that has
> just the part of the API that you actually need, is that correct?
>
>

> On Wednesday, April 7, 2021 at 5:22:55 PM UTC+2 Jo D wrote:
>

>> [COM Access to Excel]

it makes life easier if you only need a subset. If you need the full
Excel API, you can have a look here:

https://github.com/matthiasblaesing/COMTypelibraries

and here is the generator:

https://github.com/matthiasblaesing/TlbCodeGenerator

And here is sample code:

https://github.com/matthiasblaesing/TlbDemo

This only covers COM objects, that support IDispatch, but it should
bring you far. I needed this code in the past for my work where letters
and emails were composed with Word and Outlook and the bindings were
used to fuse these things together.

Greetings

Matthias

[Tres Finocchiaro]

On Wed, Apr 7, 2021, 12:02 PM Jo D <Joachim....@six-group.com> wrote:

Hmm... looking at the referenced code, the expected use is that everybody is using their own variant of ComExcel_Application that has just the part of the API that you actually need, is that correct?

That's my impression as well and it is consistent with how I've used JNA for other specific use-cases.

I've used JACOB in the past and avoided this technique but JACOB makes the code very unreadable (and is technically off-topic here).  To that point, I'm happy to link an old JACOB project of mine which calls upon the same COM APIs if you'd like.

I'm not sure what level of compatibility you require but from what I understand porting this code to Mac is a non-starter.  I've read that VBA is moving yo JavaScript for cross-platform but haven't seen any Java examples to surface yet.

[Jo D]

Hi all,

just some answers:

Yes we indeed do have a Mac port, but it does not offer a connection to Excel.

I know that Mac Excel does exist, and it seems to be accessible through Apple Events and (easier) Cocoa, but I have no idea what would be needed to make that work in JNA. Luckily, no customer has asked for a live data interface with Excel for Macs (yet), so we'll cross that bridge when we come to it.

I just manually wrapped Application.isUseSystemSeparators() and Application.getDecimalSeparator() and it worked out of the box, so I'm very happy :-)

[Jo D]

New question: Would anybody happen to know how to find out whether Excel is currently running?

(I'm aware that this is more a VBA API question than a JNA one, I'll do a more general search if you people don't have a quick answer ready.)

[Tres Finocchiaro]

find out whether Excel is currently running?

This example suggests that calling GetObject and checking for a return value of Nothing will give you this information.  I'm not sure how to translate this to JNA.  Perhaps another jna members is more familiar with "GetObject" versus "CreateObject".

https://www.exceltip.com/applications-word-outlook-in-vba/determine-if-an-application-is-available-using-vba-in-microsoft-excel.html 

Quoting:

Function ApplicationIsRunning(ApplicationClassName As String) As Boolean

' returns True if the application is running

' example: If Not ApplicationIsRunning("Outlook.Application") Then Exit Sub

Dim AnyApp As Object

On Error Resume Next

Set AnyApp = GetObject(, ApplicationClassName)

ApplicationIsRunning = Not AnyApp Is Nothing

Set AnyApp = Nothing

On Error GoTo 0

End Function

Function ApplicationIsAvailable(ApplicationClassName As String) As Boolean

' returns True if the application is available

' example: If Not ApplicationIsAvailable("Outlook.Application") Then Exit Sub

Dim AnyApp As Object

On Error Resume Next

Set AnyApp = CreateObject(ApplicationClassName)

ApplicationIsAvailable = Not AnyApp Is Nothing

Set AnyApp = Nothing

On Error GoTo 0

End Function

Personally, I would probably use a utility to iterate over running processes as current user and match a particular process name.

- Tres.Fin...@gmail.com

[Durchholz, Joachim]

Same result here – it seems that GetObject(, "Excel.Application") does what I want, but I have found multiple definitions of GetObject in JNA; I’m pretty sure the GDI-related ones don’t apply (GDI is about graphics, not applications), but that leaves me with a choice of

·         IRunningObjectTable.GetObject

·         IShellFolder.GetObject

·         RunningObjectTable.GetObject

·         EnumMoniker which enumerates a running object table (might be part of the implementation of the other functions)

So I’m unsure which one to try next; I suspect that IShellFolder isn’t relevant and that RunningObjectTable is a wrapper that simplifies using IRunningObjectTable, but I’m unsure how to validate that assumption.

Regards,

Jo

 

From: jna-...@googlegroups.com <jna-...@googlegroups.com> On Behalf Of Tres Finocchiaro

Sent: Donnerstag, 8. April 2021 16:08
To: jna-...@googlegroups.com

--
You received this message because you are subscribed to a topic in the Google Groups "Java Native Access" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jna-users/neZY8KK4C5w/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jna-users+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jna-users/CANQs7dBjZdiRBYOBF%2BX726qSuzboFJzYnfbk5_iLPG0EeRmPXw%40mail.gmail.com.

 

Sensitivity: C2 Internal

[Jo D]

Yes, the magic incantation seems to be GetObject(, "Excel.Application")

At least that's what https://www.mrexcel.com/board/threads/check-to-see-if-excel-file-is-open-from-powerpoint.250370/post-1229651 say.

What I don't know is how to determine an application's class name, but I guess some mysteries have to remain ;-)