How to programmatically open files from Application folder

Post Reply
Ashleigh W
Posts: 61
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

How to programmatically open files from Application folder

Post by Ashleigh W » Sat Nov 03, 2018 6:52 am

Hi everyone,

Is there a way to open file from Application folder using VBA? One way I tried and worked was to open the file (one at a time) from Action button (Worksheet tab) but I want to be able to loop and load multiple files.

thanks for your help!

User avatar
paulsimon
MVP
Posts: 647
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: 10.1.1 and 10.2.2
Excel Version: 2013

Re: How to programmatically open files from Application folder

Post by paulsimon » Sat Nov 03, 2018 10:08 am

Hi Ashleigh

It depends on what you are trying to do. If you are able to run your VBA on the TM1 Server itself, then the simplest approach assuming that all files have been uploaded to the server would be to loop though all files in the }Externals folder under the main cubes folder.

If you want to do this from the client side then, I have developed routines that work off the }ApplicationEntries dimension, convert the result into a path, and then allow any worksheet to be launched. I do this via the TM1 Web URL API, but if you want to work in VBA, then you can use a similar approach. Bear in mind that the worksheet in an Action Button does not have to be hard-coded but can come from a worksheet reference which can be changed by VBA.

If you upgrade to PA then there are other options in PAX.

Regards

Paul Simon

Ashleigh W
Posts: 61
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

Re: How to programmatically open files from Application folder

Post by Ashleigh W » Sat Nov 03, 2018 10:53 am

Thanks Paul.

In Cell I have a formula like this

Code: Select all

=SUBNM(pServer&":}ApplicationEntries","",1)
and using action button to load it.
Whenever you find sometime could you provide some dummy code to convert the result into a path and open them using VBA in TM1 Perspectives?

thanks again,
Ashleigh

Ashleigh W
Posts: 61
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

Re: How to programmatically open files from Application folder

Post by Ashleigh W » Sat Nov 03, 2018 4:51 pm

Got here so far and still can't make it work... Any advise will be apprciated. thanks.
Overflows/excel crash at 'hBlobName = TM1ValString(hPool, strBlobName, 75)' line

Code: Select all


Dim hPool As Integer
Dim hHandle As Integer
Dim hBlobName As Integer
Dim hBlob As Integer
Dim hObject As Integer
Dim hReturn As Integer
Dim strBuffer As String
Dim hServer As Long
Dim strBlobName As String


hUser = 0
hServer = 0
hPool = 0

'Get a handle to the user session and check it.
hUser = TM1_API2HAN
If hUser = 0 Then
    Err.Raise vbObjectError + 20010, , gSC_TM1APIERR_HPOOL_CANNOT_GET_20010
End If
'----------------------------hServer
' Get the handle to the server
hServer = TM1SystemServerHandle(hUser, "tm1_tst")
'----------------------------

strBlobName = "testfile.xlsx.blob"

'excel crashes here... what name / string do i pass here for strBlobName
hBlobName = TM1ValString(hPool, strBlobName, 75)

hBlob = TM1ObjectListHandleByNameGet(hPool, hServer, TM1ServerBlobs(), hBlobName)

'hReturn = TM1BlobOpen(hPool, hBlob)
'hReturn = TM1BlobClose(hPool, hBlob)
'TM1ValPoolDestroy (hPool)


bgregs
Posts: 37
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: How to programmatically open files from Application folder

Post by bgregs » Mon Nov 05, 2018 12:32 pm

'excel crashes here... what name / string do i pass here for strBlobName
You pass in the blob file name (i.e. Test/Budget_Sampe.blob) to your excel report. As Paul stated above, you can get these names from the }ApplicationEntries dimension.

If you want to work off of the }Externals folder, pass in the name like you see here: https://www.ibm.com/support/knowledgece ... n5730.html

Since you already have a SUBNM setup to work with the }ApplicationEntries dimension, IMHO it would be easiest to just write a simple loop and grab each blob name. If you wanted to be really fancy, you could possibly "flag" the files with attributes so you can easily set the files that should be loaded. Either way, what you're after is the }ApplicationEntries dimension and a for loop. ;)

Ashleigh W
Posts: 61
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

Re: How to programmatically open files from Application folder

Post by Ashleigh W » Tue Nov 06, 2018 6:58 am

bGregs, thanks for help so far. 'm still very to new to tm1 space. Would it be possible to throw some example?

I tried below and it doesn't load the file.
Workbooks.Open ActiveCell.Value (where active cell holds =SUBNM(pServer&":}ApplicationEntries","",1))

I am able to figure out where the files are location in application folder but it has ext. blob not sure how to open it and convert it to normal .xlsx file.

thanks again

bgregs
Posts: 37
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: How to programmatically open files from Application folder

Post by bgregs » Tue Nov 06, 2018 1:24 pm

Hi Ashleigh,

Try this one out:

Code: Select all

Sub open_files()
    Dim path As String
    Dim file As String
     
    path = "\\(your_server)\(admin_host_name)\(path_to)\}Externals\"
    file = Dir(path & "\*.xlsx")
    
    Do While file <> ""
        Workbooks.Open Filename:=path & "\" & file
        ' Add workbook processing logic here
        file = Dir
    Loop
End Sub
It doesn't use the TM1API (which is a good thing! ;) ) and opens up all of the .xlsx files in your }Externals folder. If you have macro enabled worksheets you will need to add that as a valid extension to open in the code above. Once in that loop, you could use macros to run TIs (RunTIProcess) and load your data.

Be warned! Without thinking, I ran that code and opened up 30+ worksheets, locking up my computer and resulting in a shutdown....which is why this response is delayed ;)

Hope this helps!

Ashleigh W
Posts: 61
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

Re: How to programmatically open files from Application folder

Post by Ashleigh W » Fri Nov 09, 2018 7:22 am

bGregs, reallyappreciate your help on this.

My files are in '}ApplicationEntries' folder I veried this using =SUBNM(pServer&":}ApplicationEntries","",1 which returns file path and file name with .blob extension. When i manually opened the file using excel / notepad it showed only file information rather then content as below.

FILE_FORMAT=100
ENTRYNAME=test.xlsx
ENTRYTYPE=blob
ENTRYREFERENCE=TM1:///blob/PUBLIC/.\}externals\test.xlsx_20181002143139.xlsx
REMOVEBLOBONDELETE=Yes


Not sure where to look for '}Externals' folder in server. in C drive I don't see similar folder structures documentation says.
C:\Program Files\Cognos\TM1\Custom\TM1Data\PlanSamp\}Externals\Sample_Budget.xls_20090617155650.xls

User avatar
tomok
MVP
Posts: 2491
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: How to programmatically open files from Application folder

Post by tomok » Fri Nov 09, 2018 11:21 am

Those .blob files are not the Excel workbooks, they are the application objects and only contain information about them. The actual Excel files are in the }Externals folder which is in the Data folder like all the other TM1 objects (like .dim, .cub, etc., files).
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Ashleigh W
Posts: 61
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

Re: How to programmatically open files from Application folder

Post by Ashleigh W » Fri Nov 09, 2018 12:20 pm

Thanks a lot and thanks for understanding. Finally my problem is solved. :)

Post Reply