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
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.