Excel exchange issues

9 posts / 0 new
Last post
russellharris
Excel exchange issues

Hi there.

I've been trying to get Excel exchange to work but I'm running into the following issues:

1. My base data is in an excel 2013 macro-enabled spreadsheet ie *.xlsm, which is not recognised by the dialogue box. I can type in the full name, so I guess I can get around this point. Eventually (perhaps due to issues below, or perhaps because *.xlsm data files aren't supported - documentation?), I made it work by having a standard *.xlsx spreadsheet with values linked back to the *.xlsm spreadsheet. But this is bit of pain to manage data consistency; upon a change in the original input data, the *.xlsx needs to be opened & saved before the new data is available to SAM.

2. The documentation needs to be clearer regarding how to identify cells in a spreadsheet with multiple tabs. Even using the standard excel naming conventions for cells [eg '7. Cost database'!Q172], I couldn't get the exchange to work. It did work fine with named cells.

3. I am modelling a HCPV system, and specifically I'm using the excel data to update the cost information. I could not find a data exchange cost variable for Tracking.

4. Once I got the data exchange working, I found that it produced the correct results, but that the SAM Cost input page did not update to show the new values. IMHO this is highly confusing.

Any pointers?

Russell.

Paul Gilman

Dear Russell,

1. We haven't tested Excel Exchange with macro-enabled workbooks. We'll investigate that and, in a future version of SAM, allow for working with the .xslsm filename extension if it does not cause any unforseen problems.

2. You can only use cell references (A5, B7, etc.) to refer to cells in the first worksheet in the workbook.

3. That's a bug -- we neglected to configure the tracking equipment cost ($, $/Wdc, and $m2) variables for Excel Exchange, parametric simulations, and other special simulation options. We will fix that for the next version of SAM.

4. That's another problem. The SAM input variable should show the value from the Excel workbook. We'll fix that too.

Thank you for letting us know about these issues.

Best regards,
Paul.

russellharris

Thanks for your help Paul.

Russell

van.pham.nguyen

Paul,

How do I run a for loop feeding a batch of weather file names from an Excel exchange sheet to SAM? Is there a way to read in the weather file names one by one (assuming that I have downloaded all the referenced weather files to SAM library) by using an offset function? In VBA it would be coded something like weatherfile = cell(1,1).offset(0,i).value

Thanks,

-Van

van.pham.nguyen

Similarly, how do I output the kWh to Excel one by one on different rows and columns. I'm trying to batch run a flat plate PV system using multiple weather files at different tilts and azimuths.

Thanks,

-Van

Paul Gilman

Dear Van,

I would recommend using the LK scripting language rather than Excel Exchange to loop through the weather files and to export results data to a CSV file.

Please see the sample scripts #2 and #3 on the Sample Files page for examples of how to do that. For an introduction to LK, see the Macros and Scripting topic in Help, listed under References in the Help table of contents.

Best regards,
Paul.

van.pham.nguyen

Thanks, Paul for the link. I've also found a workaround solution to do looping in Excel.

-Van.

Clara SERVOIS

Hi !

I am trying to use an LK script code to exchange data between excel and SAM (the idea is to pre-size my PV system with excel, export the main outputs data to SAM and use the simulation to asses my system.

I have issue on importing the load profile on SAM. I want to import the load hour by hour, for each hour of the year, in the Energy Usage data.

For that, I created the following loop which seems to be okay when I simulates :

for( i=5; i<8766; i++) {

index='L'+i;
Energy_usage[i-5]= xl_get(xl,index);
outln(index);

}

L is the letter where the load are on excel, going from 5 to 8766
Energy_usage is how I named the data I want to extract from excel.

But after, I tried to link this Energy_usage to the load_user_data with this :

set('load_user_data',Energy_usage);

But the following error message appears :
"equation engine: [62]: reference to unassigned variable: count\

equation engine: [62]: failed to evaluate function call argument 1 to 'msgbox()'\

fail: [load,load_annual_total,annual_peak,energy_1,peak_1,energy_2,peak_2,energy_3,peak_3,energy_4,peak_4,energy_5,peak_5,energy_6,peak_6,energy_7,peak_7,energy_8,peak_8,energy_9,peak_9,energy_10,peak_10,energy_11,peak_11,energy_12,peak_12] = f( load_model,load_user_data,normalize_to_utility_bill,utility_bill_data,scale_factor )
no variables calculated in a single iteration, cannot make progress!
eqn not evaluated: [load,load_annual_total,annual_peak,energy_1,peak_1,energy_2,peak_2,energy_3,peak_3,energy_4,peak_4,energy_5,peak_5,energy_6,peak_6,energy_7,peak_7,energy_8,peak_8,energy_9,peak_9,energy_10,peak_10,energy_11,peak_11,energy_12,peak_12] = f( load_model,load_user_data,normalize_to_utility_bill,utility_bill_data,scale_factor)"

I understood that load_user_data is an array type of variable, so I tried to do the same for Energy_usage, but I really do not see how to move forward,

if someone have a clue on that, it would really help me !

Thanks,

Clara

Paul Gilman

Dear Clara,

I reposted your question in a new thread. Please see my reply here:

https://sam.nrel.gov/node/69897

Best regards,
Paul.

Theme by Danetsoft and Danang Probo Sayekti inspired by Maksimer