PDA

View Full Version : Free Standing Alternatives to Excel Database



streub
01-01-2014, 07:12 AM
I have developed an Excel application using VBA to track store sales performance. Unfortunately, the corporation has now opted to use OpenOffice.Calc. I have far too many hours devoted to the Excel project to now re-write for OpenOffice. What alternatives do I have for developing a free standing, VBA based, program? Something that will run on any Windows based system but not dependent on Microsoft Office applications?

Bob Phillips
01-01-2014, 07:29 AM
Depends upon how much of Excel's native functionality that you are using, I would guess it would be a lot, which would mean that you would have to develop that functionality yourself, or link to some other app/library that will provide it. If it is a decent sized app, I think you have a big job on.

streub
01-01-2014, 09:20 AM
That is not what I want to hear but believe it is/was inevitable.

Happy New Year!

SamT
01-01-2014, 10:26 AM
Did you enjoy learning VBA? You might enjoy learning OOo programming.

Alternatively, I hear that it is easy to port VBA to VB.net, but you will still have to learn how to access OOo.Calc.

OOo can import XML. and newer versions of Excel can export its results to XML. I believe that your MS Office license is perpetual so you don't have to stop using Excel as your engine.

If your company is moving to a Database... I have heard that the free version of MySQL is suitable for enterprises up to the level of complexity of state and federal Revenue services.

In almost any case, you have the opportunity/onus of learning a new language.:devil2:

On the other hand, a freelance coder who knows both VBA and OOo can easily port most of Excel over, and fill in the blanks for you. It would not be that expensive.

streub
01-01-2014, 12:41 PM
Thanks for the reply Sam. It was certainly more encouraging than what I have been picking up on the web. The term "porting" is new to me so please define in layman's terms. The OOo option is there but I am having difficulty accessing the modules. I have been able to move the data and userforms from Excel to OOo but that is all and can't seem to get to where I need to go to adapt or change the code.

I have been exploring the SQL options but don't have a grasp of when, where and how for my purposes. I have downloaded Visual Basic Express 2013 and have toyed with it on a very limited basis. I have also toyed with SQL but not sure how to integrate it with VBE or anything else that matters. Does the SQL database travel with the application or must each computer have it?

The company could not care less as to what I use for a vehicle so the database idea was mine.

westconn1
01-01-2014, 01:21 PM
The term "porting" is new to me so please define in layman's terms.modify existing app to some other code language, or to run on different operating system or enviroment


Does the SQL database travel with the application or must each computer have it?depends on application installer (programmers choice), or would you want a single database accessed by many?

even an access database (access not required to be installed to us access database), would be superior to excel, but the sql lite is probably better

SamT
01-01-2014, 01:35 PM
Porting Code = rewriting code to work in new language. Usually uses the same upper level algorithms, which are the hardest part of code to create.

A *.csv is a Database Table and both Excel and OOo can use them. I think the MS Jet engine is what applies.

I recently saw an article on VB.net at I Programmer - programming, reviews and projects (http://www.i-programmer.info/) that was pretty good for newbies. Use their Search.

Free Databases (DB Engines): Start with Oracle, MySQL and Access. One of those will work for you and there is tons of stuff for them on the web.

A database consists of a set of Tables, in various file formats. They are accessed by one or more Database Engines of various manufacturers. The DB Engine interprets a Structured Query Language.

A *.csv is the most basic and cumbersome of DBs, Excel is slightly better, and you know that it works for you fairly well.

I am guessing that you have been using Excel Workbooks as your Tables and Engine and VBA as your "SQL". I suggest that you chose a database engine, then open a new thread asking foe help in Porting your existing Excel Tables into it.

Then ask for help in accessing it with Excel, "porting" your currrent VBA "SQL" queries (Macros) into one of the MS DB Engines' SQL.

Only then would I start really thinking about using OOo to access the Database Tables via an OOo suitable SQL Engine.

Remember many DB Engines (SQL interpreters) can work with many DB file formats.

Bob Phillips
01-01-2014, 03:42 PM
Porting the code is not the issue, or more accurately is probably not the main issue, Excel functionality is. If the guy has written an app in Excel, to me it seems safe to assume that he is using Excel functionality. For instance, if he were to be sorting his data he might drop it onto a worksheet and use excel's built-in sort. That is just a couple of lines of code, but without Excel he would have to get his data into an array, and then call a sort routine - sure there are plenty around, but a good 2D sort will take effort and time to build into your new app. At the very least, I would assume that he is using the Excel grid, and maybe formulae in that grid, replicating that functionality in another tool is not going to be trivial. I have tried to port Excel apps to VB and VB.Net, and it was not an easy task. I have also tried OpenOffice, and whilst it may be better now, it was darned hard, I gave up as I didn't have the time or patience to persevere with it.

SamT
01-01-2014, 05:03 PM
Pessimist :nya:

Bob Phillips
01-02-2014, 03:14 AM
Pessimist :nya:

No, been there, got the t-shirt and the headaches :banghead:

Not saying that you shouldn't do it, it is unwise to assume that you can stay on the same technology forever, you should regularly review your applications/architecture/technology choices to ensure that you are happy that they are the best fit for the overall need, but I am cautioning against underestimating the task. Selling a job as easy when it is not is the surest way to make it fail, get it poorly resourced/funded, and the quickest way to lose credibility with your management, which damages everything you try to do thereafter. Under-selling complexity is the MS way, and it is the wrong way IMV.

SamT
01-02-2014, 08:46 AM
True, but I don't think that we are selling anything to the OP, merely pointing out domains to investigate so he can discover the difficulties in the job.

I do think that he will have to rebuild his entire system, but that he can recycle some parts of the existing. However, each bit of "recycling" will need expertise in a new and different programming domain.

Imaginative1
01-03-2014, 02:40 PM
Another possibility is [re]writing your app in JavaScript, but heavily use Excel functions in your browser via ActiveX. That way, you could still learn Excel AND port to OpenOffice.Calc AND move to HTML5 - all at the same time. Mastering HTML5 could help you develop on the web, for Apple, and Android apps too.

Bob Phillips
01-03-2014, 05:34 PM
Another possibility is [re]writing your app in JavaScript, but heavily use Excel functions in your browser via ActiveX. That way, you could still learn Excel AND port to OpenOffice.Calc AND move to HTML5 - all at the same time. Mastering HTML5 could help you develop on the web, for Apple, and Android apps too.

Do you want to post an example of such a beast, so we can all be enlightened?

NoSparks
02-18-2014, 06:01 PM
Hello Streub,

Realizing it's a few weeks since you started this thread, and finding myself in a similar situation, I'm wondering if you ever managed to arrived at an acceptable solution or have any "words of wisdom" for others?

Thanks for any comments.