Solved

Converting a spreadsheet to a standalone application

Posted on 2012-04-01
19
2,700 Views
Last Modified: 2012-04-08
Hi All

I have developed a very complex business simulation (over several years) using Excel with some VB macros. I would now like to develop this as a standalone application (without the Excel branding). I have tried recreating the simulation as a Visual Studio project but the programming task is enormous without the benefit of cells, formulas, sheets, etc. that Excel is so good at.

Does anyone know of a software product that has the cell and formula functionality of Excel but with the form design, compiling and deployment features of Visual studio.net?

Hope you can help.

Regards

Terry
0
Comment
Question by:Terrygordon
  • 8
  • 6
  • 2
  • +3
19 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 37793216
I would only have one question...

WHY?

If it works exactly as you want it to as an excel application, why would you now want to break away from what works well?
0
 

Author Comment

by:Terrygordon
ID: 37793627
The idea is to market the simulation commercially as a standalone application - not as an Excel workbook with all the limitations that this would place on it as a 'product' in its own right.
0
 
LVL 9

Expert Comment

by:experts1
ID: 37793692
You wil need to use Excel Object to convert and
develop the application under Visual Basic.

Requires fairly minimal editing of modules
to run under VB environment.

Are you familiar with programming in VB?
0
 

Author Comment

by:Terrygordon
ID: 37793745
I'm familiar with VB, but not the Excel object. Currently using Visual Studio 2008.
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 150 total points
ID: 37793785
Terry,
For a single project, you may find it much easier to purchase a package like Calc4Web that takes your Excel spreadsheet and VBA macros--and converts it into C++ code that you can compile into a .exe application. In so doing, you retain "the benefit of cells, formulas, sheets, etc. that Excel is so good at."
http://www.calc4web.com/calc4webover.htm   

A free trial version is available.

Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 37793850
If you want to use Calc4Web, you will likely need the Professional version. It's not cheap, but well worth it if you place a value on your time. There is a 30 day money back guarantee and a free trial version.
http://savvysoft.stores.yahoo.net/tupred.html      $2999 without maintenance
http://savvysoft.stores.yahoo.net/tupredwima.html     $3599 with maintenance for one year
0
 

Author Comment

by:Terrygordon
ID: 37793895
Hi Brad

It's getting pretty late over here (UK) so I will download the trial version tomorrow and have a look, before I award the points. Your solutions have always worked for me in the past, so I have no doubt that this one will too. Just have to construct another spreadsheet to figure out how many hours of my time adds up to $3,000!

Regards

Terry
0
 
LVL 9

Expert Comment

by:experts1
ID: 37794025
Hi Terry!

 byundt has submitted a great idea.

However, Visual Studio 2008 should have everything you need to
compile the standalone .exe application on the fly, without any
additional cost.

Should you wish to consider the Excel Object idea further
please confirm  and I will submit a sample code example, for
you to build on.
0
 

Author Comment

by:Terrygordon
ID: 37795269
The code example for the Excel Object idea would be great (sorry for the delay in replying - I'm on UK time).

Regards

Terry
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 50 total points
ID: 37795366
There is another well known and cheaper ($999) component

http://www.aspose.com/categories/.net-components/aspose.cells-for-.net/default.aspx
0
 

Author Comment

by:Terrygordon
ID: 37796440
Hi codecruiser

Unfortunately, the full license (for distributing the application to multiple users) is also $3,000 - the same as for Brad's suggestion.

Regards

Terry
0
 
LVL 9

Expert Comment

by:experts1
ID: 37797972
Hi Terry!

This is  more of a VS6 thing but idea should work in VS2008 (imo)
(1) Open Executable VB project
(2) In object browser check Excel application object to
include it in your project(or help to find exact method).
(3) in form module enter code as below:
Private Sub My_Legacy_App()
Command1.Caption = "WORKING.."
Dim xlmod As Excel.Application
Set xlmod = New Excel.Application
'Open target file
'get_trk = App.Path & "\" & myfile.xls
get_trk = "C\" & myfile.xls
xlmod.Workbooks.Open FileName:=get_trk

'ActiveWindow.Caption = "My killer App" 'change caption
xlmod.Caption = "Brazil"        'change caption



'Do whatever you need to do here....

' When done code below to quit

'This section simulates file save,
'closes the Excel application,
'release the xlmod object, pop-up
'"Done" message and finally close kbut application
 xlmod.ActiveWorkbook.Saved = True
 xlmod.Quit
Set xlmod = Nothing
End Sub

Open in new window


Just fiddle around with idea and good luck with that!
0
 
LVL 9

Expert Comment

by:experts1
ID: 37797987
Was rushing,

You will have to modify modules in original sheet
to refer to "xlmod" where necessary.
0
 

Author Comment

by:Terrygordon
ID: 37803213
Looks like the method is completely different in VS 2008. I will have a play and see what happens.
0
 
LVL 9

Expert Comment

by:experts1
ID: 37805252
Clients using your VB executable App, might simply need to
have MS Excel installed on their machine. Still much cheaper than
$3000.00.

Been a really long time since messing with that Excel object
and have not been doing too much with .NET framework, which
is too closely tied to Microsoft for me.
0
 
LVL 1

Assisted Solution

by:jdannemann
jdannemann earned 50 total points
ID: 37809139
More than a few solutions exist here. The easiest way to go about it uses the Microsoft Excel Object library, or pure COM object returned from createObject function, with the former described the following MSDN article (VB.net). http://support.microsoft.com/kb/301982

The Microsoft.Office.Interop library might also be of use, but either solution does not create a standalone application, per se, just automates Excel.

The third solution requires transforming the Excel VBA classes, modules, subroutines, and functions into VB.Net. Fortunately, this is somewhat easy because VB.Net is not so different. Most of the VBA syntax will likely work without conversion. A few pointers:

Wrap all Excel VBA classes in the following:

      Class MyClass
      End Class

      Module Module1
      End Module

2. use the Interop Library to call Excel or use a custom class for holding input data

3. If using Excel formulas, convert those into VB.Net functions

4. Display data using datagridview controls

5. Store and manage data using datatables, set this as the data source for your datagridview control.

6. Add handles to event-triggered procedures

Really  though, if you don't want a whole bunch of work, just automate Excel.
0
 

Author Comment

by:Terrygordon
ID: 37811917
Hi jdannemann

Some good ideas but automating Excel doesn't solve the basic problem of having a standalone application (the user still sees a spreadsheet that is clearly branded as Microsoft Excel).

I think I might be forced to go down the more expensive 'off the shelf software' route or just rebuild the whole thing as a VB application.

Regards

Terry
0
 
LVL 9

Accepted Solution

by:
experts1 earned 250 total points
ID: 37814094
Hi Terry!

Sorry, I overlooked the Excel branding execption
in your question.

In that case it is a tweek more like pulling out a tooth.

However, you could try the "MSFlexGrid" object in
your VB project to simulate your own custom Excel
like spreadsheet. But you would need to define all
the property details.

Just drag the "MSFlexGrid" object onto a form
and set dimensions and properties as you desire.
Lots of help available or used to be available on
configuring "MSFlexGrid" object as custom spreadsheet.

Don
0
 

Author Comment

by:Terrygordon
ID: 37821135
Hi All

I have decided to start from scratch in VS - looks like the only way I can get 'exactly' what I want. However, because you have all given valid comments I am dividing the points based on the amount of effort each expert has put in (seems like the fairest way). I hope this is acceptable and thanks again for your efforts.

Regards

Terry
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now