Constant that indicates Access or Excel

Question

I am writing some macros for general use. I have a macro that should be executable under Access and Excel. I have tried the following idea.

#If Application.Name = "Microsoft Excel" Then
    sFile = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, ".")) & "foo"
#ElseIf Application.Name = "Microsoft Access" Then
    sFile = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, ".")) & "foo"
#End If

Of course this doesn't work. The object Application doesn't exist at compile time. My question is whether is there a constant that indicates that the macro runs under Access or Excel?

Answer 1

You can only use Compiler constants in a Compiler condition. That means you'd have to adjust the constant values before deploying, like so:

'Requires References to BOTH Excel AND Excel
#Const AccessHost = False
#Const ExcelHost = True
#If ExcelHost Then
    sFile = Left(ThisWorkbook.FullName, InStrRev(ThisWorkbook.FullName, ".")) & "foo"
#ElseIf AccessHost
    sFile = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, ".")) & "foo"
#End If

If you want something more dynamic, and you don't have early-bound references to both Excel and Access, then you don't want compiler directives, but you do need to use late-binding to make it work in both hosts:

Dim app As Object 'Late-binding
Set app = Application
If app.Name = "Microsoft Excel" Then
    sFile = Left(app.ThisWorkbook.FullName, InStrRev(app.ThisWorkbook.FullName, ".")) & "foo"
ElseIf app.Name = "Microsoft Access" Then
    sFile = Left(app.CurrentDb.Name, InStrRev(app.CurrentDb.Name, ".")) & "foo"
End If