LEAP

346 views | 8 messages Discussion: LEAP
Topic: Export all data to Excel from Results view?Subscribe | Previous | Next

Rabi Vandergon 1/17/2017

Like

Hello,

I am curious if there is a method to export data from the Results view similar to the process of exporting from the Analysis view. In Analysis view, I can use the dropdown menu from Analysis and select "Export to Excel." This brings up a menu of options for the export. The exported data in a database-like format is useful when using an external application such as Tableau to shape, explore and analyze the data.

I am wondering if there is a method to conduct a similar process from the Results view. This would create a similar output, but instead would contain the calculated values by year, scenarios, tags, etc. Is there a method to obtain a table-like export of multiple variables using the LEAP API?

Thank you for your time,

Rabi Vandergon

Emily Ghosh 1/18/2017

Like

Hi Rabi,

The Results View does not include a batch export function like the Analysis View. However, you can definitely export the results data using the API. An example batch export script for exporting energy demand results to Excel is provided in the link below:

https://www.energycommunity.org/default.asp?action=9&fid=22&TID=3431

You can easily modify this script for other branch types (i.e. transformation, resources), result variables, etc., through LEAP's built-in script editor. Hopefully this helps get you started, but if you need any additional help, please let us know.

Thanks!
Emily

Rabi Vandergon 1/22/2017

Like

Hi Emily,

Thanks for your response! The script ran well from the link you posted and I have almost everything I need in the output in Excel. I tagged branches, and I am hoping to add the tags to the export. In addition, I am trying to change the units from gigajoules to Million BTU. I am new to VB and am using the information highlighted below. I keep getting an error message when I try running the edited script posted below. Do you have any suggestions?

The resources I've found so far are as follows:

LEAP API Objects in right hand pane
LEAP Variables in right hand pane
LEAP help online - using this resource is helpful, but I am still running into errors when I try running the change below (I added 4 lines attempting tag outputs and changed units to Million BTU).

Thanks again,

Rabi

' ============================================

' Bulk energy demand export tool for LEAP

' Taylor Binnington, Stockholm Environment Institute

' ============================================

' Instantiate objects

' ============================================

If Not IsObject(LEAP) Then

set LEAP = CreateObject("Leap.LEAPApplication")

End If

set xla = CreateObject("Excel.Application")

' ============================================

dim tech_branches

set tech_branches = CreateObject("Scripting.Dictionary")

for each b in LEAP.Branches

if b.Branchtype = 4 then

tech_branches.Add b.BranchID, b.Name

end if

xla.ScreenUpdating = true

set xlw = xla.Workbooks.Add

LEAP.Verbose = 4

LEAP.Visible = true

set xls = xla.sheets.add

xls.name = "Exported from LEAP"

' Add headers to Excel worksheet

r = 1

xls.Cells(r, 1).Value = "Scenario"

xls.Cells(r, 2).Value = "Year"

xls.Cells(r, 3).Value = "Branch"

xls.Cells(r, 4).Value = "Fuel"

xls.Cells(r, 5).Value = "Value (MMBtu)"

xls.Cells(r, 6).Value = "Tag (Name)"

xls.Cells(r, 7).Value = "Tag (Group)"

' Loop through scenarios, years, branches and export final energy demand in gigajoules

for each s in LEAP.Scenarios

for y = LEAP.BaseYear to LEAP.EndYear

for each key in tech_branches.keys

r = r + 1

xls.Cells(r, 1).Value = s

xls.Cells(r, 2).Value = y

xls.Cells(r, 3).Value = LEAP.Branch(key).FullName

xls.Cells(r, 4).Value = LEAP.Branch(key).Fuel.Name

xls.Cells(r, 5).Value = LEAP.Branch(key).Variable("Energy Demand Final Units").Value(y, "Million BTU")

xls.Cells(r, 6).Value = LEAP.Branch(key).Tag.Name

xls.Cells(r, 7).Value = LEAP.Branch(key).Tag.TagGroup

xla.visible = true

Taylor Binnington 1/25/2017

Like

Hi Rabi,

What error message do you see? Please be sure to include this information.

When I copy and paste your script into LEAP's Script Editor and try to run it, I also see an error (though I'm not sure if it's the same as yours):

LEAP Automation Error, Variable.Value: Cannot access results variable: "Energy Demand Final units" in a non-result view

