Solved

Converting a spreadsheet to a standalone application

Posted on 2012-04-01
19
2,420 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 80

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 80

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

762 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

22 Experts available now in Live!

Get 1:1 Help Now