Generating Gcode via Excel: Tool: Multi-element Instrument Panel Gcode in One Quick Pass

[B2]

Please let me know what you think of this and if you try it out! Good or bad? Useful or not? A waste of my time? I look forward to hearing your suggestions, improvements and especially any questions. It would be nice to hear if others have done similar work.

Here I am sharing my efforts to automate some Gcode generation via the use of a Excel Workbook (multiple spread sheets). A quick Web search did not provide much useful to me about how others might do this. Anyway, I always wanted to know more about Excel Macros so I used it as an excuse to teach myself "some" MS Visual Basics for Applications.

(Technical Disclosure: My PC is set up with Microsoft Win 7-64bit, and I run Office 2010. Also my CNC mill runs from Mach3 so the Gcode is written for Mach3. However, the Gcode functions are pretty simple and so it will probably run on other CNC platforms. The Excel Workbook uses Macros, so even if you are afraid of malware in them, you can see what the program is doing without using them. Maybe I will even post one version of the workbook where they have been removed into a separate text file.)

Back Ground: Many folks use a CAD package to draw their work and then rely on the program to generate Gcode. When the results are generated the code is not very intuitive and depending upon the work complexity can be quite long. However, if you want to change something about the device it commonly requires a lot of time to do make the changes. This becomes more tedious the more complex the drawing. This is probably true even for planar objects such as cutting various holes in a sheet, like that of an instrument panel

I have been writing Gcode for things like this "by hand" for a while and recently made an instrument panel for my VFD converted PM1440GT lathe. (It is always a learning experience.) Then I found that I had wished I had laid out the instrument differently with switches, displays, lights in different locations. So I did it over! Then I decided I would actually like to add a digital counter display and switch to the panel and was faced with repeating the work again. The Thread on my conversion and the instrument panel installed in the lathe is HERE https://www.hobby-machinist.com/thr...tronic-components-pm1440gt-vfd-3-phase.95058/:

VFD conversion using solid state electronic components.

Later, I will post more about my Gcode generated Instrument Panel there.

What is in the Posting?: So this time I wrote a program in Excel to generate the Gcode. Excel inputs include the device style (switch, display, light, etc.) size, location, orientation, and features like detailed shape features and cutting tabs to prevent tear-out during the machining. In addition, things like tool diameter, i.e. tool offset, as well as multi-path cuts to adjust the depth of the feed requires calculations. The final depth of the cut should coincide with the bottom of the sheet to prevent excessive cutting into a backer board. As long as one is doing those calculations you might as well add in the feature that the plate or backer board is not level or flat. Depending upon the device shape detail this required math calculations to determine the points along tool path where the Gcode needs to change.

So I have written an Excel workbook which will automatically generate the Gcode for you, if you are willing to invest just a little bit of time to input data. It generates Gcode for any number of circles and rectangles at user specified locations, sizes, features etc. It does this by using templates for a shape where the user inputs parameters into a table and then executes macro(s) that generates the code. There are is a Circle and a Rectangle Template spread sheet so far, but there can be others that a user might decide to build. They are each a template from which other sheets of similar styled devices are generated via substituting feature parameters, so the instrument panel can have lots of different featured device holes.

Realizing, that using even a spread sheet to compose Gcode maybe a new approach, lot to comprehend, for some folks (It was for me!) I will post two entries. The first posting will be of two individual spreadsheet programs which composes the Gcode for either a single rectangular or singular circular device with various edge features. The second posting will be of the multi-spread sheet workbook where Gcode from the individual device templates can be used to compile a large set of devices into a single set of Gcode.

This first posting of the Rectangle (uwRectT) and Circle (uwCirT) templates has specific device parameters (which you can change) already written into the spread sheets. This will allow one to perhaps understand how my Gcode is constructed. There is a uwReadme sheet in the spread sheets which I started to draft for the overall Workbook. If you read the portion under these template names it may provide insight into what the individual spreadsheets actually do. However, if you want to get a general idea of the individual spread sheet functions you can just copy and paste a bit from these two template sheets into a Gcode simulator or you CNC tool and see the tool path. You do not have to run the program with macros to do this. Copy the column from the RED cell (~row 197 column on the right) containing "(G-code-- copy and paste into Simulator i.e. Mill Wizard)" to the bottom of this column where the cell contains "%****************". Then paste!

Five files are available here, but the SingleRectHole may be easier to understand as it has fewer features:
1) SingleRectHole LC26_1100.xlsm Zip This contains the simple Macro for copying the Gcode. "Cntrl+Shift+O" will invoke the macro.
2) SingleRectHole LC26_1100.xlsx This has NO Macros.
3) SingleCircleHole LC26_1100.xlsm Zip This contains the simple Macro for copying the Gcode.
4) SingleCircleHole LC26_1100.xlsx This has NO Macros.
5) Module6.bas Zip This is the Macro as a text file. If you change the .bas to .txt you will be able to open it.

