I originally bought VSTO so that I could write .net applications that utilised excel workbooks.
What I was originally after was a solution that:
- Collected data from a user using a windows form.
- Identified from the user input a workbook required to perform some calculations.
- Opened that workbook.
- Added the necessary data to the workbook and collected data from specifed result cells.
- Peformed iterative functions on data until an acceptable response was obtained from the workbook (for example design a component to withstand a given force, if the first set of dimensions aren't strong enough increase them and try again until a success is achieved).
I didn't use Visual Studio .Net 2003 because of all the problems with releasing Excel applications (look in task manager and they remain unless you manually garbage collect them or close the calling application).
I have a solution which has two projects, one window forms project (the main project) and one excel project. I can't figure out how to call and utilise the excel project from the windows forms project.
For example
public sub openExcel
dim myEX as new MyExcelProject
myEX.Open()
end sub
Or something of that nature.
Now, how does VSTO work with this
Is it the case that VSTO allows me to build .net code into an excel application but to interface with excel from windows forms I still need to use the excel com object as normal
FYI The reason I'm using Excel for calculations, etc is that it only needs excel training to update the programs so I can delegate this to other people and so that the program is adapatable if I leave as I'm the only one at the company with programming knowledge (as little as it is).
Any help would be gratefully appreciated (especially on the excel garbage collection issue)
Newbie understanding of Excel and VSTO
scorpion_anshul
Guy,
As you noticed, Visual Studio 2005 Tools for Office projects for Excel and Word are designed to work at the document level. From you have described, you have options:
1) You can create a WinForm application that presents the user with a form(s) that lets the user set some basic values and then launch the spreadsheet with the data values filled in. However, if you do this, you need to either keep the winform app alive to allow further modifications to the data that support the spreadsheet (let users increase force, dimensions, etc.). This is not a very good architecture for your application.
2) You can create and Actions Pane project for an Excel template. The user would open the spreadsheet, see some default presentation and data. Then, the user would see the Actions Pane and have the opportunity to change the data values. It has the flexibility of the winform application, but it is easier to build/debug, and it removes complexity. Pairing the customization with the template means that anyone who creates a new workbook based on that template can get the customization without any fuss.
3) You can create a COM add-in for Excel that would have a menu. You can add a menu to Excel, and users can launch your winform-based add-in given certain conditions. The user gets to work with the form to set initial data values and focus on the spreadsheet when they need to do so.
These are the main three concepts. I recommend #2 or #3. Option i#1 ntroduces more complexity than is needed, and it is not really desirable. The third option is architecturally sound, but you will need to add logic to make sure that the menu for your add-in is enabled/disabled only when circumstances deem it relevant.
Please let me know if you need more information about your overall architecture or specific issues.
HTH!
John.
H Katz
Thanks for the feedback.
I have noticed one issue that may affect what I want to do.
I can open an excel workbook from a windows form but the controls I've placed on the worksheets don't work. If I open the workbook by itself in the normal manner the controls work fine so something seems to be locking them.
What I have in mind overall is the following:
- Create some design sheets in excel using VSTO to create a template. The template will have an area with controls that will accept standard data, data that is consistant such as material properties and dimensions.
- Allow users to add calculations lower on the worksheet as they would do for any worksheet (i'll figure out a way of identifying wether the design is valid or not later).
- Interface with the workbooks through a windows form or manually through excel as normal.
This will allow the spreadsheets to exist and be maintained long after I'm gone (not that I'm thinking of going anywhere) and provide a good platform for the future.As an aside, using VBA you could always produce public functions that could be accessed by calculations in worksheet cells. I presume that this can still be done but can worksheet cell functions also access the properties of controls placed on the worksheet The old style controls used to link their values to cells, the .Net controls don't. If you can't easily access the properties then would I be able to create a public function that did access the property and returned its value (I presume you can see where I'm going with this).