Create Form using just headings from Excel

1 Overview

Using Form_Mass_Add_Questions it is possible to build a form using just the headings from an Excel spreadsheet. Whilst usually people can just load the spreadsheet, with all it's data and just delete the data, in some instances "a little tweaking" can be very helpful. This process, shows how to convert the "Create from Excel" process to work as the "Create from Word" process.

N.B.  This article uses a lot of keyboard shortcuts.  You can use the traditional right click or provided buttons in the programs if you prefer.  We just find the shortcuts a lot faster.

2 Convert Excel top row into Word doc

If you wish to build a form using the top row of an Excel spreadsheet, with a few simple clicks it can be added using this engine.

Firstly open your Excel spreadsheet with the header row. Copy the row.

Open up Notepad and paste the row.

Again copy the row but this time from Notepad, so it should just be text and paste this into Word.

Ctrl and H on the keyboard is "find and replace". ^t means tab character. Find ^t and in the replace box put ^p for paragraph or new line marker.

This will quickly move each column heading into it's own row.

Now,

• ctrl and a to select all.
• ctrl and c to copy.
• Paste (ctrl and v) this into notepad.

Each header should be on its own line now. Now within Notepad,

• ctrl and a, then
• ctrl and c

Into a new spreadsheet in Excel.

• Paste (ctrl and v) this data into the second column.
• Add Q and a space all the way down in column A. i.e.

In A1 type "Q " then in the bottom right hand corner of that cell... find the darkened square and drag it down for all the headers you want to add.

Copy all the Qs and the headers back into Notepad. With the mouse highlight the character just before one of the questions. You can see the blue in the image below. This is a tab character. You can delete them all by hand, or

• Highlight the tab character
• ctrl and c to copy the tab
• ctrl and h to pop up the find and replace window
• ctrl and v in the find box and leave the replace box empty
• replace all

The remaining text is what you need for bxp to quickly mass add the questions.