Determining if a workbook was opened from SAP NetWeaver Platform

[Former Member]

When working with a Analysis workbooks, you'll probably need to know, at some point, whether the workbook was opened from SAP NetWeaver or BI Platform.

There doesn't seem to be an Analysis function that will return this detail, so for want of a better approach, I've been using VBA to discover more about the workbook's origin.

When you open a workbook from SAP NetWeaver, and check its path, you'll find that it is actually copied to, and opened from cache folder in your temp directory.

The cache folder path includes the hWnd of the current Excel session. Armed with this information, we can write a VBA function that determines if a workbook has been opened from the cache directory, and thereby infer that it was opened from a SAP Platform.

Here's a VBA function that will return True if the ActiveWorkbook was opened from NetWeaver, and otherwise False:

Function OpenedFromSAPPlatform() As Boolean

  'This function determines if the active workbook was opened from a NetWeaver Platform

  'by checking if it was opened from Analysis' cache folder path

  Const TemporaryFolder As Byte = 2

  Const sAOCache As String = "sapaocache"

  Const sDownload As String = "download"

  Dim sPath As String

  Dim sHwnd As String

  Dim sAOCacheFilePath As String

  Dim wb As Workbook

  Dim oFSO As Object 'Scripting.FileSystemObject

  Dim oTempFolder As Object 'Scripting.Folder

  Dim sCacheFile As String

  Dim sWorkbookFile As String

  Set wb = ActiveWorkbook

  Set oFSO = CreateObject("Scripting.FileSystemObject")

  'Build the path where we would expect to find a file opened from SAP Platform

  Set oTempFolder = oFSO.GetSpecialFolder(TemporaryFolder)

  sHwnd = CStr(Application.Hwnd)

  sAOCacheFilePath = oTempFolder.Path & "\" & sAOCache & "\" & sHwnd & "\" & sDownload & "\" & wb.Name

  'Check if a cache file exists at the path we expect it

  If oFSO.FileExists(sAOCacheFilePath) Then

    'Compare the ShortPath to ensure both paths use the same short file/folder name syntax

    sCacheFile = oFSO.GetFile(sAOCacheFilePath).ShortPath

    sWorkbookFile = oFSO.GetFile(wb.FullName).ShortPath

    If StrComp(sCacheFile, sWorkbookFile, vbTextCompare) = 0 Then

      OpenedFromSAPPlatform = True

    End If

  End If

End Function

If you experience unexpected results, or find a more robust way of finding if a workbook was opened from a SAP platform, please add your findings in the comments.