Integrating Userform and Excel in ArcGIS

Question

Hi,

I am a novice to ArcGIS and VBA. I want to develop a userform in ArcGIS. Using this userform, I want to undertake some energy related calculations. Currently I have an excel sheet which has the necessary formulas.

Now, I want the userform to pass on the values to this excel sheet, do the calculations and return the final results back to ArcGIS.

Can anyone please tell me how to pass values from ArcGIS to excel sheet, undertake calculations?

Many thanks in advance,

Amit

MatthiasAbele · Answer

Dear Amit,

there is a perfect solution for you! With the GISconnector for Excel you can work as you desire. You can do all your calculations in Excel and then synchronize with ArcIGS: http://www.gis-connector.com.

It is much faster than programming with VBA.

Best,

Matthias

AmitMhalas · Answer

What you're asking about is how to "automate" Excel.  That's too big a topic for a discussion thread but Microsoft has plenty of documentation on the subject if you care to read it.  Basically what you will need to do is open Excel, load the document, plug your values into the appropriate cells and read the results from wherever the worksheet puts them.  It would probably be easier, not to mention cleaner, to simply code the functions in VBA.

Thanks for your response. I could write the functions in VBA, but the equations are too many and too complicated. I have attached the excel sheet. The coloured cells need to be filled to get final result. Now what I wish to do is based on the map displayed on ArcGIS, fill the userform, pass the information into these coloured cells and take the final result and display in ArcGIS. I wish to undertake this for say several polygons (houses) on a map and make an energy map.

Sorry, I dont know if I am making it clear or confusing.

Energy.xlsx

NeilClemmons_GISinc · Answer

What you're asking about is how to "automate" Excel.  That's too big a topic for a discussion thread but Microsoft has plenty of documentation on the subject if you care to read it.  Basically what you will need to do is open Excel, load the document, plug your values into the appropriate cells and read the results from wherever the worksheet puts them.  It would probably be easier, not to mention cleaner, to simply code the functions in VBA.