Question: Importing spreadsheet data into g-code.

[TXFred]

Some background first:

I'm a fairly new programmer, with one year of experience programming with MasterCAM. I'm slowly but surely learning manual programming. I'm just getting started with macro programming.

I've been handed a project that I think could be accomplished with macros. I have to program engraving for a set of labels. There are a total of 229 labels. Each label lists different information that is provided in an Excel spreadsheet.

The simple way to do this is use G47 engraving (Haas Mill) and have the operator enter the values for each part. I don't like this. There's too much manual entry and too much opportunity for error.

I am envisioning a way to do this with macros. Here is the basic structure of what I want to have in the program. Additional features, such as turning the spindle on, will be added later.


Assign variable #100 to track the number of times the program has run.

GOTO #100

Then there will be a list of N entries. Inside each N entry is code that assigns
the excel spreadsheet values to macro variables. I think that I can talk Excel into outputting what I need. For example:
N1
#101=Tag Name
#102=Tag Number
etc. until all tag values are assigned.
Jump past the remaining list of entries.
GOTO N1000

Engrave the information
N1000
G47 P0 X1.703 Y-2.440 I0 J0.18 R0.1 Z-.025 E4. F4.0 (TAGNUMBER)
G47 P0 X2.866 Y-1.137 I0 J0.18 R0.1 Z-.025 E4. F4.0 (TAGNAME)

Increment the cycle counter
#100=#100+1
M30

That's my current plan. I'd like to get feedback on it. Will it work? Are there better ways to accomplish this? Are there any macro functions that might make this easier? I don't need a detailed explanation, just tell me what to look up and I'll go from there.

Sincerely,
Frederic

[TXFred]

While we're on the subject of macro programming, can anyone recommend me a good book to buy?

Frederic

[ProProcess]

One way to do this is to have a sub program like this...

O123(DATA-FILE)
N1
#101={TAG NUMBER HERE}
#102={TAG NAME HERE]
M99

N2
#101={TAG NUMBER HERE}
#102={TAG NAME HERE]
M99

(So on and so forth for remaining data)

then in your main program ...

O1(MAIN-PROGRAM)


M98P123H#100(GET-DATA-FROM-SUB)

(ENGRAVE-HERE-USING)
(VALUES-ASSIGNED-TO-#101-AND-#102)

#100=#100+1(INCREMENT-COUNTER)

We've done some pretty complex things with this type of schema.
It's simple and managable.
HTH
Good luck.
Let us know how you make out.

[TXFred]

Thanks for the help. I'll let you know how it turns out.

Frederic

[TXFred]

I've got some code written and hopefully ready to run.

As it turns out, we only have 200 hours of Macro language time available on the Haas mill. I don't want to waste it on debugging newbie mistakes, so I am posting a sample of the code here. I would appreciate it if somebody could look over it and point out any obvious flaws.

This first program calls the subroutine, and then performs the engraving.

(BEGIN MANUALLY ENTERED ENGRAVING CODE)

(CALL SUBROUTINE)
M98 P03128

(ENGRAVE-HERE-USING VALUES ASSIGNED TO)
(101 - 115 IN SUBROUTINE O03128)

(ENGRAVE THE TAG NUMBER)
G47 P0 X3.067 Y-1.782 I0 J0.15 R0.05 Z-.015 E24.4 F48.9 (#101)

(INCREMENT THE CYCLE COUNTER)
#100=#100+1

(END MANUALLY ENTERED ENGRAVING CODE)

The subroutine performs a simple GOTO command, assigns the variables, and then exits back to the engraving program.

%
O03128

(Go to the current variable set)
GOTO#100

N1
#101=1
#102=7/23/2010
#103=A10340353
#104=Allentown I
#105=Sika 2100 and Sika Plastiment
#106=0.45
#107=Jane Doe
#108=John Doe
#109=0
#110=700
#111=0
#112=Instrument Cube Block
#113=NULL
#114=Yes
#115=No
M99

Does that look OK?

Frederic

[ProProcess]

...

#103=A10340353
#104=Allentown I
#105=Sika 2100 and Sika Plastiment
...
M99

Does that look OK?

Frederic

I do not believe that you can perform these types of "String" asignments to Macro variables.
I think the asignments have to be numeric or macro in nature.
That would be easy enough to test in MDI...

#101=THIS IS A TEST

Let us know and good luck

[TXFred]

It took some doing, but I was able to make Excel output workable Gcode. Since I've lost count of the amount of help I've gotten from the Zone, I think it's worth my time to make a writeup of this method.

Step 1. Modify the excel file that you were given until it looks like step1.xls. Some fields contain the original data, and the other fields contain Haas G47 gcode. The very last column in each line contains code to shift the work offset to the next position, so you can engrave multiple tags in a single operation. (Note, save your original work offsets. If you stop the program in mid cycle, you will lose your original offsets.)

Step 2. Save the Excel file as a .csv file. Ignore the warnings that Excel gives you about losing formatting.

Step 3. Open the csv file in Word. Bring up the Find/Replace dialog.
A: Replace "," with nothing. This removes all the commas from the file.
B: Replace "CR" with "^p". This turns all the CR's into carriage returns.
C: Replace "^p " with "^p". This aligns all the text. Not necessary, but easier on the eyes.
D: Save as a .txt file.

Step 4. Rename the .txt file to .nc

Step 5. Paste the contents of the .nc file into your program at a convenient location. Anywhere after a retract move should be safe. I put mine at the end of the program for simplicity.

If your tags will have fields that do not change, program then in MasterCAM using the MCX Box font. It's almost identical to the font that Haas G47 engraving uses. On a completed tag, I cannot tell the difference.

Thanks again to all of you for offering advice and suggestions.

[stevo1]

Frederic,
Great work on this project.

I really just had to comment on your signature. LMAO.....what about the polishers that have to clean the welds? These are the people you want to keep in your good graces. #1 should always be the payroll lady.

Stevo

[sinha_nsit]

Fred,
You seem to have a good knowledge of Excel. So, I have a question:
Excel has limited editing options. For example, "Change Case" is not available. Can we save the .csv file opened in Word, back in .xls format, after necessary editing?

[dcoupar]

Fred,
You seem to have a good knowledge of Excel. So, I have a question:
Excel has limited editing options. For example, "Change Case" is not available. Can we save the .csv file opened in Word, back in .xls format, after necessary editing?

Excel has the UPPER function. You could write a macro to check each cell and convert it to UPPER case.

[sinha_nsit]

Is a function for Title Case also available (upper case for first letter of each word, such as in a name)?

[dcoupar]

Is a function for Title Case also available (upper case for first letter of each word, such as in a name)?

Yes, PROPER