To resolve this, try the modification below. Note that I've made two changes:

Ensured that results have been properly calculated by changing to LEAP's Results view, and then switching back to Analysis view at the end of the script.
Corrected your syntax to extract tags attached to branches. Note that there may be multiple tags assigned to branches, so the script concatenates each tags into a comma-separated list before writing into Excel. Note that I haven't taken any care to group tags with their respective tag groups - instead, in the final two columns of the output spreadsheet you'll simply find a list of all tags, and all tag groups which are associated with one or more of those tags.

Hope this helps,

Taylor

' ============================================

' Instantiate objects

' ============================================

If Not IsObject(LEAP) Then

set LEAP = CreateObject("Leap.LEAPApplication")

End If

set xla = CreateObject("Excel.Application")

' ============================================

dim tech_branches

set tech_branches = CreateObject("Scripting.Dictionary")

'dim tagnames, taggroups As String

for each b in LEAP.Branches

if b.Branchtype = 4 then

tech_branches.Add b.BranchID, b.Name

end if

xla.ScreenUpdating = true

set xlw = xla.Workbooks.Add

LEAP.Verbose = 4

LEAP.Visible = true

set xls = xla.sheets.add

xls.name = "Exported from LEAP"

' Add headers to Excel worksheet

r = 1

xls.Cells(r, 1).Value = "Scenario"

xls.Cells(r, 2).Value = "Year"

xls.Cells(r, 3).Value = "Branch"

xls.Cells(r, 4).Value = "Fuel"

xls.Cells(r, 5).Value = "Value (MMBtu)"

xls.Cells(r, 6).Value = "Tag (Name)"

xls.Cells(r, 7).Value = "Tag (Group)"

LEAP.ActiveView = "Results"

' Loop through scenarios, years, branches and export final energy demand in gigajoules

for each s in LEAP.Scenarios

for y = LEAP.BaseYear to LEAP.EndYear

for each key in tech_branches.keys

r = r + 1

xls.Cells(r, 1).Value = s

xls.Cells(r, 2).Value = y

xls.Cells(r, 3).Value = LEAP.Branch(key).FullName

xls.Cells(r, 4).Value = LEAP.Branch(key).Fuel.Name

xls.Cells(r, 5).Value = LEAP.Branch(key).Variable("Energy Demand Final Units").Value(y, "Million BTU")

for each t in LEAP.Branch(key).Tags

tagnames = t.Name & ", " & tagnames

taggroups = t.TagGroup.Name & ", " & taggroups

xls.Cells(r, 7).Value = taggroups

tagnames = ""

taggroups = ""

xla.visible = true

LEAP.ActiveView = "Analysis"

Rabi Vandergon 2/1/2017

Like

Hello Taylor,

The modification you added worked well. To further complicate matters, we are trying to add regions to our output. My coworker modified the script (which is pasted below), but it resulted in an error when it hit line 65, which is when it starts to try and assign tags. She is not very familiar with
scripting libraries and doesn't really understand how the tags are being assigned. She is wondering if there is a way to halt the code so we can
see what’s happening. She mentioned other code editors have this stop feature, which might be helpful in this case. Any guidance would be much appreciated.

Thank you for all of your help so far,

Rabi

' ============================================

' Instantiate objects

'
============================================

If Not IsObject(LEAP) Then

set LEAP =
CreateObject("Leap.LEAPApplication")

End If

set xla =
CreateObject("Excel.Application")

' ============================================

dim tech_branches

set tech_branches =
CreateObject("Scripting.Dictionary")

'dim tagnames, taggroups As
String

for each b in LEAP.Branches

if b.Branchtype = 4 then

tech_branches.Add b.BranchID,
b.Name

end if

xla.ScreenUpdating = true

set xlw = xla.Workbooks.Add

LEAP.Verbose = 4

LEAP.Visible = true

set xls = xla.sheets.add

xls.name = "Exported from
LEAP"

' Add headers to Excel worksheet

r = 1

xls.Cells(r, 1).Value =
"Scenario"

xls.Cells(r, 2).Value = "Year"

xls.Cells(r, 3).Value =
"Branch"

xls.Cells(r, 4).Value =
"Region"

xls.Cells(r, 5).Value =
"Fuel"

xls.Cells(r, 6).Value =
"Value (MMBtu)"

