Page 1 of 1
How to programmatically open files from Application folder
Posted: Sat Nov 03, 2018 6:52 am
by Ashleigh W
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!
Re: How to programmatically open files from Application folder
Posted: Sat Nov 03, 2018 10:08 am
by paulsimon
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
Re: How to programmatically open files from Application folder
Posted: Sat Nov 03, 2018 10:53 am
by Ashleigh W
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
Re: How to programmatically open files from Application folder
Posted: Sat Nov 03, 2018 4:51 pm
by Ashleigh W
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)
Re: How to programmatically open files from Application folder
Posted: Mon Nov 05, 2018 12:32 pm
by bgregs
'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.
Re: How to programmatically open files from Application folder
Posted: Tue Nov 06, 2018 6:58 am
by Ashleigh W
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
Re: How to programmatically open files from Application folder
Posted: Tue Nov 06, 2018 1:24 pm
by bgregs
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!
Re: How to programmatically open files from Application folder
Posted: Fri Nov 09, 2018 7:22 am
by Ashleigh W
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
Re: How to programmatically open files from Application folder
Posted: Fri Nov 09, 2018 11:21 am
by tomok
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).
Re: How to programmatically open files from Application folder
Posted: Fri Nov 09, 2018 12:20 pm
by Ashleigh W
Thanks a lot and thanks for understanding. Finally my problem is solved.
