Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / August 2007

Tip: Looking for answers? Try searching our database.

extract text from form dropdown to Excel

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
maryj - 31 Jul 2007 19:52 GMT
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!!
Signature

maryj

Jay Freedman - 01 Aug 2007 01:39 GMT
>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        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.
maryj - 01 Aug 2007 13:14 GMT
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!
Signature

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
[quoted text clipped - 21 lines]
> Email cannot be acknowledged; please post all follow-ups to the
> newsgroup so all may benefit.
Jay Freedman - 01 Aug 2007 15:46 GMT
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

Signature

Regards,
Jay Freedman
Microsoft Word MVP        FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

> 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
[quoted text clipped - 30 lines]
>> Email cannot be acknowledged; please post all follow-ups to the
>> newsgroup so all may benefit.
maryj - 01 Aug 2007 16:32 GMT
Awesome!!!
Worked great!
Signature

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
[quoted text clipped - 59 lines]
> >> Email cannot be acknowledged; please post all follow-ups to the
> >> newsgroup so all may benefit.

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.