Copy, Modify, Paste, Send to CNC machine

[camle]

I have come up with an excel sheet to program a CNC machine at work. It reads a database (Main Data Mag Cyl) for different prints.
My question is: I need a VB code to copy A7:C616 and copy it to sheet name Edit - Send with (tabs and empty spaces) removed. From here I would like to send it to the com port to the CNC machine, also a pop-up windows asking if the machine is ready.
Hope you don't mind helping. Thanks

 

[camle]

I have come up with an excel sheet to program a CNC machine at work. It reads a database (Main Data Mag Cyl) for different prints.
My question is: I need a VB code to copy A7:C616 and copy it to sheet name Edit - Send with (tabs and empty spaces) removed. From here I would like to send it to the com port to the CNC machine, also a pop-up windows asking if the machine is ready.
Hope you don't mind helping. Thanks

No Ideas?
am I in the wrong area?

 

[camle]

No Ideas?
am I in the wrong area?

Thanks for the help

 

[Teeroy]

Never tried it myself but have a read of VBA control of serial port - Forums.MrPLC.com.

 

[camle]

Thanks Man that is a good link.

 

[John_w]

Try this for starters. It uses VBA file I/O to send data to the COM2 port (no need for the MSComm control). You might have to change the string sent to the COM port - currently each value is separated by a space and terminated by CR LF characters.

Sub Send_to_COM_Port()

    Dim dataArray As Variant, r As Integer, c As Integer
    Dim SendSheet As Worksheet
    Dim lastRow As Long, row As Long
    Dim COMport As Integer
    Dim rowCells As Range
    Dim buffer As String
    
    Set SendSheet = Sheets("Edit - Send")
        
    'Copy A7:C616 to SendSheet, removing tabs and spaces
    
    dataArray = Sheets("Sheet1").Range("A7:C616")
    For r = 1 To UBound(dataArray, 1)
        For c = 1 To UBound(dataArray, 2)
            dataArray(r, c) = Replace(Replace(dataArray(r, c), vbTab, ""), " ", "")
        Next
    Next
    With SendSheet
        .Cells.Clear
        .Range("A1").Resize(UBound(dataArray, 1), UBound(dataArray, 2)).Value = dataArray
        lastRow = UBound(dataArray, 1)
    End With
    
    If MsgBox("Is the machine ready?", vbYesNo) = vbNo Then Exit Sub
    
    COMport = FreeFile
    Close #COMport
    
    'Open COM2 port with baud rate 2400, No parity, 8 data bits, and 1 stop bit
    
    Open "COM2:2400,N,8,1" For Random As #COMport
    
    'Send each row to COM2 port
    
    For row = 1 To lastRow
        
        'Create string with this row's A, B and C cell values separated by a space
        
        Set rowCells = SendSheet.Cells(row, 1).Resize(1, 3)
        buffer = Join(Application.Transpose(Application.Transpose(rowCells.Value)), " ")
        
        'Send string to COM2 port
        
        Put #COMport, , buffer & vbCrLf
        
        Application.Wait DateAdd("s", 0.2, Now)  'pause between each data send
        DoEvents
    Next
    
    Close #COMport
    
End Sub

 

[camle]

Very nice Thanks

 

[camle]

Works great!!!!
In the vb code I forgot to say remove empty rows, can you help with this?

Thanks so much

 

[Teeroy]

Try changing this:

For row = 1 To lastRow
        
        'Create string with this row's A, B and C cell values separated by a space
        
        Set rowCells = SendSheet.Cells(row, 1).Resize(1, 3)
        buffer = Join(Application.Transpose(Application.Transpose(rowCells.Value)), " ")
        
        'Send string to COM2 port
        
        Put #COMport, , buffer & vbCrLf
        
        Application.Wait DateAdd("s", 0.2, Now)  'pause between each data send
        DoEvents
    Next

to:

For Row = 1 To lastRow
        
    'Create string with this row's A, B and C cell values separated by a space
    
    Set rowCells = SendSheet.Cells(Row, 1).Resize(1, 3)
    'check if all cells are blank and only run code if they are not
    Set checkCells = rowCells.SpecialCells(xlCellTypeBlanks) 'DONT FORGET TO DIM CHECKCELLS AS RANGE
    If rowCells.Cells.Count <> checkCells.Cells.Count Then
        buffer = Join(Application.Transpose(Application.Transpose(rowCells.Value)), " ")
        
        'Send string to COM2 port
        
        Put #COMport, , buffer & vbCrLf
        
        Application.Wait DateAdd("s", 0.2, Now)  'pause between each data send
        DoEvents
    End If
Next

And add at the top

Dim CheckCells as range

 

[John_w]

In the vb code I forgot to say remove empty rows, can you help with this?

Run this instead (Teeroy's modification ignores them rather than removes them):

Sub Send_to_COM_Port()

    Dim dataArray As Variant, r As Integer, c As Integer
    Dim SendSheet As Worksheet
    Dim lastRow As Long, row As Long
    Dim COMport As Integer
    Dim rowCells As Range
    Dim buffer As String
    
    Set SendSheet = Sheets("Edit - Send")
        
    'Copy A7:C616 to SendSheet, removing tabs and spaces
    
    dataArray = Sheets("Sheet1").Range("A7:C616")
    For r = 1 To UBound(dataArray, 1)
        For c = 1 To UBound(dataArray, 2)
            dataArray(r, c) = Replace(Replace(dataArray(r, c), vbTab, ""), " ", "")
        Next
    Next
    With SendSheet
        .Cells.Clear
        .Range("A1").Resize(UBound(dataArray, 1), UBound(dataArray, 2)).Value = dataArray
        
        'Delete blank rows
        
        lastRow = .UsedRange.row + .UsedRange.Rows.Count - 1
        For row = lastRow To 1 Step -1
            If WorksheetFunction.CountA(.Rows(row)) = 0 Then
                .Rows(row).EntireRow.Delete
            End If
        Next
        
        lastRow = .UsedRange.Rows.Count
    End With
    
    If MsgBox("Is the machine ready?", vbYesNo) = vbNo Then Exit Sub
    
    COMport = FreeFile
    Close #COMport
    
    'Open COM2 port with baud rate 2400, No parity, 8 data bits, and 1 stop bit
    
    Open "COM2:2400,N,8,1" For Random As #COMport
    
    'Send each row to COM2 port
    
    For row = 1 To lastRow
        
        'Create string with this row's A, B and C cell values separated by a space
        
        Set rowCells = SendSheet.Cells(row, 1).Resize(1, 3)
        
        buffer = Join(Application.Transpose(Application.Transpose(rowCells.Value)), " ")
                
        'Send string to COM port
        
        Put #COMport, , buffer & vbCrLf
    
        Application.Wait DateAdd("s", 0.2, Now)  'pause between each data send
        DoEvents
    Next
    
    Close #COMport
    
End Sub