I need to turn spreadsheet data into a .txt file for running a computerized
saw. I am trying to get a single board cut into multiple pieces.

The boards going in are all the same length, in this example 585mm long.
They get cut into pieces of varying lengths. If the pieces are 58mm, then
one board might get cut into 10 pieces. If they are 290mm, then one board
would get cut into two pieces. I've already optimized the desired cutting
list to figure out how to cut them most efficiently and exported that data
into a spreadsheet.

The spreadsheet looks something like this:
Board Piece Length
1 104 123.4
1 105 456.7
2 106 582.1

This is a reduced dataset to illustrate the problem. In this example 2 raw
boards each 585mm long need to be cut into 3 differetn pieces. The first
board gets cut into pieces 104 and 105. The second board gets cut into piece
106.

The text file I need for this looks something like this:
G28
G1 X123.4 M6
G1 X456.7 M6
G28
G1 X89.0 M6
G28

Line 1
Where "G28" means return the carriage to the home position.

Line 2
G1 = "move the carrier" Xnumber means "in the x direction, this number of
units" and M6= "wait for the operator to hit the next button" (the operator
will use the saw to cut the wood off at the 123.4 length and then hit the
"next piece" button)

Line 3
same as Line 2 but for a different length piece

Line 4
Return the carriage to the home position again and wait for the operator to
load the next board and hit the "next piece" button again. The carrier needs
to return to the home position after each board is cut into its requsite
pieces.

Line 5
Move and wait

Line 6
Return home and wait.

I need to do this for a much larger dataset where some boards can get cut
into any number of pieces, depending on how the optimization process grouped
them.

I think I need some sort of overall looping structure to keep going until
the list is over and another looping structure inside it to loop until the
board number changes, then insert a G28 M6 line and start over.

I hope it an easy request, thanks for any help.

Hope this makes sense. . .


Example

MockUpData
Board Piece Length
1 104 123.4
1 105 456.7
1 106 582.1
2 107 123.4
2 108 456.7
3 109 123.4
3 110 456.7
4 111 1763.5
4 112 1992.85
4 113 2222.2

Output
G28
G1 X123.4 M6
G1 X456.7 M6
G1 X582.1 M6
G28
G1 X123.4 M6
G1 X456.7 M6
G28
G1 X123.4 M6
G1 X456.7 M6
G28
G1 X1763.5 M6
G1 X1992.85 M6
G1 X2222.2 M6
G28

SubProcedure

Sub WriteToText()
'The file name that will be used
Dim sFileName As String
sFileName = "C:\ExampleOuput.txt"

'File System Objects - Set a reference to "Microsoft Scripting Runtime"
'Under Tools - References
Dim fso As Scripting.FileSystemObject
Dim f As Scripting.TextStream



'Create the File System and Text Stream Objects
Set fso = New Scripting.FileSystemObject
Set f = fso.CreateTextFile(sFileName, True, False)

'Data Range Information to Process
Dim iRow, iCol
Dim wsWorkSheet
Dim wsRange As Range

Set wsWorkSheet = ThisWorkbook.Sheets("DataSheet")
Set wsRange = wsWorkSheet.Cells(1, 1).CurrentRegion ' Get the range of
data


f.WriteLine "G28" 'Start the file with a G28 command

For iloop = 2 To wsRange.Rows.Count 'skilp the header row thus iLoop = 2
IboardNum = wsRange(iloop, 1)
f.WriteLine "G1 X" & wsRange(iloop, 3) & " M6"
If IboardNum < wsRange(iloop + 1, 1) Then
f.WriteLine "G28"
End If
Next

f.Close

Set f = Nothing
Set fso = Nothing

End Sub




"Lumpy" wrote in message
...
I need to turn spreadsheet data into a .txt file for running a computerized
saw. I am trying to get a single board cut into multiple pieces.

The boards going in are all the same length, in this example 585mm long.
They get cut into pieces of varying lengths. If the pieces are 58mm, then
one board might get cut into 10 pieces. If they are 290mm, then one board
would get cut into two pieces. I've already optimized the desired cutting
list to figure out how to cut them most efficiently and exported that data
into a spreadsheet.

The spreadsheet looks something like this:
Board Piece Length
1 104 123.4
1 105 456.7
2 106 582.1

This is a reduced dataset to illustrate the problem. In this example 2
raw
boards each 585mm long need to be cut into 3 differetn pieces. The first
board gets cut into pieces 104 and 105. The second board gets cut into
piece
106.

The text file I need for this looks something like this:
G28
G1 X123.4 M6
G1 X456.7 M6
G28
G1 X89.0 M6
G28

Line 1
Where "G28" means return the carriage to the home position.

