Creating Address Labels - without saving anything

George J

Well-known Member
Feb 15, 2002
I know this is an old chestnut, but i was hoping that someone had found something by now. I'm using Excel 2003 and have written some VBA to extract names and addresses from a webpage (at owners request). A user just pushes a single button and all the data is collected and sorted into a new workbook - one sheet for email updates and another sheet for postal.

Is there a way to get the data in column A (names and addresses nicely structured) into word labels? I know you can use a datasource in word, but i don't want anything to be saved onto the users system. The workbook they can close without saving once they have sent the emails and the postal sheet has a button on it just waiting for the code that will create the labels when pressed.

I can't find any way of copying and pasting the data to the labels manually - i even tried putting vbtab & chr(9) at the end of each cell to see if it would force it to tab to the next cell when inputting. Can anyone suggest a way of achieving this or how to even transfer from excel to labels one cell at a time?

I managed to get what i thought was the complicated part out of the way, but because nothing should be saved i'm stumped at the final hurdle.

thanks for any advice or wisdom.

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Thanks to all who had a look at this.

I think i've got it now - was struggling to even get Word to be visible at first (visible=true did nothing), but defining the Application and Document as Object seemed to do the trick. Had a little fight with selection.typetext also, but seems to behave now. Just need to check the sizing and formatting.

Dim xLabelDoc As Object 'Word.Document
'This macro was written by George J on the MrExcel forum.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'Only because it was a real pain in the nether region to create!  :o)

Dim xBlankDoc As Object 'word.document
Dim xLabelDoc As Object 'word.document
Dim xWord As Object 'word.application
Dim z As Range

Set xWord = CreateObject("Word.Application")
xWord.Visible = False

Set xBlankDoc = xWord.ActiveDocument

xWord.MailingLabel.CreateNewDocument Name:="L7163"
Set xLabelDoc = xWord.ActiveDocument

For Each z In Range(Range("a1"), Range("a65536").End(xlUp))
    xWord.Selection.TypeText Text:=z.Text
    If xTabCount = Empty Then
        xWord.Selection.MoveRight Unit:=wdCell
        xWord.Selection.MoveRight Unit:=wdCell
        xTabCount = 1
        If Not z.Row = Range("a65536").End(xlUp).Row Then
            xWord.Selection.MoveRight Unit:=wdCell
            xTabCount = Empty
        End If
    End If
Next z

xWord.Application.Visible = True
End Sub

And in theory, that's how you can create labels in word from excel without saving as a datasource or using mailmerge. If you have part of a sheet of labels, you can just leave blank cells to cater for the gaps.
Just make sure you have the Microsoft Word Object Library referenced (Tools > references)

{Ah, smug mode}
Last edited:
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back