Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

The task I'd like to complete is to keep an item bank in Excel, complete with text for the item, correct answer(s), tags for the item to indicate what it measures and what materials its based on.

With this data, how can I create a template in Word that will allow me to query for a random item that meets certain criteria from the item bank?

It'd be great to just be able to design a bunch of items, and create a template so that any time I want a 'fresh' quiz I can just refresh or click some other simple sequence of buttons.

What tools would I be using within Office to accomplish this?

share|improve this question
1  
Use Access as your "Item Bank" since it's for Databases, where as Excel is for Spreadsheets. –  techie007 Sep 30 '13 at 20:17
add comment

1 Answer

Here's one approach which doesn't use any VBA. It assumes that you are using Windows Word (it won't work on Mac Word) and

  • the number of items you want in your document is fixed (i.e. does not vary randomly)
  • your "Item Bank" is a tabular Excel sheet, i.e. all the rows contain the same columns, and the first row contains column names.
  • either the data for your template only needs to come from a single randomly selected row, or if you need data from multiple rows, it doesn't matter if they are sometimes the same row.

Let's assume your Excel workbook is at c:\xl\rnd.xlsx and the data is in Sheet1.

Call the first column k, and put the row number in it, so you have

k
1
2
3

...

Call the second column r. In cell B2, put the following formula

=COUNT(A:A)

In cells B3,B4 etc. put the following formula

=RANDBETWEEN(2,$B$2)

Don't use row 2 (where k=1) for your data.

Create and populate as many additional columns as you need (up to 255 in total). If you have any columns with memo texts (over 255 characters long), make sure you put a memo text in row 2. That should ensure that Word will get the complete text of any later memo fields.

In Word, for each item you want to insert, insert the following nested fields, using the column name of the data item you want to insert instead of "mycolumn"

{ DATABASE \d "c:\\xl\\rnd.xlsx" \s "SELECT mycolumn FROM [Sheet1$] WHERE k = { DATABASE \d "c:\\xl\\rnd.xlsx" \s "SELECT r FROM [Sheet1$]  WHERE k = 2" }" }

(make sure you double up the backslashes in the path name, and ensure that both pairs of {} are the special field code braces that you can insert using ctrl-F9 on Windows Word.

If you want more than one item from the same Excel row, use the same index row (i.e. k = 2). If you want items from a different record, use a different index (e.g. k = 3)).

Save your template.

When you want to create a new test (or whatever),

  1. Open the Excel file and add any new data you need, then recalculate. Save the Excel file but do not close it. the sheet. That should update the RANDBETWEEN values
  2. Make a copy of your template (if it's an ordinary .docx/m, or open a new document based on it (if it's a .dotx/m) and save it
  3. Select the document body (ctrl-A) and press F9 to update all the fields
  4. Assuming you are happy with the results, "fix" them by selecting the document body and pressing ctrl-shift-F9.
  5. Save the document

There are drawbacks to this approach, including

  • As mentioned before, if you need data to come from different rows, this won't guarantee that they are different, because r can have the same value for multiple values of k. I suppose if you don't have much data, you could repeat steps (1) and (3) until you have a satisfactory result. Otherwise, it would be necessary to think of a way to avoiding getting duplicate rows, and that would probably involve VBA, in which case you might as well do the whole thing a different way.
  • Also, these days, Word unhelpfully inserts a paragraph mark at the beginning of each DATABASE field result. This can be removed for numeric and date results using a numeric/date-time formatting switch, but not for text. If you have to remove those paragraph marks, you'd need either a reliable mechanism using Find/Replace, or (again) a bit of VBA.
  • DATABASE fields don't work in Word tables (and probably other places). If you need to put data in those places, you would need to use a SET field to set a bookmark to a result, then use a REF field where you wanted the data.
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.