need help with excel vba macro

[Timbersmith]

I'm trying to use VBA to generate a g-code text file that will run a CNC saw that will automaticly move the board to the right position and adjust the saw miter and bevel angles. I've already used other software to model the building and export pieces, then optimize and sequence all those "pieces" for cutting from the "boards" or raw stock. The text file to run the cnc machine needs to have the following format:

G28 M6 '(go to home position and wait for operator input)
G1 X75.0 Y-30.0 Z90 M6 '(inital cut-off. move x=75.0 y=-30 z=90 wait for operator input)
G1 X662.5.0 Y30.0 Z90 M6 '(left cut. similar to abv.)
G28 M6 '(go to home position and wait for operator input)
G1 X75.0 Y-45 Z45 M6 '(piece2 initial cut-off)
G2 X312.5 Y-30 Z90 M6 '(piece2 left cut)
G1 X35.0 Y90 Z90 M6 '(piece3 right cut)
G1 X212.5 Y30 Z90 M6 '(piece3 left cut)
G1 X40.0 Y20 Z90 M6 '(piece4 right cut)
G1 X237.5 Y30 Z90 M6 '(piece4 left Cut)
...



A little graphic might be handy to describe the situation. The graphic shows the saw with the 4 pieces that the two boards need to be cut into. Board 1 gets cut into piece 1 and board 2 gets cut into pieces 2,3 and 4.
http://picasaweb.google.com/kurtafra...85101064800914

G28 is a command to return to the home position, G1=set the saw to the following values, X=how far to move the x motor (red block in graphic), y=what angle to set the miter angle(top view rotation of saw blade), z=what angle to set the bevel motor(front view rotation of sawblade) and M6 says wait for the operator to do something and press the "next" button.

I've got a spreadsheet with 2 tables that define the pieces and boards. The spreadsheet that coresponds to the image would look like this:

Pieces!
Piece# Thickness Width Length Orientation Miter Bevel
1 88.1 180 650 Right -30 90
1 88.1 180 650 Left 30 90
2 88.1 180 300 Right -45 45
2 88.1 180 300 Left -45 90
3 88.1 180 200 Right 90 90
3 88.1 180 200 Left 30 90
4 88.1 180 225 Right 20 90
4 88.1 180 225 Left 30 90

Boards!
Board # Piece# Length
1 1 750
2 2 750
2 3 750
2 4 750

I'm only showing a few pieces and boards and simplifying the data for the example. A normal batch might have 200 boards being cut into 500 pieces.

The pieces need to be processed with a looping structure that loops through the boards sheet,looking up values from the pieces sheet:

Start the text file with a G28 line
For
If this is the first piece in a board
Generate gcode line "G28 M6"
Generate gcode line for initial right end cut-off. "G1 X--- Y--- Z--- M6"
Generate the gcode line for the left cut. "G1 X--- Y--- Z--- M6"
Else
Compare last piece left cut with this piece right cut.
If angle and bevel are not the same
Generate the gcode for this piece right cut "G1 X--- Y--- Z--- M6"
Else
Genreate the gcode for this piece left cut "G1 X--- Y--- Z--- M6"
End If
End If
Next

The Y and Z values (miter and bevel) just come right from the Pieces! sheet. The X value needs to be calculated for the 3 differetn conditions - initial right cut, left cut, or last left to current right. I've got all the math figured out but they are complicated formulas involvoing trig, piece width and thickness, and saw geometry constants like MiterPivotPoint, BladeThickness, and BladeOffset. I think if I can just get the values from the spreadsheet for now, I can substitute back in formulas once the looping structure all works properly. That way I don't unnecesarily complicate my request for help in here.

For now, if we just say that:
Initial Right cut, x=800 - Boards!Length +25.0
Left Cut = Pieces!Length + 12.5
LastLeft to CurrentRight = LastPieceLeftMiter + 10
I can substitute back in the big ugly formulas later.

[ger21]

I glanced at this earlier, but didn't have time then. Know that I have time to carefully read it, I don't see any questions in there.

I wrote an AutoCAD VBA macro that exports g-code. It's basically just a series of if..then...else statements

What I do is open a file to write to.

Set fs = CreateObject("Scripting.FileSystemObject")
Set gcode = fs.CreateTextFile(Fpath, True)

Then get all the values you need, and concatenate them into a string (along with your G1 X Y Z...). I don't have Excel here, so I'm guessing a little (along with googling).

Set up a string variable to use for each line of code.

Gcodeline = "G28 M6"

Write the line to the file

gcode.writeline (Gcodeline)

Set up the next line:

Gcodeline = "G1 X" + Cstr(Range(A1).value) + " Y" + Cstr(Range(A2).value) + " Z" + Cstr(Range(A3).value) + " M6"

gcode.writeline (Gcodeline)


Keep going until your done, and close the file. Is that what your looking for?

[Kiwi]

Timbersmith
I'm not familiar with generating gcode from excel, I use VB6 for generating code.
A program could be written to read the data from your excel spread sheet and manipulate as required. eg: The lengths could also be sorted to save waste.
See #15 of this post which shows data which was read from excel and converted to gcode.
http://www.cnczone.com/forums/showth...163#post249163

Send me PM if this is of interest.

[Kiwi]

Timbersmith
Took up the challenge and wrote a VB program..see attached.
I tried writing in Excel but I'm not up to speed with writing the code to notepad from excel.

[Kiwi]

Timbersmith
Two points that I'm unable to follow.
This line has G2........
G2 X312.5 Y-30 Z90 M6 '(piece2 left cut)
What is the difference?
And this line shows -45
2 88.1 180 300 Left -45 90
Should this be -30 ?

[Timbersmith]

The reason you are unable to follow is that they are my mistakes! You are right on both counts. Typos from doing it by hand.

I'd love to Skype or IM with you about this live and get some insight and try to conveyit better. It's hard to type out a good description. The piece-to-piece transition is particularly cumbersome.

On Skype I'm kurt.westerlund in Truckee CA USA.

Thanks a miilion for your help Kiwi!

[Kiwi]

Hi Kurt
I'm sorry, I'm not up with Skype as yet.
Did the VB program do what you need (with refinements)?
I've sent you a PM.

[Kiwi]

Kurt
Not sure if you still require this as I haven't heard anything for over 6 weeks.
I hope all is well.