I am trying to use an Add-in to post process SWMM 5 data in Excel.
I set up the add-in as described in the Help but when I execute it Excel 2007 opens and I get a message that :
"SwmF486.tmp cannot be accessed. The file may be corrupted, located on a server that is not responding, or read only."
Any help will be appreciated.
Thanks
John
Replies
Hello,
Sorry, I am sure this is a simple problem, but I am trying to use the Excel add-on, I set it up as described but when I try to change data in the spreadsheet, and then close and save, I get errors from SWMM such as:
Too few items at line 55:
[JUNCTION]
"; Invert Calculated; Tee Connection, Assume Bolted.
and when I try to run I get many errors such as this:
ERROR 209: undefined object FLOW at line 262 of [INFLOW] section:
818-1039 FLOW FLOW FLOW 1.0 1.0
It appears that when the excel sheet is opened, it does not contain the correct info. For example, before using the tool my inflows look like this:
[INFLOWS]
;; Param Units Scale Baseline Baseline
;;Node Parameter Time Series Type Factor Factor Value Pattern
;;-------------- ---------------- ---------------- -------- -------- -------- -------- --------
818-1039 FLOW "" FLOW 1.0 1.000000
818-1518 FLOW "" FLOW 1.0 1.000000
but after they look like this:
How can I force excel to read the file correctly? It is the 2003 version if that might matter...
Many thanks
Ross
I just tried this again and it worked fine for me, however I save my altered model as a tab text file in excel rather than coming directly back to SWMM 5
I was able to successfully use the excel add-on using Excel 2007, but duplicating the steps I took produced errors when trying to use Excel 2003 :-/
There clearly seems to be a difference in the format between what 2003 and 2007 imports, so hopefully I can figure out how to force 2003 to behave like 2007 since that is all I have available on the work PC.
2003:
2007:
In the newest version of EPA SWMM (5.0.1.11), there is a new feature of allowing for Add-ins and third-party tools. One such Add-in, the Microsoft Excel, can be very helpful for input data editing and model calibration.
1. To activate the Add-in
This process is detailed in pp. 141 of the EPA SWMM manual (http://www.epa.gov/ednnrmrl/models/swmm ... manual.pdf). Basically the user needs to go to "Tools->Program Preferences->Configure Tools" on SWMM main menu. Then in the pop-up "Tool Options" menu choose "Add." A "Tool Properties" window will pop-up, and the user can assign a name to the Excel Add-in for the "Name" field. For the "Program" field, the user needs to navigate to the location of the Excel executable file at "C:\Program Files\Microsoft Office\Office10\Excel.exe" (the file path may vary). Leave the "Working Directory" field as blank, and choose "INPFILE" macro for the "Parameters" field. Check both "Disable SWMM while executing" and "Update SWMM after closing."
After the above is set up, click OK and the Excel Add-in is registered in SWMM5. The Add-in tool is under the "Tools" menu. One important thing now is to go to "Tools->Program Preferences," and in the pop-up window check "Tab Delimited Project File."
2. Use the Excel Add-in
The SWMM5 input file by default is a tab-delimited .txt file. The user can view the file using Wordpad, but the editing is not very convenient, especially when it comes to calibration for a watershed with large number of subbasins. The Excel Add-in provides great relief for such operations.
Create a simple watershed model in SWMM, and then go to "Tools->Excel Editor (or whatever the user names the Add-in)." The input file for the watershed model is then displayed in tab-delimited format in Excel. In this environment, the user can edit the input data much easier (as compared to double-click each model component and key in the values in the Graphic User Interface). This becomes more apparent when the number of subbasins increases. When the editing is finished, close the Excel program, and then click "YES" or "OK" to all the pop-up windows. After that, the SWMM model interface pops back and the input parameters are updated.
So with this knowledge the model setup process can be much easier. In the initial model setup, the user may not bother to input any parameter values (i.e. subbasin area, width, slope, etc.). Instead, the model can be delineated and all components represented. Then the user can open the "Excel Editor" and copy/paste the model parameter values from another table of pre-created input parameter values (which is always the case). This process will totally by-pass the manually key-in of parameter values.
The second case of this feature applies is the model calibration. In a traditional way, suppose the user needs to change the value of depression storage for the impervious area. That means for a 30-subbasin watershed, the user needs to roam around the watershed and double-click 30 times to finish that single parameter change. Imagine if it takes five times to find the best value for that single parameter. With this feature, the user can open up the input file, set a depression storage value for the first subbasin, and then drag down for all the other 29 subbasins. Close Excel and go back to SWMM, and the updated model can be ran immediately.
Source: http://ceeforums.com/forum/viewtopic.php?f=26&t=256&p=582#p582
That also works well, you can highlight a section of the output file in the browser and then copy it to the clipboard and ultimately Excel
Well I had a look at what is in the temp file and decided that it would be best to copy and paste table data into excel instead.
Cheers
John