Creating Address Labels - without saving anything

[George J]

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.

 

[George J]

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

xWord.Documents.Add
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
    Else
        If Not z.Row = Range("a65536").End(xlUp).Row Then
            xWord.Selection.MoveRight Unit:=wdCell
            xTabCount = Empty
        End If
    End If
Next z

xBlankDoc.Close
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}