How to programmatically open files from Application folder
-
- Posts: 88
- 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
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!
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!
- paulsimon
- MVP
- Posts: 808
- Joined: Sat Sep 03, 2011 11:10 pm
- OLAP Product: TM1
- Version: PA 2.0.5
- Excel Version: 2016
- Contact:
Re: How to programmatically open files from Application folder
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
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
-
- Posts: 88
- 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
Thanks Paul.
In Cell I have a formula like this 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
In Cell I have a formula like this
Code: Select all
=SUBNM(pServer&":}ApplicationEntries","",1)
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
-
- Posts: 88
- 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
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
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)
-
- Posts: 77
- 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
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.'excel crashes here... what name / string do i pass here for strBlobName
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.
-
- Posts: 88
- 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
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
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
-
- Posts: 77
- 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
Hi Ashleigh,
Try this one out:
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!
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
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!
-
- Posts: 88
- 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
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
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
-
- MVP
- Posts: 2832
- 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
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).
-
- Posts: 88
- 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
Thanks a lot and thanks for understanding. Finally my problem is solved.