Exporting multiple data sets from a single Word Form into Excel

[Tom Saylor]

PROBLEM: I have a multi-page MS Word file, each page of which contains a single discrete data set, with each datum entered as a content control. [In this case, each data set comprises a question and five multiple-choice answers, along with some classifications.] I want to export the data from this Word file into an Excel sheet so that each data set appears in a separate row in the spreadsheet.

Is there a macro available that will allow me to make this transfer?



I’ve found a macro created by Paul Edstein to export data from multiple Word files, each containing a single data set, but I need a macro that allows me to export data from a single Word file containing multiple data sets.

[gmayor]

See http://www.gmayor.com/ExtractDataFromForms.htm which does exactly that.

[Tom Saylor]

Thanks, Graham.

I installed the recommended add-in and ran it on a single Word file containing a total of three data sets--one data set per page, as described above--with 14 content controls in each data set. The application extracted all the data into a single row with 42 columns, not, as I had hoped, into three separate rows of 14 columns each. In other words, the application didn't recognize that each page of the Word file contains a discrete set of content controls to be extracted as a separate row of data in the Excel sheet

Is there some way that I can get the application to recognize divisions between data sets and to export the data on each page into a separate row of the Excel sheet?

Thanks for any help you can offer!

[gmayor]

It would be a major programming exercise to modify the already complex process to additionally separate each document into three separate rows by page.

It would be more practical either to separate the documents into their three page components before extracting the data, or extract the data to the 42 columns and split the worksheet into three equal (by column) segments and combine those segments into a single 14 column sheet.

[Tom Saylor]

Thanks for taking the time to think this through, Graham. Even if it's not what I'd hoped to hear, it's helpful to find out what is and and isn't feasible from someone who knows the terrain.

The Word files I'm working with often contain as many as 50 data sets (multiple-choices questions), so manual division would be cumbersome. I believe there's a Word macro available that will divide a large file into 50 smaller files according to delimiters placed in the large file, so maybe I could use that and then run Edstein's Excel macro, targeting the folder containing the 50 smaller files.

I'm intrigued, though, by your other suggestion that I might divide a long row of Excel data into multiple smaller segments. In my case, this would mean transforming a single 700-column row into 50 vertically aligned 14-column rows. Is there an Excel function or macro that would allow me to do this automatically?

[gmayor]

I was thinking more of 42 columns as in your original spec. 700 columns just adds to the complexity

However I am intrigued why you have the documents combined in the first place. What process do you have in place to create such a combined document?

On the face of it you appear to have a number of question sets combined into a single document. It would surely make things simpler if you were to have one question set per document, which makes it easier to extract the data by document to a separate line of the workbook (which is what I thought you were doing at the outset, when I suggested the add-in).

It might be possible to split the document into those separate documents, and then extract the data, but it would help to know both why you have it that way in the first place, and how the datasets are separated in the document so that they can be logically split into separate documents each with the same field sets.

[Tom Saylor]

It’s a long story, Graham. I work for a major educational testing company, developing all sorts of standardized tests, often consisting of multiple-choice questions (which we in the industry call “items”). For reasons all their own, a particular client wants batches of items delivered in an Excel sheet, one item per row. But the spreadsheet format (with items horizontally arrayed) is not at all congenial to the test development process. For that you need to have the items vertically arrayed in more or less the same way they’ll appear on the test and in a form that allows easy review and editing, and you want to have multiple items gathered into one document so that you can move readily from item to the next during the review process, resequence the items if necessary, and easily send all the items together from place to another. So for item development purposes, a Word file containing multiple items seems the most convenient option. The challenge I’m facing is to find the most convenient way to convert such a file to Excel format. You and others here have been a tremendous help. Thanks!