Constant indicating access or excel

Question

I write several macros for general use. I have a macro that needs to be run in Access and Excel. I 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 does not work. The Application object does not exist at compile time. My question is: is there a constant indicating that the macro works in Access or Excel?

Answer 1

Compiler constants can only be used in the compiler. This means that you need to configure constant values ​​before deployment, for example:

#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

- , Excel Access, , , :

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"
    sFile = Left(app.CurrentDb.Name, InStrRev(app.CurrentDb.Name, ".")) & "foo"
End If