Topic: Problem with Linking to ExcelSubscribe | Previous | Next
Ralph Samuelson 2/21/2011
2173 Views We are having a very annoying problem when we have set up our LEAP models to link to Excel spreadsheets. Sometimes LEAP does not recognize the link, and either shows the link in red, or shows the link in blue, but incorrectly reads the values as zero. The problem seems to occur when the first value in the row of the spreadsheet being read is blank or zero.
If you still have the example model I sent you on the 10 February ('February 2011 Brainstorming Version')you may be able to reproduce the problem. Under Key Assumptions->Industrial Sector Indices->Iron and Steel->Historical Fuel Use, if you see red links or see that the value of Coal is zero, there is a problem. The problem is, unfortunately, intermittent. If you don't see it the first time, try switching to anther branch, say Demand->Other Sector Demand->Residential Demand, close LEAP and the spreadsheets that it opened, open it again, and go back to Key Assumptions->Industrial Sector Indices->Iron and Steel->Historical Fuel Use. Sometimes the problem does not appear until you have opened LEAP at least once before. Also, look at the other Key Assumptions and see if they are linking correctly. If that doesn't work, I can send you another example to try.
Experimentally, I have found you can usually work around the problem by making the first branch under each folder a dummy with a constant non-zero value. But it would be nice to get the problem fixed.
Ralph
Tory Clark 2/22/2011
2172 Views Hi Ralph,
Since you are using the Data function, the first thing to check would be that you have values for all years. The data function specifies time-series data values as year/value pairs. Values are not interpolated between data points. Any unspecified data years are assumed to have zero value.
I cannot replicate issue of the expression being marked in red ink to indicate an invalid expression. Please do send me another example and I will investigate further.
Best,
Tory
Ralph Samuelson 2/22/2011
2163 Views Hi Tory,
I really do believe there is a problem here. Specifically, if the first cell of the row in the spreadsheet that the Data function refers to is zero or blank, LEAP will frequently read the entire row as a blank, even though other cells are non-zero (it is, of course, not a problem if LEAP reads the blank cells as zero). The problem is a frustratingly intermittent one--sometimes it happens, sometimes it doesn't--but it is most likely to happen if the LEAP Area has been opened at least once before. May I suggest that you again take a look at the example I sent you earlier, specifically, Key Assumptions->Industrial Sector Indices->Iron and Steel->Historical Fuel Use. If the row for Coal is all zero, there is a problem, since you can check that the spreadsheet shows non-zero values in this row. If it seems to be working, then I will need to send you another example.
The problem with the red ink is similarly intermittent, but I suspect the two problems are related, so if you can reproduce one, you will probably find the source of the other.
Ralph
Tory Clark 2/23/2011
2162 Views Thanks for the additional information, Ralph.
I realize that I was not fully clear in my last post. I did try to replicate the issue in your file (February 2011 Brainstorming Version.leap), but everything seemed to be working on my machine.
I will continue to troubleshoot with the file you had previously sent me, but if you have another example on hand to send that would help us fully understand the bug.
Also, are you seeing this issue only on one machine, or on multiple? Please provide the following system and software information for the machines that are having this issue:
- Operating System
- Version of LEAP (the latest is v.94)
- Version of Microsoft Office
Thanks!
Tory
Ralph Samuelson 2/24/2011
2156 Views Hi Tory,
I just sent you (leap@sei-us.org) two new LEAP areas that may allow you to replicate the problem, along with the requested information.
Regards, Ralph
Tory Clark 3/7/2011
2143 Views For those that are experiencing this problem, it seems like the best way to fix it is to go to Area: Force refresh of excel links in LEAP.
Josefine Larsson 11/4/2011
1937 Views Hi Tory,
I have a problem with excel linking. First of all, LEAP only finds one of my excel document. And it only finds one of the spreadsheets in that document. I named a range of 365 days*24 hours in the only doucument LEAP could find when it was saved and open on my computer. But then I got the error messages:
Invalid Excel range.
*One column*8760
*One row*8760 columns
*365 rows*24 columns
*365 columns*24 rows
But I know for sure that I have named range of 365 days *24 hours.
(LEAP seems to find the excel range I want to use.)
Regards
Josefine
Tory Clark 11/4/2011
1936 Views Hi Josefine,
Can you give me a little more background information about what you are trying to do here when you get the "Invalid Excel Range" message? It seems like you are trying to enter a new set of time slices and load curve data into LEAP.
Can you tell me which screen you are using when you try to link to excel and which specific steps I would have to take to replicate what you are seeing on your screen?
Thanks!
Tory