Generate report from ascii data file
Generate report from ascii data file
(OP)
I am programming parts for our Coordinate Measuring Machine, in MCOSMOS 2.4, for a Mitutoyo CMM. I can output the results in numerous ways, including PDF and an appended ascii file. I can also direct to Excel. If more than 1 part is checked, it appears that only the 1st data set is created in Excel.
I want to be able to create an Excel template of sorts that will allow Excel to automatically open the created appended data set, decide how many sets of data there are, and load into the appropriate cells in order to create a formatted report. The data sets will probably be from 1 to 10, and there could be as many as 20 - 25 dimensions per data set.
How do I set a monster like this up? I think that it has to be slightly intelligent. I can possibly go into the appended data set file (ascii) and add some data such as number of data sets, total number of dimensions per set, and file name/path to find data, but I would rather be prompted or just have to fill in a few cells to tell the spreadsheet what to do.
Someone else other than I will be loading and measuring the parts, so it has to be fairly easy to understand.
I have never written a macro before, created a template, ar am versed in VB.
Any ideas?
I want to be able to create an Excel template of sorts that will allow Excel to automatically open the created appended data set, decide how many sets of data there are, and load into the appropriate cells in order to create a formatted report. The data sets will probably be from 1 to 10, and there could be as many as 20 - 25 dimensions per data set.
How do I set a monster like this up? I think that it has to be slightly intelligent. I can possibly go into the appended data set file (ascii) and add some data such as number of data sets, total number of dimensions per set, and file name/path to find data, but I would rather be prompted or just have to fill in a few cells to tell the spreadsheet what to do.
Someone else other than I will be loading and measuring the parts, so it has to be fairly easy to understand.
I have never written a macro before, created a template, ar am versed in VB.
Any ideas?





RE: Generate report from ascii data file
I2I
RE: Generate report from ascii data file
Will you know in advance how many data sets there are each time? If so, you could have an event-initiated macro (File_Open) run asking you how many data sets there are with an input box for you to specify. The number you input would be stored for Excel to reference later when it performs whatever actions necessary on the data sets.
RE: Generate report from ascii data file
Thanks
RE: Generate report from ascii data file
RE: Generate report from ascii data file
Import some raw data, go to Tools/Macros/Record New Macro, give your macro a name and description, do your Data/Text to Columns thing, then Tools/Macro/Stop recording. Voila! you have a macro.
You can also insert autoshape buttons and assign a macro to each, so you just click on the shape to activate macro.
"...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928
"I'm searching for the questions, so my answers will make sense." - Stephen Brust
RE: Generate report from ascii data file
end of free plug. An immediate solution that might work is to go through the Excel help and figure out how to Record a macro. This allows you to get Excel to store a bunch of consecutive steps into a macro. Say you know how to open up your data (text) file by using File/Open in Excel. All you would have to do is use Tools/Macro/Record new macro by selecting it, and then just go through the normal steps of File/Open. At the end, you'll see your file is read in, then a small button is pushed to stop the Recording of the macro. (you'll see the button pop up right when you start recording the macro). I heartily recommend using "Record the macro" option as you learn how to do these things. You can get it to record any set of steps you do to do almost any operation in Excel. Then under View/Toolbars/Forms there is a small list of icons, pick the upper right one ('Button' shows up when you hover over the upper right icon with your mouse cursor), this creates a button on your screen which you can then point to this new macro you just recorded.
RE: Generate report from ascii data file
If your output data always has the same file name/location then you're done. If not, you need to edit the macro to allow you to specify a file name/location. I've never written a macro that allowed me to open the File Open dialog, but I know it can be done.
I'm still not sure how the number of data sets affects what you have to do. Are the data sets different files? Or do they have to go onto different sheets? Either way, it's relatively easy if you know your way around VBA, and by recording macros and using the help function (put your cursor in the bit you don't understand and hit F1!) you should find your way. To edit macros and find the code, open the Macro Toolbar or just hit Alt-F11.
RE: Generate report from ascii data file
easy as anything... You can just as easily link the data as embed it...
Dik