Randall Spalding-Fecher 10/15/2015

Like

703 Views

Dear LEAP Community,

Has anyone created a generic script using the API in LEAP that will extract multiple tables from the "favorites" results charts and export them to excel?

I thought it would be better to "stand on the shoulders" of others who had done this, instead of starting from scratch.

Thank you! Randall

Taylor Binnington 10/19/2015
 

Like

701 Views

Hi Randall,

Sorry for the wait on this. The following will perform the basic operation for you, which you can modify as you see fit.

I wrote it in VBScript, so all you need to do is select Advanced: Edit Scripts, then drop this text into a new script and hit 'Run Script'.


' Instantiate LEAP object, if not already done
' ============================================
If Not IsObject(LEAP) Then
Dim LEAP
set LEAP = GetObject("", "Leap.LEAPApplication")
End If
' ============================================

' Loop through favorite charts, export each to named Excel tab
' ============================================
for each fav in LEAP.Favorites
fav.activate
LEAP.ExportResultsXLS(fav.name)
next
' ============================================


Hope this helps,
Taylor

Randall Spalding-Fecher 10/19/2015
 

Like

700 Views

Hi Taylor
Brilliant! Two quick follow ups:

If I want to update with new results each time I run the model again (as opposed to creating new sheets each time), how do I specify this?
Also, can I designate a file name for the excel workbook? (I assume "fav.name" is the tab name)
Best wishes, Randall

' Instantiate LEAP object, if not already done
' ============================================
If Not IsObject(LEAP) Then
Dim LEAP
set LEAP = GetObject("", "Leap.LEAPApplication")
End If
' ============================================

' Loop through favorite charts, export each to named Excel tab
' ============================================
for each fav in LEAP.Favorites
fav.activate
LEAP.ExportResultsXLS(fav.name)
next
' ============================================

Taylor Binnington 10/19/2015
 

Like

699 Views

I think the simplest way to accomplish this would be to check for the existence of Excel tabs with names matching each of the favorite charts, and if they exist, delete and then recreate them.

Doing so would allow you to continue to use the ExportResultsXLS method - otherwise you'll need to write your own data export routine that updates cells in existing worksheets. This is straightforward enough, but just requires a little more work.

The one caveat to my above method is that the deletion would corrupt references to data in your 'favorite' Excel tabs. You could mitigate this by closing any workbooks that reference the exported data before refreshing, which should preserve any references.

Taylor

Randall Spalding-Fecher 10/19/2015
 

Like

698 Views

That makes sense, and it is not difficult have another Excel workbook just reference the LEAP export.
Does the script as you wrote it do this check for an existing tab, or does it need to be modified?

Taylor Binnington 10/19/2015
 

Like

697 Views

Hi Randall,

The example that I started does not do any checking - it really is just a bare-bones script to export favorites, which can be adapted to your specific needs. If a named tab already exists, it will append a number to the newly-created tab.

Taylor

Randall Spalding-Fecher 10/19/2015
 

Like

693 Views

I tested it on a small region and it works perfectly. I will just save the results workbook each time over a previous version. The references work fine that way.
Thank you!

Randall Spalding-Fecher 10/20/2015
 

Like

688 Views

One additional thought - it there a simple way to only export the favorites in a specific folder, without having to write an entirely new routine for extracting the data? Thank you!

Taylor Binnington 10/20/2015
 

Like

684 Views

Hi Randall,

Yes - I would insert a conditional statement to export the results only if the favorite folder name matches some condition.

In the API documentation, the LEAPFavorite object supports a (readonly) FolderName property which returns the name of the chart's folder. Unfortunately, it's not working right now - I mention it here because it should work in the next version of LEAP.

In the meantime, you can extract the name of the folder manually from the favorite chart's name. For only one level of folders in your list of favorite charts, the following will work:

' Instantiate LEAP object, if not already done
' ============================================
If Not IsObject(LEAP) Then
Dim LEAP
set LEAP = GetObject("", "Leap.LEAPApplication")
End If
' ============================================

' Loop through favorite charts, export each to named Excel tab.
' ============================================
for each fav in LEAP.Favorites
fname = Split(fav.name,"#")

if fname(0) = "name_of_folder" Then
fav.activate
LEAP.ExportResultsXLS(fname(1))
end if
next
' ============================================

Taylor

Randall Spalding-Fecher 10/20/2015
 

Like

683 Views

Brilliant! Thank you!