Microsoft Excel Based Layout Control Software

How does this communicate with the layout?  Is this a GUI for eTCC?

It's a "GUI" using objects and VBA code in Excel.  It communicates with the layout via the serial port to the command base.  It's just Excel makes creating the GUI much easier than building it all out from scratch using vb.net or other language.  The main thing is the VBA code to do the communication.  It is stand alone, and doesn't need eTCC or anything other than Excel to run.

OK, I get an error about some image that's missing.

How do you configure what COM port to use?

Good point on the com port!  I hard coded it and forgot then to put it in the setup.  Will do that and post a revision.

The missing picture is my trackplan.  When I added the feature for people to upload their own, it doesn't save it with Excel it seems, it needs it in the location where the Excel file is saved.  I did not know that until I tried to figure out why you had that error.  My track plan pic is attached here, and I'll edit the original post with it added as well.  John, thanks for giving this a try, I really appreciate the feedback!!

Burl

The version attached to this post has a place on the "Engine Setup" section to select a COM port.  If one doesn't work, just try another.  I'll try to get a better way to confirm function in time, but I'm not sure of a good way at the moment. Please keep the feedback coming.

What a great branch of the hobby!!!

Here's a new version with these changes:

- Swapped out the slider throttle control for just a "speed" indicator as I couldn't get it to work reliably.
- Engine controls now sync the speeds.  When you adjust one, it adjusts across them all.
- Exposing the "Block Power" controls, which are still under development.
- Exposing the Accessory controls which work with an ASC2.
- Switched to .xlsb file format to save a little on file size.
- The trackplan picture now saves as part of the Excel file, no need to have it saved separately.

All feedback and questions are welcomed!

The version attached here has these improvements:

- Functioning block controls
- Improved throttle operation
- Fixed bug that locked up program after around 50 commands

I'm anxious for folks to give it a try and let me know how it works.  The more folks that use it, the more robust it will be.  Next is making a go at train detection, either with RFID or current sensors.  I expect the latter as the programming I think will be simpler and won't require any modification to engines and cars.

All feedback and thoughts are welcome!

Great work. I used to do some VBA programming for Access applications (have forgotten a bunch of it). Now that I'm retired I think it's time to dust off my Inventory program and put some code behind it instead of macros. Thanks for the inspiration.

I tried opening it in Apache Open Office ver. 4.1.5  (it reads/writes to Excel) Win 8.1 but the screen is quite wonky.

Dave,

I expect this will require a desktop/laptop installed version of Excel.  While other tools like Apache, Google Docs, and even Office 365 run from the cloud do alot of what Excel offers, when you get into things like this (lots of VBA behind the scenes and using graphics) most of them won't cooperate.

The version attached here has now been well tested and bugs fixed.  The train detection works with serial ports and relays, and the BPC block control works well now too.  If anyone is interested in giving it a try, just drop me a note.  Some features are easy to customize, others require some coding.  I'd be glad to help anyone wanting to set it up.

This is what I see if I open the file in Open Office Calc on a Windoze 7 Pro Desktop:

I use Open Office exclusively and do not use any Microsoft Products other than Windoze.

Rich, I'm sorry it wouldn't work for you.  I use Excel 2016 installed on my desktop.  I expect this will require a desktop/laptop installed version of Excel, but older versions back to 2007 I think will work.

The latest update below now works with the LCS WiFi module to remotely program sensor tracks.  I've also leveraged the train detection to drive automation of switches, whistles, bells, and accessories. To use the WiFi feature, it requires installing a reference to Ostrosoft Winsock dll.  Note this will only work with a desktop/laptop version of Excel, not cloud based versions nor free "reader" type versions.  If anyone would like more information, just drop me a note.  I'm glad to share with anyone interested.

The excel nerd in me loves this, I can't wait to dig in to this!

@Burl, could this file be used to give someone on the internet control of the layout? I guess I was specifically thinking of dropping an internet connection between the sheet and the layout, rather than just sharing control of my screen with them like TeamViewer. It would be cool to let my friends out of state drive trains with me in the room driving my own locomotive.

I know there would likely be security risks involved but would it be possible?

I think it's possible.  It would require a major rewrite of this program to leverage the WiFi module instead of the serial connection (the only thing on WiFi now are the sensor tracks in this program).  I use the WiFi module in WPS mode, which links it via your wireless router and gives it an IP address (v. using Access Point mode which is just between it and local devices).  If you use WPS and capture the IP address with your router's config webpage, then I think anyone could do it using the current LCS app.  I think you could use eTCC as I've described as well if you don't want to go the iPad route (just no GUI).  Does that make sense?  I do think TeamViewer would be an easier path though.

You lost me at Access Point so I would say a TeamViewer-style path would indeed be the way to go. Thanks! I'm not anywhere near being able to use this but I was curious for future ambitions. It would be a decent way to solve the issue of most of my friends and family moving away.

I  think I'm closed to finished on this, I can't think of anything more I want to do with automation given the size of my layout (too small for real train detection driven automatic operations).  The attached version has sensor track reading capability for engine number, name, water and fuel levels.  If anyone has interest in the details, please just let me know.

BillYo, I also figured out a little more of the WiFi, so your desire of remote control is now alot easier than I thought.  You could use this for remote control via the internet, but a few of the features may not be available.  I'd need to add/rewrite some code, but not as much as I first thought.

Here is a short "demo" of the main functionality.