Excel VBA when exported to txt for Mcam library has too many spaces between data

[Dustin S.]

I have been working with Mastercam and Excel VBA to crack the almighty mass editing of the tools and have accomplished this as far as I am concerned with VBA. I now want to export my Excel sheet to text so I can import it back into Mastercam.

 

I can export my excel to text but there are too many spaces between the data and Mastercam won't use it. I have tried changing the column spaces in excel but the data is too varied.

 

Is there a way to make a converter that will put just 1 space between each set of data??

 

code:

---

This is my data after being exported to text:

1 - TOOL

2 - 1

3 - #64 .0360 DRILL

4 -

5 -

6 -

7 - 19 3 0 0 0 0 118 19 19 5 50 200 18000 162 1 -1

8 - 0 0. 0. 0 0 0 0 0 0

9 - 0. 0.625 1.5 1 0 0 0 0 0 0 0

11 - 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

---

code:

---

This is how the data needs to be formated:

1 - TOOL

2 - 0

3 - #29 .1360 NC DRILL

4 -

5 -

6 -

7 - 60 3 0 0.136 0. 0. 118. 60 60 37.4 50. 200. 9128 16 2 0 -1

8 - 0 0. 0. 0. 0. 0. 0. 0. 0.

9 - 0. 0.9375 1.9375 0.9375 0.136 0. 0. 0 0. 0. 0

11 - 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0

---

Thanks in advance for any help

[Mick from CNC Software Inc.]

You can use a regular expression to remove the excess spaces using the function below:

 

code:

---

Function RemoveWhiteSpace(strText)

 

Dim RegEx

Set RegEx = New RegExp

RegEx.Pattern = "s+"

RegEx.MultiLine = True

RegEx.Global = True

strText = RegEx.Replace(strText, " ")

RemoveWhiteSpace = strText

 

End Function

---

In your VBA code you would iterate each line of text and pass in the current line to the function and write it out to a new file for example.

 

code:

---

Dim clean as string

 

clean = RemoveWhiteSpace(mylineoftext)

---

From Excel, press ALT+F11 to open up the VBA editor, from the Tools References menu select Microsoft VBScript Regular Expressions 5.5 or whatever version is on the machine and you should be good to go.

[Mick from CNC Software Inc.]

If you are not comfortable writing a macro in Excel to do this feel free to e-mail me a zipped excel file and I'll write a quick little app that will convert it for you.