I do very little programming these days, but I try to maintain an application for our church that maintains most of it's bookkeeping and report generation. The application is all done in Excel/VBA, using many forms and several workbooks. Yes, we are using antiquated programs (XP/Office 2003) because we are getting what we need at a cost we can afford, and it works fine except for one function.
My thorn in the side has been the weekly donations. The data is entered by the Treasurer, and names are not uniform (despite all known means of encouragement).
When the treasurer attempts to make corrections, the VBA form seems to get easily confused if tab flow is not followed. Sometimes a control will get focus, but the tab reverts to making a tab instead of progressing to the next control. Then things get really messy. I'm on my fourth version of the form and have been unable to beat this issue.

Since the vb6 form is more robust, I am trying to write a VB6 exe that will handle data entry for this task. I have used Excel as my DB with vb6 several times and understand most of that aspect. My current problem is how to reference the "already open" workbook so that the exe may be started and stopped without opening new instances of the workbook.
I can't seem to find anything anywhere on this, so any suggestions would be appreciated.

Recommended Answers

All 2 Replies

The problem here is that a spreadsheet is not a database. Yes, both can be used to store information, but a spreadsheet is designed to manipulate that information, but a database, like Access, is designed to provide efficicient storage and retrival of the information. An Excel spreadsheet can only be open for one application at a time, unless you open it read-only for every instance.

If you want to connect to the running Excel application, a couple of mechanisms come to mind: Use an external application that can control the interface, such as AutoIt, to manage the activities of the Excel sheet. Another idea is to write a VBA routine in your Excel spreadsheet that uses network functions to listen for service requests from other applications and then acts on those requests; my problem with this last idea is that I do not know how to do that in VBA.

Thanks for the response. What I was trying to do has proven too problematic. It appears that my easiest solution is to allow errors in entry, then correct them when I do year end summaries, as I have done for past years.
Just for argument's sake, there was a time when an executable would use Excel when a moderate amount of data storage was needed, but using Access was a bit of overkill. Where I have a problem is trying to use Excel as a "shell" (if you would) instead of the other way around. What you say about Excel only open for one application at a time is probably the key, and the show stopper.
Thanks again.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.