extract text from form dropdown to Excel

[maryj]

Client is using Excel 2003/Win XP. He received an 8 page Word form with many
dropdown boxes. He is looking for a way to extract the data from the
dropdowns into Excel.

Thanks for your help!!

 

[Jay Freedman]

Client is using Excel 2003/Win XP. He received an 8 page Word form with many
dropdown boxes. He is looking for a way to extract the data from the
dropdowns into Excel.

Thanks for your help!!

Assuming the client also has Word 2003 handy:

- Open the form document in Word.
- Go to File > Save As.
- Click the Tools button in the upper right corner of the dialog.
- Select Save Options.
- Check the box for "Save data only for forms".
- Enter a name for the data, which will automatically be set to save
as a text file. It will contain only the form data in comma-separated
variable (CSV) format, although the file's extension will be .txt
unless you change it. Excel doesn't care whether it's .txt or .csv.
- Open the resulting file in Excel.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[maryj]

Thanks Jay, but I guess I wasn't totally clear in my first post.
The client doensn't need just the selected value in the dropdown fields, he
would like to export all the possible options for each dropdown to an Excel
file. This form must have close to 50 different dropdowns and he's trying to
avoid having to manually enter all the options into Excel.

Thanks!

 

[Jay Freedman]

Yes, that's possible. This macro will create a .csv file with one row for
each dropdown, containing the name of the dropdown and all the items in its
list. You can then open the .csv file in Excel. If you want the data in
columns instead of rows, Excel's Paste Special command can do that (check
the Transpose box in the dialog).

Sub ExtractDropdownData()
Const qt = """"
Dim strData As String
Dim ddff As FormField
Dim le As ListEntry
Dim outputFile As String

outputFile = Replace(ActiveDocument.FullName, ".doc", "_data.csv")
Open outputFile For Output As #1

For Each ddff In ActiveDocument.FormFields
If ddff.Type = wdFieldFormDropDown Then
strData = qt & ddff.Name & qt
For Each le In ddff.DropDown.ListEntries
strData = strData & "," & qt & le.Name & qt
Next
End If
Print #1, strData
Next

Close #1
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[maryj]

Awesome!!!
Worked great!
--
maryj

Yes, that's possible. This macro will create a .csv file with one row for
each dropdown, containing the name of the dropdown and all the items in its
list. You can then open the .csv file in Excel. If you want the data in
columns instead of rows, Excel's Paste Special command can do that (check
the Transpose box in the dialog).

Sub ExtractDropdownData()
Const qt = """"
Dim strData As String
Dim ddff As FormField
Dim le As ListEntry
Dim outputFile As String

outputFile = Replace(ActiveDocument.FullName, ".doc", "_data.csv")
Open outputFile For Output As #1

For Each ddff In ActiveDocument.FormFields
If ddff.Type = wdFieldFormDropDown Then
strData = qt & ddff.Name & qt
For Each le In ddff.DropDown.ListEntries
strData = strData & "," & qt & le.Name & qt
Next
End If
Print #1, strData
Next

Close #1
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[u tran]

Do you have a macro that takes data from the created .csv file and populate the dropdown items? It's pretty much going backwards.

Thank you!