Solved Determine if M365 via VBA - how? (1 Viewer)

[isladogs]

Yes - similar to 32/64 bitness conditional compilation
BUT you have to use the # character at the start of each condition as I showed in post #18
Then Access ignores the code in the condition not valid for that setup

 

[AHeyne]

Some workbook properties can be accessed in a kind on non-early-binding way by using the 'BuiltinDocumentProperties' property of the workbook.

Since I don't have a current version of Microsoft Excel I can't test it myself, but you could give this code a try:

On Error Resume Next

Dim docSenseLabel As Object
Set docSenseLabel = oExcelWrkBk.BuiltinDocumentProperties("SensitivityLabel")

On Error Goto 0

If docSenseLabel Is Nothing Then
    MsgBox "SensitivityLabel property doesn't exist."
Else
    MsgBox "SensitivityLabel property exists"
End If

 

[Gasman]

Some workbook properties can be accessed in a kind on non-early-binding way by using the 'BuiltinDocumentProperties' property of the workbook.

Since I don't have a current version of Microsoft Excel I can't test it myself, but you could give this code a try:

On Error Resume Next

Dim docSenseLabel As Object
Set docSenseLabel = oExcelWrkBk.BuiltinDocumentProperties("SensitivityLabel")

On Error Goto 0

If docSenseLabel Is Nothing Then
    MsgBox "SensitivityLabel property doesn't exist."
Else
    MsgBox "SensitivityLabel property exists"
End If

I believe we are talking about Access here?

 

[Marshall Brooks]

Making progress ...

@isladogs - Thanks - I missed the part of your comment with the #. I'd seen that for 32/64-Bit Bitness, but never knew what it did. I learned something new.

If it doesn't work, I have another idea. The sub that is failing is called Export2XLS. Rather than splitting it into two functions and fixing 30 calls to it with conditionals, I think this should work:

Rename Export2XLS as Export2XLS_M365. Copy Export2XLS_M365 as Export2XLS_O2016. Comment out M365 only references in Export2XLS_O2016. Optionally comment out conditional checks in Export2XLS_M365.

Create a new sub Export2XLS with only 5 lines of code

If IsM365 = True Then
Call Export2XLS_M365
Else
Call Export2XLS_O2016
End If

It is much more complicated than your idea (which is why I will try yours first), and I think it still would not compile in O2016, but I think it would work in O2016 since the M365 variables are never called (and it is easier than changing 30 function calls to conditionals and then potentially changing them all back. I should have seen it sooner.

@AtzeX - Good suggestion, but a bit late. Would have eliminated the idea for the IsM365 subroutine. But since I use that for both Outlook and Excel, I would have to test in Outlook and it probably doesn't work there. But a good approach to the problem.

@Gasman - I appreciate the effort, but @AtzeX 's comments are actually on-point here. Sensitivity labels don't exist in Access (at least yet - fingers crossed it stays that way!!!). They were added in M365 (and apparently in something called Azure) to Excel, Outlook, Word, and ??? Powerpoint. What I am doing is setting sensitivity labels in VBA that is called from Access and manipulates Excel and Outlook. (But you can't set the labels in Outlook (yet?) and have to use SendKeys - which is ugly, but working.

 

[Marshall Brooks]

Conditional compiling didn't work.

My code looks like:

#If IsM365 = True Then
   'Do something
#End If

It seems to be ignoring the 'Do Something part - even if IsM365 = True.

Out of curiosity and as I somewhat expected, the code now compiles and works fine in O2016 MSI - so basically, with the hashtags, the code in the hashtags is ignored - in either M365 or MSI.

On to Plan B.

 

[Marshall Brooks]

Success!!!!

Creating a new sub Export2XLS with only 5 lines of code as mentioned in Reply #24 works:

Sub Export2XLS()
If IsM365 = True Then
    Call Export2XLS_M365
Else
    Call Export2XLS_O2016
End If
End Sub

As expected, it compiles in M365 and fails to compile in Office MSI, but I'm okay with that, it works in either flavor.

Thanks to all for the assistance!!!

 

[AHeyne]

Can't follow the idea to use the conditional compilation constant `Is365` or `IsM365`...
Where does it come from? Is it set by VBA environment automatically (I have no Office 365 to test it myself)?
If not, when is it set and based on what?

 

[Marshall Brooks]

See Reply #10 and Reply #12.

There is a registry key that doesn't exist for MSI and has a different value for M365.

Basically, I wrote my own user-defined function to check if the key exists and what it's value is.

That part of the code worked, but the compiler was throwing errors even if the function would have evaluated false meaning the variable types were not valid for that version.

 

[AHeyne]

Ok, you don't use any conditional compilation in your solution, I see now.

 

[isladogs]

I still think it may be possible to get conditional compilation to work for this using a defined compiler constant
See Understanding conditional compilation (VBA) | Microsoft Learn

I'll do some investigating when I have time

 

[AHeyne]

I am very interested in how you provide the value that `#IF` will rely on to choose the right path....

 

[Marshall Brooks]

Okay, so it MIGHT work with:
#Const IsM365 = True.

I can try that, I also have a revision to my earlier code.

 

[Marshall Brooks]

It fails the other way with the constant, i.e.

#If IsM365 = True Then
    ' Do something
#End If

Works in O2016, but the "Do Something" code is never executed, even in M365.

#Const IsM365 = True
#If IsM365 = True Then
    ' Do something'
#End If

Works in M365, Fails in Office 2016 with user-defined type is not defined.

Basically, without the #Const, it ignore the IF block altogether and with the #Const, it seem to ignore the conditionals.

My new idea is to put only the M365 code in a separate function, so instead of M365 or O2016 versions, I will have:

Function Export2XLS()
...
If IsM365 = True Then
    Call ApplyM365Labels()
End if

Sub ApplyM365Labels()
End Sub

Then if I can figure out how to make it work in Outlook ...