Line 2
G1 = "move the carrier" Xnumber means "in the x direction, this number
of
units" and M6= "wait for the operator to hit the next button" (the
operator
will use the saw to cut the wood off at the 123.4 length and then hit the
"next piece" button)

Line 3
same as Line 2 but for a different length piece

Line 4
Return the carriage to the home position again and wait for the operator
to
load the next board and hit the "next piece" button again. The carrier
needs
to return to the home position after each board is cut into its requsite
pieces.

Line 5
Move and wait

Line 6
Return home and wait.

I need to do this for a much larger dataset where some boards can get cut
into any number of pieces, depending on how the optimization process
grouped
them.

I think I need some sort of overall looping structure to keep going until
the list is over and another looping structure inside it to loop until the
board number changes, then insert a G28 M6 line and start over.

I hope it an easy request, thanks for any help.

Perfect Dan. Thank you very much. There's more to do and the dataset was
very simplified but I think I can get it from here.

"DanRoss" wrote:

Hope this makes sense. . .


Example

MockUpData
Board Piece Length
1 104 123.4
1 105 456.7
1 106 582.1
2 107 123.4
2 108 456.7
3 109 123.4
3 110 456.7
4 111 1763.5
4 112 1992.85
4 113 2222.2

Output
G28
G1 X123.4 M6
G1 X456.7 M6
G1 X582.1 M6
G28
G1 X123.4 M6
G1 X456.7 M6
G28
G1 X123.4 M6
G1 X456.7 M6
G28
G1 X1763.5 M6
G1 X1992.85 M6
G1 X2222.2 M6
G28

SubProcedure

Sub WriteToText()
'The file name that will be used
Dim sFileName As String
sFileName = "C:\ExampleOuput.txt"

'File System Objects - Set a reference to "Microsoft Scripting Runtime"
'Under Tools - References
Dim fso As Scripting.FileSystemObject
Dim f As Scripting.TextStream



'Create the File System and Text Stream Objects
Set fso = New Scripting.FileSystemObject
Set f = fso.CreateTextFile(sFileName, True, False)

'Data Range Information to Process
Dim iRow, iCol
Dim wsWorkSheet
Dim wsRange As Range

Set wsWorkSheet = ThisWorkbook.Sheets("DataSheet")
Set wsRange = wsWorkSheet.Cells(1, 1).CurrentRegion ' Get the range of
data


f.WriteLine "G28" 'Start the file with a G28 command

For iloop = 2 To wsRange.Rows.Count 'skilp the header row thus iLoop = 2
IboardNum = wsRange(iloop, 1)
f.WriteLine "G1 X" & wsRange(iloop, 3) & " M6"
If IboardNum < wsRange(iloop + 1, 1) Then
f.WriteLine "G28"
End If
Next

f.Close

Set f = Nothing
Set fso = Nothing

End Sub




"Lumpy" wrote in message
...
I need to turn spreadsheet data into a .txt file for running a computerized
saw. I am trying to get a single board cut into multiple pieces.

The boards going in are all the same length, in this example 585mm long.
They get cut into pieces of varying lengths. If the pieces are 58mm, then
one board might get cut into 10 pieces. If they are 290mm, then one board
would get cut into two pieces. I've already optimized the desired cutting
list to figure out how to cut them most efficiently and exported that data
into a spreadsheet.

The spreadsheet looks something like this:
Board Piece Length
1 104 123.4
1 105 456.7
2 106 582.1

This is a reduced dataset to illustrate the problem. In this example 2
raw
boards each 585mm long need to be cut into 3 differetn pieces. The first
board gets cut into pieces 104 and 105. The second board gets cut into
piece
106.

The text file I need for this looks something like this:
G28
G1 X123.4 M6
G1 X456.7 M6
G28
G1 X89.0 M6
G28

Line 1
Where "G28" means return the carriage to the home position.

Line 2
G1 = "move the carrier" Xnumber means "in the x direction, this number
of
units" and M6= "wait for the operator to hit the next button" (the
operator
will use the saw to cut the wood off at the 123.4 length and then hit the
"next piece" button)

Line 3
same as Line 2 but for a different length piece

Line 4
Return the carriage to the home position again and wait for the operator
to
load the next board and hit the "next piece" button again. The carrier
needs
to return to the home position after each board is cut into its requsite
pieces.

Line 5
Move and wait

Line 6
Return home and wait.

I need to do this for a much larger dataset where some boards can get cut
into any number of pieces, depending on how the optimization process
grouped
them.

I think I need some sort of overall looping structure to keep going until
the list is over and another looping structure inside it to loop until the
board number changes, then insert a G28 M6 line and start over.

I hope it an easy request, thanks for any help.