xls.Cells(r, 7).Value =
"Tag (Name)"

xls.Cells(r, 8).Value =
"Tag (Group)"

LEAP.ActiveView =
"Results"

' Loop through scenarios, years,
branches and export final energy demand in gigajoules

for each s in LEAP.Scenarios

for y = LEAP.BaseYear to
LEAP.EndYear

for each key in
tech_branches.keys

for i = 1 to leap.regions.count

r = r + 1

xls.Cells(r, 1).Value = s

xls.Cells(r, 2).Value = y

xls.Cells(r, 3).Value =
LEAP.Branch(key).FullName

xls.Cells(r, 4).Value =
LEAP.Regions(i).Name

xls.Cells(r, 5).Value =
LEAP.Branch(key).Fuel.Name

xls.Cells(r, 6).Value =
LEAP.Branch(key).Variable("Energy Demand Final Units").Value(y,
"Million BTU")

for each t in
LEAP.Branch(key).Tags

tagnames = t.Name & ",
" & tagnames

taggroups = t.TagGroup.Name
& ", " & taggroups

xls.Cells(r, 8).Value =
taggroups

tagnames = ""

taggroups = ""

xla.screenupdating = true

xla.visible = true

LEAP.ActiveView =
"Analysis"

Taylor Binnington 2/13/2017

Like

Hi Rabi, sorry for the wait.

It's difficult to tell from your post what you mean by line 65, but I'm afraid there is no hotkey combination to break execution using LEAP's internal script editor. You can, however, call LEAP from any of your favorite Windows-based editors, though, by appropriately linking to it.

I'd suggest inerting some print statements throughout, to make sure that correct arguments are being passed to LEAP's API functions.

Good luck,

Taylor

Kate Desrochers 4/4/2017

Like

HI Taylor and Emily,

I'm following up on
Rabi's code questions, working on the same issue.

Your help was much
appreciated but now we're running in to two issues.

We were able to run the code below
without error, however, while it was generating rows with different
scenarios in columns (as asked in line 56), it was not actually changing
the active scenario and providing different values. It was simply giving
us the value for one of our scenarios over and over again. Strangely, it
was the value for the scenario with index number "3". I have
tried various commands to have it loop through active scenarios, but each
of them has thrown a bug. Can you help us figure out the code to get these
values for different scenarios (I think by setting different active
scenarios?)

When I went back to rerun the
original, no-bug code (that changed the names of the scenarios but didn't
change the value), the code began to throw an error: Object not a
collection" , referring to line 50, "For each s in
Leap.Scenarios.Count". I thought you had recently changed the object
from "scenario" to "scenarios", but I might be wrong.
Can you help me understand the issue here?

Thank you very much.
The code is pasted below. I have added line numbers, please let me know if you'd like the code without them. Additionally, you'll see that we've temporarily commented out all of our tag work-- that's something we can do with equations in excel, and we wanted to minimize the possible areas for error while trouble shooting.

Thanks,

Kate

1' ============================================

2 ' Instantiate objects

3' ============================================

4 If Not IsObject(LEAP) Then

5 set LEAP = CreateObject("Leap.LEAPApplication")

6 End If

7

8 set xla = CreateObject("Excel.Application")

9 ' ============================================

10

11 dim tech_branches

12 set tech_branches = CreateObject("Scripting.Dictionary")

13 'dim tagnames, taggroups As String

14

15 For i = 1 to Leap.Regions.Count

16 Leap.Regions(i).Active = True

17 V = Leap.Regions(i).Active

18 tech_branches.RemoveAll

19 for each b in LEAP.Branches

20 if b.Branchtype = 4 then

21 tech_branches.Add b.BranchID, b.Name

22 end if

23 next

24

25 xla.Visible = false

26 xla.ScreenUpdating = true

27

28 set xlw = xla.Workbooks.Add

29

30 LEAP.Verbose = 4

31 LEAP.Visible = true

32

33 set xls = xla.sheets.add

34 xls.name = "Exported from LEAP"

35

36 ' Add headers to Excel worksheet

37 r = 1

38 xls.Cells(r, 1).Value = "Scenario"

39 xls.Cells(r, 2).Value = "Year"

40 xls.Cells(r, 3).Value = "Branch"

41 xls.Cells(r, 4).Value = "Region"

