Excel guru's please comment

LowRider CNC Advice
1

I have been toying with an idea. I want to take my GCode one created into a text document and drop it into Excel. I want to drop it into column B. Then in Column A I want to put =IF B2 Z<0,“FAIL”,“PASS”) Then use conditional formatting to color the cells red for fail and green for pass. I am just not sure how to write the If than to look at each line of the code to see if value of Z drops below 0. I would also like to build in a little caution zone like if Z<.1 Fail, If Z is between .1 and 0 then caution and Z>0 then pass. The caution could then be defined in the conditional formatting to be a yellow color. Then I want to save the spread sheet as a blank and be able to drop in my GCode after I get it out of the post processor just as a second visual to help with Crashes. This would be nothing more than one extra set of eyes for me to review to make sure I did not miss anything.

Those with Excel here are my two questions.

  1. how could I take the GCode and copy it into Excel and have each line of text go into a different cell B2 and down from there?
  2. what would the if then statement look like so that it would read each Z value down the line and do this? Am I close?
  3. Is there anything else that would be a good thing to look for in the GCode prior to running it on my machine.
The entire idea behind this is to help put an extra layer of safety in place to help me from crashing this machine into a work piece or driving the bit through a project.
2

I am not that good with excel, but if you drop it in repetier and show travel moves you can view it from the side and quickly see if anything is too low.

2 Likes
3

Gcode and excel won’t mix very well together. I haven’t done any of the visual basic programming in excel, but I did some very complicated stuff with the equations they give you. It is not going to be super easy.

I would do this in python, and I wonder if there’s a good playground environment for you (I assume you’re in windows). But just doing some basic text parsing would be pretty easy in python. Something like:

[scode]
with open(‘input.gcode’, ‘r’) as gcode:
for line in gcode:
words = line.upper().split()

    if len(words) == 0:
        continue

    if words[0] in ['G1', 'G0']:
        for word in words:
            if word.startswith("Z"):
                valueString = word[1:].strip()
                if len(valueString) == 0:
                    continue

                z_height = float(valueString)

                if z_height < 0.0:
                    print("Error, Z is {}".format(z_height))
                elif z_height < 0.1:
                    print("Caution, Z is {}".format(z_height))
                else:
                    continue

[/scode]

If you’ve ever wanted to learn some python, this would be a great little project to start with.

OK, now you’ve read my solution, I can help with yours. You basically have to do the same thing though. You have to split up the command into words, and look at each word to see if it has a Z in it (or at the beginning) and then take the rest of the word and convert it to a number. Then you can search the numbers for values that bother you.

So what I would do is import the file as a csv (excel will separate words into separate columns if you choose white space as a delimiter). Leave that raw data sheet alone, since it will be destroyed whenever there is a new gcode file.

Then I would make an equation sheet that (for each cell) would be something like IF(starts with Z, substring from 1 to the end , “”). In the next set of columns, I would convert that string to a number. In the front page, I would make a cell for each column, and do the check on the number and apply conditional formatting.

This in an interesting little problem. But yeah, Ryan’s solution is probably faster.

1 Like
4

Does whatever program you’re using to generate gcode not do simulation?

1 Like
5

It wouldn’t be that bad. I’ve debased myself in worse ways inside Excel… But it’s still using gas chromatography–mass spectrometry when a cheap smoke detector will do the job.

1 Like
6

Here’s something I threw together. One thing that’s possibly interesting is the ability to use number filtering, for example you can filter for Z < -3 and then do a plot of the X/Y coordinates. But you have to be careful with the plots because if you have say 50,000 lines Excel gets sluggish.

Note, if you get too creative with G92 you can still crash your tool and this program won’t detect it.

Sub Macro1()
    myFile = Application.GetOpenFilename("Gcode Files (*.gcode; *.nc),*.gcode;*.nc")
    
    ActiveSheet.Cells.Clear
    
    Row = 1
    
    Xpos = 0
    Ypos = 0
    Zpos = 0
    
    Cells(Row, 1) = "Gcode"
    Cells(Row, 2) = "G or M command"
    Cells(Row, 3) = "X position"
    Cells(Row, 4) = "Y position"
    Cells(Row, 5) = "Z position"
    
    Open myFile For Input As #1
    Do Until EOF(1)
        Row = Row + 1
        Line Input #1, textline
        Cells(Row, 1) = textline
        
        Words = Split(UCase(textline))
        
        If UBound(Words) < 0 Then
            Cells(Row, 2) = "-"
            GoTo ContinueDo
        End If
        
        If Left(Words(0), 1) <> "G" And Left(Words(0), 1) <> "M" Then
            Cells(Row, 2) = "-"
            GoTo ContinueDo
        End If
            
        Cmd = Words(0)
        
        If Len(Cmd) = 2 And Left(Cmd, 1) = "G" Then
            Cmd = "G0" & Mid(Cmd, 2)
        End If

        Cells(Row, 2) = Cmd
        
        If Cmd = "G00" Or Cmd = "G01" Or Cmd = "G02" Or Cmd = "G03" Then
            For Each w In Words
                If Left(w, 1) = "X" Then
                    Xpos = Val(Mid(w, 2))
                ElseIf Left(w, 1) = "Y" Then
                    Ypos = Val(Mid(w, 2))
                ElseIf Left(w, 1) = "Z" Then
                    Zpos = Val(Mid(w, 2))
                End If
            Next
        End If
        
ContinueDo:
        Cells(Row, 3) = Xpos
        Cells(Row, 4) = Ypos
        Cells(Row, 5) = Zpos
    Loop
    
    Close #1

End Sub
1 Like
7

Also note that you should take into account whether you’re in absolute or relative mode (yep, and G92 too)

1 Like