HM did not accept .xlsm files nor .bas files so I had to zip these.

The next Post will contain and provide a bit more detail about the MAIN program.

 

[B2]

This is the second half of the post of the Excel Gcode generator. Perhaps you will find it useful.

To see the program in operation load the first spread sheet and then with Macros enabled simply press the keys Ctrl+Shift+M. It should generate Gcode and copy it to the Windows Clipboard. One can then paste it into a machine Gcode simulator or into ones CNC machine to see the tool path.

To understand the details of this Excel Workbook program see the "uwReadMe" sheet contained in the Workbook. The program generates Gcode for an array of devices. I used this to construct the Gcode for instrument panels containing circular or rectangular holes of various sizes, locations, rotational angles, and features such as hangers, Keyways, Flats, etc. It is based upon Templates of a given general shape where the features can be implimented or not. The Template approach allows the user to add future generalized shapes to the over all program.

The files contained here are:

1) PanelCreateAllSheets_Gcode uw-T 3Templates LC20_2100 .xlsm ZIP This contains 7 Macros.
2) PanelCreateAllSheets_Gcode uw-T 3Templates LC20_2100 .xlsx This contains NO Macros.
3) Module1.bas Macro as text file: "DeleteExcessGDrillRows" Ctrl+Shift+D Creates Gcode for drilling all devices and deletes all comment lines
4) Module2.bas Macro as text file: "PanelCreateAllSheets_Gcode" Ctrl+Shift+M Main Gcode generator.
5) Module4.bas Macro as text file: "DeleteExcessGShortRows" Ctrl+Shift+S Creates Gcode with SHORT comments
6) Module5.bas Macro as text file: "DeleteExcessGLongRows" Ctrl+Shift+L Creates Gcode with LONG comments
7) Module6.bas Macro as text file: "CopyOneDeviceGcodeAtCurrentSheet" Ctrl+Shift+O Copy Gcode from ONE Device Sheet
8) Module7.bas Macro as text file: "FindLastRow_Column" Ctrl+Shift+R Finds the ROW and COLUMN # of the last row and column
9) Module8.bas Macro as text file: "ClearSheetsExcept" Ctrl+Shift+C Clears (deletes) unwanted Sheets, no front "uw" or ending "T"

A Module3.bas does not exist.

I had to zip the macros and the Excel file containing the macros. If you have trouble getting the zipped Excel workbook, then you can always install the Macros into the .xlsx file your self and they should work.

 

[B2]

I forgot to provide a picture..

 

[B2]

A few examples screen captures of Gcode tool paths taken from the Excel Gcode generator and simulated via Mill Wizard.
Similar examples are shown in the Excel files.
1) Single Circle device: At -35 degree angle, 2 flats, 1 Key-way, 2 hangers
2) Rectangle at +25 degree angle, 4 hangers, corners are cleared
3) Typical assembled panel with multiple device holes.

 

[Dabbler]

nice work. I'll have a look at the details when I have time, but well done for an ambitious project like this.

 

[ptrotter]

Hi B2,

I spent some time looking at this and I must say it is impressive work. The amount of effort to create these workbooks must have been incredible, to say the least. It did take quite awhile (and some help from a friend who knows more about Excel that I do) to get a basic understanding of how this works, but once I understood it, it was fairly simple to use. It looks like its ideal use is for panel cutouts like you have done for your lathe. If I were to do a one-off, I could probably make the model faster in Fusion 360, but if I needed to make a number of panels with different hole configurations, this tool makes it very easy to add, remove or move holes around and output gcode. The biggest drawback is the need to create the gcode templates for each individual shape you might need to cut out, but once you have a library of templates. creating new panels or modifying existing ones, is very fast, probably faster than using a CAD program.

If anyone is interested in what one can do without a fancy CAD/CAM program, this is a great example. It looks incredibly complex when you look at the Excel workbooks, but much of what you see is the backend modeling that you don't need to worry about to simply create the gcode for a panel.
Nice job.

Paul

 

[B2]

It would surely be nice to hear from someone. Has anyone other than @ptrotter tried out the Excel Tool? Has anyone built anything with it other than me? Am I wasting my time with these postings?

New Template is added to the spread sheet. Are there any other connector templates that would be of value to folks?

