seperate G-Code

[Dr.Evil925]

I need to seperate out a G-Code file into multiple cells. Any sugestions?
This is my start file.

N3G54G90G0X6.2Y.185S10000M3 <colgroup><col width="64"></colgroup><tbody> </tbody>

<tbody>
</tbody>

I want my result to be

N3

G54

G90

G0

X6.2

Y.185

S10000

M3

<tbody>
</tbody>

 

[Rick Rothstein]

You didn't give us an indication of the layout you wanted from that file, so I guessed (if I guessed wrong, the code can be modified). Here is a macro that asks you to select the file, processes that file totally in memory and then outputs the result to the active worksheet (which must be empty... the code checks). The layout I chose is Column A contains the N numbers, the remaining columns contain the numbers (only) from the codes that follow that N number. There is a G number before the first N number... I output as the first line with Column A left blank because there was no N number associated with it. All other lines in the file are ignored.

Sub ProcessGCodeFile()
  Dim X As Long, Z As Long, FileNum As Long, Index As Long
  Dim TotalFile As String, PathFile As String
  Dim NLines() As String, Gspaced() As String, LinesOut() As String
  Const MaxPossibleGCodesPerLine As Long = 20
  If ActiveSheet.UsedRange.Cells.Count = 1 And Len(ActiveSheet.UsedRange.Cells(1).Value) = 0 Then
    MsgBox "The active worksheet must be totally empty..." & vbLf & _
           "I see data on the currently active sheet", vbCritical
    Exit Sub
  End If
  With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    .Show
    On Error GoTo NoFileSelected
    PathFile = .SelectedItems(1)
  End With
  FileNum = FreeFile
  Open PathFile For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  NLines = Split(Replace(TotalFile, "G00", "N G00", , 1), vbNewLine & "N")
  ReDim LinesOut(1 To UBound(NLines), 1 To MaxPossibleGCodesPerLine)
  For X = 1 To UBound(NLines)
    Gspaced = Split(Split(NLines(X), vbNewLine)(0))
    Index = 0
    For Z = 0 To UBound(Gspaced)
      Index = Index + 1
      LinesOut(X, Index) = Mid(Gspaced(Z), 2 + (Z = 0))
    Next
  Next
  Range("A1").Resize(UBound(LinesOut), UBound(LinesOut, 2)) = LinesOut
NoFileSelected:
End Sub

Rick, I think that check needs a check.

Thanks Peter, you are correct. That greater than sign should be an equal sign. Fixed and shown in red above

 

[Peter_SSs]

Thanks Peter, you are correct. That greater than sign should be an equal sign. Fixed and shown in red above

Surely that now says if the sheet is empty then display a message that says "I see data" and if the sheet actually contains data it proceeds to run the rest of the macro?

 

[Rick Rothstein]

Surely that now says if the sheet is empty then display a message that says "I see data" and if the sheet actually contains data it proceeds to run the rest of the macro?

It surely does. Maybe I should just remove the test and let the OP overwrite any data on the sheet? Only kidding! Here is revised code that I believe now works correctly. Thanks for keeping me honest Peter.

Sub ProcessGCodeFile()
  Dim X As Long, Z As Long, FileNum As Long, Index As Long
  Dim TotalFile As String, PathFile As String
  Dim NLines() As String, Gspaced() As String, LinesOut() As String
  Const MaxPossibleGCodesPerLine As Long = 20
  If WorksheetFunction.CountA(ActiveSheet.UsedRange) Then
    MsgBox "The active worksheet must be totally empty..." & vbLf & _
           "I see data on the currently active sheet", vbCritical
    Exit Sub
  End If
  With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    .Show
    On Error GoTo NoFileSelected
    PathFile = .SelectedItems(1)
  End With
  FileNum = FreeFile
  Open PathFile For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  NLines = Split(Replace(TotalFile, "G00", "N G00", , 1), vbNewLine & "N")
  ReDim LinesOut(1 To UBound(NLines), 1 To MaxPossibleGCodesPerLine)
  For X = 1 To UBound(NLines)
    Gspaced = Split(Split(NLines(X), vbNewLine)(0))
    Index = 0
    For Z = 0 To UBound(Gspaced)
      Index = Index + 1
      LinesOut(X, Index) = Mid(Gspaced(Z), 2 + (Z = 0))
    Next
  Next
  Range("A1").Resize(UBound(LinesOut), UBound(LinesOut, 2)) = LinesOut
NoFileSelected:
End Sub