EPANET Excel Add-In
A colleague of mine sent me an Excel Add-In which utilize the EPANET Toolkit functions. This Add-In is just the right tool for all Excel users who want to make custom EPANET simulations without writing a line of code, just use function in Excel cells. From the Add-In documentation:
The EPANET Excel Add-in is open-source software which operates within the Excel environment, for providing an interface for the 2.0 version build 2.00.12 of EPANET (a hydraulic and quality modeling software created by the US EPA). With Excel, the user can perform complex network analyses without programming. The goal of the Add-In is to serve as a framework for:
- Combining different sources of data using Excel capabilities (e.g. SCADA data).
- Manipulating data in Excel.
- Sending data to the EPANET Toolkit.
- From Excel, performing network simulations using the EPANET Toolkit with no programming.
- Getting results easily from the EPANET Toolkit to Excel.
- Analyzing and presenting results already in Excel.
Overview
The EPANET Excel Add-In features an easy way to use EPANET Toolkit functions in Excel. The Add-in functions are described in EPANET 2 Programmer’s Toolkit help. The functions are divided into 2 categories: “EPANET Traditional” includes all the functions of the toolkit and “EPANET Extended” includes functions based on the fundamental toolkit functions. (e.g. ENXLSetLinkDiameter).
Usage
- Traditional functions are prefixed by ENx (e.g ENxgetcount). Extended functions are prefixed by ENXL e.g. ENXLSetLinkDiameter. There is some overlap between the categories.
- Almost every function’s first parameter is the trigger. The trigger should be referenced to a cell computed prior to that function. Because Excel must know the order of computations, the trigger cell “tells” this to Excel. Please look at the examples for more information.
- Functions getting data from the toolkit always return one value:
- E.g. ENxgetlinkvalue returns a link value.
- The function ENxgetlinknodes is devided into 2 functions: ENxgetlinknodeto and ENxgetlinknodefrom.
- “Traditional” functions return “#N/A” if any error occurs. To display the EPANET error code, instead of “#N/A”, pass TRUE to the optional ReturnError parameter of the function.
- “Extended” functions return an error string (e.g. “Error 103” if an Epanet error occurs or some other string otherwise).
- Functions setting data to the toolkit or performing a task always return an error code. The error code is the Epanet error code value plus a random value between 0 and 0.5. If another error occurs the error code is 900 plus that random value. This has 2 reasons:
- The user can debug the functions with the help of error codes.
- Excel is forced to compute the dependent functions after this one, as the return value is used as a trigger in the dependent function.
- “Extended” Functions setting data to the toolkit propose the option to run a simulation immediately after setting a value. This option is useful when the user wants to set a series of values (e.g. pipe diameters) to the toolkit and get a result (e.g. node pressure) for each value.
- “Extended” functions include specific solutions such as the ENXLTraceSources function that performs source tracing for multiple sources and selected targets.
- The Add-in’s functions can be used with other functionality of Excel e.g. “goal seek”. See examples 3 and 6.
- Also the ENXLLinInterp function for simple linear interpolation is included. For example, it can be used to compute pool volumes if the pool level and the volume curve are available in Excel.
- Epanet parameter codes (e.g. EN_BASEDEMAND) are added as defined names in Excel. Using them the formulas are much easier to understand and maintain.
- Warning: Some functions, especially Enclose and ENXLOpen may crash Excel. Save your work regarding this warning.
Examples
Attached are a few examples. The path in the Excel sheet is the directory where the example networks (Net1, Net2 and Net3) are stored. Although the examples do not need the Add-in to operate, it is recommended to use the Add-in for new projects as a better practice (e.g. Add-in updates).
- Parametric test: This example demonstrates the opening of an Inp file, how to pass a series of diameters to the Toolkit and getting a pressure value corresponding to each diameter.
- Online SCADA net solver : In this example the connection to the SCADA system is represented by a simple sql query. For every data change a snap shot simulation is performed. The user may change the data to check a What-If scenario.
- Goal seek: Excel functionality can be used to find answers to simple questions automatically e.g. what diameter should be placed for keeping the required pressure at a remote node.
- Get sources of each node: The purpose of this example is to illustrate the use of the ENXLTraceSources Extended function. This function performs multiple source tracing runs, one for each chosen source. This way the source mixture for desired nodes can be found.
- Compute pump working points: This example demonstrates how to run a 24 hour hydraulic simulation and get data of interest for every hour. “Extended” functions are used.
- Meeting a minimum chlorine residual target: This example demonstrates how to run a 24 hour quality simulation and get data of interest for every 5 minutes period (Cl residual value). “Traditional” and “Extended” functions are used.
- Compute pump combination data in a pumping station: The example demonstrates the computation of flow and head of pump combinations in a pumping station. The user is expected to supply the inp file and make the appropriate small changes in the worksheet to suit a specific pumping station.
The Excel Add-In is distributed under the MIT license. Please report any bugs, issues and fixes to EpanetXL at gmail.com. If you use this software you may also use this email address to send PayPal donations.
Download EPANET Excel Add-In (ZIP, ~1MB)
It says
Compile Error:
The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.
and it points to this line:
Declare Function ENepanet Lib “epanet2.dll” (ByVal f1 As String, ByVal f2 As String, ByVal f3 As String, ByVal F4 As Any) As Long
What version of Excel and Windows are you running?
I SEARCH EPANET For MAC, Can you help me
thank you
Try here:
http://www.water-simulation.com/wsp/2011/08/13/epanet-on-mac-os-x/
Thank you for posting this Add-On it appears very useful.
Where could I find a list of functions for this? Are they the same as the toolkit?
I am attempting to simply run a simulation and return the head at all of the nodes in the network. If I can figure it out, I’d also like to change base demand at all nodes, rerun the simulation and again return the head at all of the nodes.
Thank you
Thank you for posting this Add-On it appears very useful.
Where could I find a list of functions for this? Are they the same as the toolkit?
I am attempting to simply run a simulation and return the head at all of the nodes in the network. If I can figure it out, I’d also like to change base demand at all nodes, rerun the simulation and again return the head at all of the nodes.
Thank you
Specs: Win7, Excel2010
Hi Chris,
The EpanetXL functions are defined as any other Excel functions and you can find them in the same manner:
-Open one of the examples.
-Select an empty cell.
-Press the fx button left to function editing line near the top of the Excel window (where you edit your functions).
-Press the “Choose category” combo box.
-Select the “Epanet traditional” or the “Epanet extended” category.
Epanet traditional category contains all the functions of the toolkit while Epanet extended category has some more function. There is some overlapping between the categories.
HTH,
EpanetXL
Is all sheets are password protected ? it doesnot allow to edit any cell. is this protected?
I also have a problem running the macros. Is says Compile error: The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute.
Is there a way to fix this? Please help.
Hi mike,
The 64-bit version of Office has some limitations, one of them is that VBA code that uses Declare statements must be updated, as described here:
64-Bit Visual Basic for Applications Overview
https://msdn.microsoft.com/en-us/library/gg264421.aspx
HTH,
EpanetXL
Hello Mr. Elad Salomons. I am Water Supply engineer and just started designing using Epanet. I downloaded this Add Inn but couldn’t get started with it. Can you please send me any note how to start/use this add inn ( as Beginner ) . Thank you ..
Hello Mr. EpanetXL . I am Water Supply engineer and just started designing using Epanet. I downloaded this Add Inn but couldn’t get started with it. Can you please send me any note how to start/use this add inn ( as Beginner ) . Thank you ..
Hi Bipul,
1. I would recommend understanding the EPANET Net1.net example without using the add-in.
2. Then try opening the (first) EPANET-Excel examples and see what functions are used and how they are used. The examples do not use the add-in. All the EPANET-Excel functions are included in VBA modules within the workbook, nothing is locked. You might want to check the Excel security settings.
3. Use the add-in only if needed. You may use all the functions of EPANET-Excel the way they are used in the examples. You may delete the worksheet of an example and start your own project.
HTH,
EpanetXL
Hello,
I would like to ask where can I find Epanet Calibrator.Thank you in advance.
What do you mean by “Epanet Calibrator”?
Hi,
i’m using Windows 8.1 64 bit and office 2010. I can’t run examples. I have changed all “Declare” to “Declare PtrSafe” so there is no compile errors. Excel function ENXLOpen(B2;B3) (example1.xls) returns #VALUE error.
I’ve tried debugging but vb macro won’t go past line:
ENError = ENopen(FullFilePath & “\” & FileName, “”, FullFilePath & “\” & fname & “.bin”) it just stops there.
Any information about fixing it will be helpful.
Greetings,
Artur
Hi Artur,
The EpanetXL was was not written in compatibility with the 64 bit version of Office.
As you mentioned some code modification are needed but also the 64 bit version might be needed.
I hope these 2 links might help:
http://support.microsoft.com/kb/983043
http://epanet.de/developer/64bit.html.en
Regards,
EpanetXL
EpanetXL thank You :). After using 64 bit dll from http://epanet.de/developer/64bit.html.en
it worked.
In order to run EPANET for Excel examples on 64 bit Windows:
1. 64 bit dll is needed (e.g. from link above). On Windows 8.1. I have copied epanet2.dll (64 bit version) file to c:\Windows\System\
2. Replace all “Declare” to “Declare PtrSafe” in Module1.
Dear all,
I am trying to use the toolkit on Windows7 64-bit running Excel 32-bit. I have copied a 32-bit epanet2.dll to C:\Windows\System32\ and left the Declare statements untouched. I receive a #VALUE error. I tried removing the 32-bit dll using a 64-bit one including changing Declare statement to PtrSafe as explained above. All in vain. Does anybody have any idea?
Best wishes
Sebastian
I’m using 64 bit version of Excel and it works.
hello
i am employee in Municipality of a big city in iran.i am mechanical supervisor of the big linear park and general building and other project
if it is possible, i want from you please email me the free source of necessary saoftware to help me to work better
thanks a lot
HI, i am Jumko, want to subscribe your EpanetXL, hope you will share.
Dear Mr. Salomon,
I am working with the Epanet Excel Add in and everything works in the provided examples but as soon as I try to switch to my own made Epanet models, I am told that there is “Not enough nodes in the Network”, which refers to the error 223. Do you have any idea about this problem ?
Thank you
Hi Alexandre,
Does the network run within the EPANET environment?
Regards
Hello,
Yes in fact they work without any running calculation problems.
Thank you very much
Hi Alexandre,
Is it possible to send the network to epanetxl@gmail.com?
I’ll try to solve the problem.
Regards
Hello,
I sent you my models, I hope you will be able to figure out what is going wrong.
Thank you very much for your time,
Regards
Alexandre
Hi,
It seems that the network should have at least 2 pools. (The toolkit is a little different then the EPANET environment).
Regards,
EpanetXL
Hello!
Can you please explain the syntax of some of “traditional” functions. For example, I wish to retrieve the type of nodes in my network i.e. whether they are junctions, reservoirs or tanks. I think ENgetnodetype is proper function for this task, but its arguments are index and typecode, which I don’t understand. Isn’t typecode exaclty what I want to retrieve? How can it be an argument then?
Or, another function which arguments I don’t understand: ENgetnodevalue has three arguments: index, paramcode and value. What the argument value stands for?
Thanx,
Sandro
Dear Sandro,
The “traditional functions” are not the toolkit functions. The “traditional functions” are the add-in functions that wrap the toolkit functions in a simple way.
e.g. The toolkit function ENGetNodeType is not used directly by the user. It is wrapped by the “traditional function” enXgetnodetype that is defined as a VBA function and is available to an Excel user.
The VBA function is defined is the following way:
Public Function ENxgetnodetype(trigger, index, Optional ReturnError As Boolean = False) As Variant
Regards,
EpanetXL
Hallo, Thank you for this
am currently using excel 2013 but it doesn’t link me to epanet. what can i do
kind regards
Dear Ivan,
Are you using a 64-bit version of EXCEL?
If so, the following changes should be done:
1. In the VBA module 1 of the add-in (or the examples) the “Declare Function” command should be replaced by “Declare PtrSafe Function”
2. The file epanet2.dll in the EPANET Excel Add-In folder should be replaced by the 64 bit version found here:
http://epanet.de/download/epanet_toolkit_64bit_mingw.zip
Regards,
EpanetXL
Hello
i’m a hydraulic engineering student and i was wondering if you can help me with some information in order to understand how the epanet excel add-in really works. How it can simplify the job ? How does it work ? Its limitations ?
A simple discription would be really helpfull.
Thank you