Graham Mayor
... helping to ease the lives of Microsoft Word users.
 |
 |
 |
Many people access the material from this web site daily. Most just take what they want and run. That's OK, provided they are not selling on the material as their own; however if your productivity gains from the material you have used, a donation from the money you have saved would help to ensure the continued availability of this resource. Click the appropriate button above to access PayPal.
Convert Labels to Excel Worksheet
The subject of editing pre-prepared label documents comes up in the forums on a fairly regular basis. I have often referred such enquiries to my web page - Convert Word labels to a mail merge data source - which covers a complex, but nevertheless effective, method of extracting label contents to a word table that can be used as a mail merge data source.
Now I have revisited the issue and produced a simple to use Word add-in that extracts data from a named label document to an Excel worksheet.
The add-in is driven by a simple userform from a button on the add-ins Tab of the ribbon
The userform, after my standard disclaimer, is as follows:
A default name is pre-inserted into the form, but you may change that and of course you must add a location to save it.
You must also add a document to process, which can be the active document or another selected document. If you get it wrong, the process is extensively error trapped with informative messages to indicate the error.
At this point you can click 'Extract the labels to the workbook'
and the process will extract the data to as many fields as are required for the data in the labels. The fields are all called Field# where # is an incrementing number:
Note that the process does not extract graphics from the labels - only the text components are recorded.
As the process can take a while to run, a progress indicator keeps track:
When the extraction is completed, the process wil attempt to save the workbook with the name and path selected on the userform. If a file of that name already exists, a prompt askes if you wish to overwrite the existing file. If you choose not to overwrite, the workbook is displayed, so that you may make your own choice of file name and save location.
Note that the process is a means to extract label data to allow it to be edited with a view to re-merging to labels.
The field names do not reflect the data in the Excel columns.
If you wished you could edit the Excel worksheet to make the columns and their headings more accurately
reflect that data, but it is not necessary to do so in order to
mailmerge the data back to labels.
- Click here to download the add-in
Extracting Label Data
Some years ago I prepared a web page Convert Word labels to a mail merge data source which describes how to extract the data from a pre-prepared label sheet, so that it may be extracted to a Word table for use as a Mail Merge data source.
This page builds on that process to extract data from a similar label sheet to an Excel worksheet, where again it may be easily edited and re-merged to labels.