42 xls.Cells(r, 5).Value = "Fuel"

43 xls.Cells(r, 6).Value = "Value (MMBtu)"

44 xls.Cells(r, 7).Value = "Tag (Name)"

45 xls.Cells(r, 8).Value = "Tag (Group)"

46

47 LEAP.ActiveView = "Results"

48

49 ' Loop through scenarios, years, branches and export final energy demand in gigajoules

50 For each s in Leap.Scenarios.Count

51 LEAP.Scenarios(s).Active = true

52 for y = LEAP.BaseYear to LEAP.EndYear

53 for each key in tech_branches.keys

54

55 r = r + 1

56 xls.Cells(r, 1).Value = s

57 xls.Cells(r, 2).Value = y

58 xls.Cells(r, 3).Value = LEAP.Branch(key).FullName

59 xls.Cells(r, 4).Value = LEAP.Regions(i).Name

60 xls.Cells(r, 5).Value = LEAP.Branch(key).Fuel.Name

61 xls.Cells(r, 6).Value = LEAP.Branch(key).Variable("Energy Demand Final Units").Value(y, "Million BTU")

62

63 'for each t in LEAP.Branch(key).Tags

64 'tagnames = t.Name & ", " & tagnames

65 'taggroups = t.TagGroup.Name & ", " & taggroups

66 'next

67

68 'xls.Cells(r, 7).Value = tagnames

69 'xls.Cells(r, 8).Value = taggroups

70 'tagnames = ""

71 'taggroups = ""

72

73 next

74 next

75 next

76 next

77

78 xla.screenupdating = true

79 xla.visible = true

80 LEAP.ActiveView = "Analysis"

Emily Ghosh 12/9/2017

Like

For future reference, here is the final code from this thread (the issues were resolved by email). Thanks!

'============================================

' Instantiate objects

' ============================================

If Not IsObject(LEAP) Then

set LEAP = CreateObject("Leap.LEAPApplication")

End If

set xla = CreateObject("Excel.Application")

' ============================================

dim tech_branches

set tech_branches = CreateObject("Scripting.Dictionary")

'dim tagnames, taggroups As String

set xlw = xla.Workbooks.Add

For i = 1 to Leap.Regions.Count

Leap.Regions(i).Active = True

V = Leap.Regions(i).Active

tech_branches.RemoveAll

for each b in LEAP.Branches

if b.Branchtype = 4 then

tech_branches.Add b.BranchID, b.FullName

end if

xla.ScreenUpdating = true

LEAP.Verbose = 4

LEAP.Visible = true

set xls = xla.sheets.add

xls.name = LEAP.Regions(i).Name

' Add headers to Excel worksheet

r = 1

xls.Cells(r, 1).Value = "Scenario"

xls.Cells(r, 2).Value = "Year"

xls.Cells(r, 3).Value = "Branch"

xls.Cells(r, 4).Value = "Region"

xls.Cells(r, 5).Value = "Fuel"

xls.Cells(r, 6).Value = "Value (MMBtu)"

xls.Cells(r, 7).Value = "Tag (Name)"

xls.Cells(r, 8).Value = "Tag (Group)"

LEAP.ActiveView = "Results"

' Loop through scenarios, years, branches and export final energy demand in gigajoules

For s = 2 to 4

LEAP.Scenarios(s).Active = true

for y = LEAP.BaseYear to 2018

'for y = LEAP.BaseYear to LEAP.EndYear

for each item in tech_branches.items

r = r + 1

xls.Cells(r, 1).Value = s

xls.Cells(r, 2).Value = y

xls.Cells(r, 3).Value = LEAP.Branch(item).FullName

xls.Cells(r, 4).Value = LEAP.Regions(i).Name

xls.Cells(r, 5).Value = LEAP.Branch(item).Fuel.Name

xls.Cells(r, 6).Value = LEAP.Branch(item).Variable("Energy Demand Final Units").Value(y, "Million BTU")

'for each t in LEAP.Branch(item).Tags

'tagnames = t.Name & ", " & tagnames

'taggroups = t.TagGroup.Name & ", " & taggroups

'next

'xls.Cells(r, 7).Value = tagnames

'xls.Cells(r, 8).Value = taggroups

'tagnames = ""

'taggroups = ""

xla.screenupdating = true

xla.visible = true

LEAP.ActiveView = "Analysis"