A question I do have for folks involves direction. As designed, the tool path in each of these templates runs Counter Clockwise. Hence, in removing material for a hole the remaining outside edge of this hole is being "Climb" cut. Would one get a smoother surface if the templates run Clockwise to yield a "normal" cut?

Since @ptrotter suggested that more templates would be useful I added the D-Sub connectors as a template. It took me a while to first find the full set of dimensions and then a while to code it. It is a single template but with switches to allow one to make any of the FRONT or BACK Mounted 5 standard D-Sub connectors (10 possible sizes). I used NorComp's connector mount descriptive table to get the dimensions. I have not actually cut these to see if they fit and would love to hear from anyone who has tried any of the fittings, but especially these. The URL for the connector dimension table along with lots of other info is shown in the Excel File Readme sheet.

A screen shot of a set of the 5 Front mounted ones, along with one showing pre-drilled hole locations superimposed on a device, is attached. As before any angle as well as any location is allowed. The mounting holes are the ends of the connectors are a switched option and anti-tear out hangers and their size at the 4 sides of a connector are allowed. So this screen shot demos some of this. There is also code for pre-drill holes where the End mill plunges. While pre-drilled holes for the mounting hole are available they are not needed as these are made via spiral cuts. They can be turned off as describe in the Readme sheet. The pre-drilled holes seem to improve end-mill life considerably.

In the process I found a couple of bugs in the original spread sheet Macro, module2, where they use line number referenced points in the panel Gcode construction process. These worked fine for the original templates which had the same line location structure, but the D-Sub template was of different length and spare lines were inserted for possible changes changing the line numbers that needed to be referenced. So this Macro bug is now fixed. Also, a small thing, I changed the variable name from "Tab" to "Hanger" when describing the small uncut pieces of material holding the hole material in place while cutting. Sometimes Tab is used to describe sheets and even confused me in the text. So hopefully it is gone now.

So I updated the zipped file for the panel spread sheet with the macros as well as the macro txt files. File names for attached files are:

PanelCreateAllSheets_Gcode uw-T 5Templates M108_1514.xlsm.zip This is complete and all that you need. Just unzip it.
Macros 7total M108_1545.zip This is the txt version (.bas) of the macros if you prefer to install them yourself.
PanelCreateAllSheets_Gcode uw-T 5Templates M108_1514.xlsx This is the workbook without any macros. Pretty useless without installing the macros, but you could see the sheets and the Readme file.

Dave

 

[Dabbler]

I used to solve client problems using Excel spreadsheets - What you have done here is pretty nice... I'm fighting some pretty big problems with plumbing (-28C here lately) and am meaning to get to your stuff, and possibly contribute.

Sorry for being so quiet about this, I hope to get back to your stuff later in January.

 

[B2]

@Dabbler

Wow! Too cold even for me. I have seen a lot of sub zero F temperatures, but never this low for very long! If I do the conversion correctly that must be about -18F. I wish you well! Keep water running through those pipes to keep them from freezing. Anyway, thanks for responding.

Of the places I have lived I think Minneapolis had the largest temperature/weather extremes. I was only there for 1 year, but I saw several 104F days and several -10F days during that year. It is a great city with lots of sun shine and lots to do. It was so dry that on those cold days with the sun shinning and clear sky it was very invigorating. You did not really feel the cold and it was not messy as it did not thaw out. The little bit of snow just evaporated before it melted. When you went out in it you just sort of went numb before you realized you were even cold. I even saw it rain mud one day. The winds picked up the dust from the west and when it got over the city it rained.....with dirt in the drops.

Anyway, if you ever get a chance just run the program, enable the macros and then type Cntrl+Shift+S and you will see it generate Gcode as it also copies it to the windows clipboard, which you can paste into a simulator. Type Cntrl+Shift+C (clear) to put the workbook back into pristine condition. After the Cntrl+Shift+C it will place the cursor at the top of a green table of values in the uwParam sheet. This is where you can change the parameters for the individual holes that are being generate. This and much more is explained in the uwReadme sheet.

I would love to have the opportunity to help anyone use it. Why else do we go to all of this trouble to share this stuff!

Thanks again!

Dave

 

[Dabbler]

In Calgary we have what are called Chinook winds ("snow eater") that have dramatic rises in temperatures. I have personally experienced -18C to +15C in a single afternoon. This week we will see 16C on Monday after two weeks at -25C average. (the biggest swing was in Pincer Creek: -19C to +22C in just a few hours.

I'm exited to try out your generator, and even add to it. I am rushing to turn on my garage furnace, but I am trying to complete a project in the ceiling and attic - fortunately it will be a warm week upcoming from +16 to +8 all week. so I hope I can get it done!

talk again soon!