Generate report from ascii data file 1

[mattding]

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?

 

[insult2injury]

I would assume that the ascii data is delimited in some way. If this is the case you should be able to open the data with excel. If not, then you are gonna need to teach yourself some vb...particularly file i/o and parsing strings.

I2I

 

[francesca]

I'm afraid that you're going to have to venture into waters you've not yet chartered yourself, or find someone who has. You'll need a template with macros embedded. The macros themselves might be quite simple; it's impossible to tell not knowing what the data looks like and if it'll need formatting.

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.

 

[mattding]

Thanks to those who have made suggestions so far. The ascii data in question is delimited by semi-colons. I can and have brought the data into Excel, by opening the file in question. All of the data is placed into individual cells. I will know how many data sets were taken and the number of dimensions measured. I am just trying to figure how to get from A => B.

Thanks

 

[mattding]

One other thing along this line... Is there a Excel reference that anybody can recommend that shows how to set this up. I'll buy what I have to, if necessary.

 

[ACtrafficengr]

If your data format is standardized, and you can standardize the procedure, a simple macro should be able to take care of most of it. If you have a few common data set formats, you could set up a macro for each format. If your data set vary widely, this may not work for you.

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

 

[prost]

The Excel reference I like and use quite often is "VBA and Macros for Microsoft Excel" by Bill Jelen. It comes with access to many XLS macros, avail. on his website.

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.

 

[francesca]

The ascii data in question is delimited by semi-colons. I can and have brought the data into Excel, by opening the file in question. All of the data is placed into individual cells. I will know how many data sets were taken and the number of dimensions measured. I am just trying to figure how to get from A => B.

To automate the procedure of opening the semi-colon delimited file, open Excel and record a macro (Tools --> Macros --> Record) while you open the file. Once it's open, press the stop button to terminate recording the macro.

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.

 

[dik]

I've just embedded a bunch of steel properties into a spreadsheet... Used Word to convert database to comma delimited data and just pasted this into an excel spreadsheet. I then convert the comma limited data to excel columns...
easy as anything... You can just as easily link the data as embed it...

Dik