Create word doc from excel data

Microsoft Excel 2003 (full product)
February 3, 2010 at 02:38:41
Specs: Windows XP
Hi guys,
I have an excel file where row 2 on each coumn has a persons names in it. What I want a macro to do is:
Create a new word doc
Paste the data in that cell into the word doc
Save the word doc under the name of the cell (ie Mr J Downes.doc)
Create a hyperlink in a column across from the name with a hyperlink that will open that file


See More: Create word doc from excel data

Report •

February 3, 2010 at 07:08:51

If row 2 has names in every column, it is not possible to put the hyperlinks in a column across from the name.
I have assumed that the names are in every row in column B and that the hyperlinks are in column C.

This macro works by first identifying the number of rows containing data in column B.
Then it tests the adjacent cells in Column C for a hyperlink.
If there is no hyperlink in the adjacent cell the macro creates a new Word document, then names it with the name in the cell in Column B and saves it. Finally it creates a hyperlink.

The path to these files is contained in the program and you will have to change it to match your folder structure/path.

In the worksheet containing the names create a command button and attach the macro to it as follows:

On the names Worksheet, create a command button from the Control Toolbox toolbar.
(If this isn't visible, right click on an existing toolbar and check the Control Toolbox).
Select the button Icon and draw a button
Right-click the button and select Command Button - Edit and change the name to 'Create Documents' or some other useful name.
Right-click the button again and select View Code
From the VB window menu bar select Tools - References ...
In the 'References - VBA Project' dialog box, find 'Microsoft Word 11.0 Object Library' and check the check box, click OK
(If you select Microsoft Word 11.0 this will work for Word 2003 and later. If you are using an older version of Word look for an earlier version number. Also 12.0 will only work with Word 2007).

In the code window on the right side enter this:

Option Explicit
Private Sub CommandButton1_Click()
Dim rngFirst As Range
Dim rngLast As Range
Dim rngCell As Range
Dim appWord As Word.Application
Dim strPath As String

On Error GoTo ErrHnd

'create link to word
Set appWord = Word.Application

'set path for Word documents - include closing "\"
strPath = "C:\Temp\"

With ActiveSheet
    'set address of first name in column B
    Set rngFirst = .Range("B2")
    'find last record in column B
    Set rngLast = .Range("B65534").End(xlUp)
    'loop through all cells with data in column B
    For Each rngCell In Range(rngFirst.Address, rngLast.Address)
        'test if adjacent cell in column C has a hyperlink
        If rngCell.Offset(0, 1).Hyperlinks.Count = 0 Then
            With appWord
                'no hyperlink, so create a Word doc
                'save new document with name from column B
                .Documents(.Documents.Count).SaveAs _
                    Filename:=strPath & rngCell.Text & ".doc"
                'add text to the document
                .Documents(rngCell.Text & ".doc").Activate
                .ActiveDocument.Content.InsertAfter rngCell.Text
                'close document saving changes
                .Documents(rngCell.Text & ".doc").Close SaveChanges:=wdSaveChanges
            End With
            'create text in cell in Column C
            rngCell.Offset(0, 1).Value = "Open " & rngCell.Text & "'s document"
            'create hyperlink
            rngCell.Offset(0, 1).Hyperlinks.Add Anchor:=rngCell.Offset(0, 1), _
                Address:=strPath & rngCell.Text & ".doc"
        End If
    Next rngCell
    'size column C to fit all new hyperlinks
    Range(rngFirst.Offset(0, 1).Address, rngLast.Offset(0, 1).Address). _
End With
Exit Sub

'error handler
End Sub

Note that Private Sub CommandButton1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Private Sub CommandButton1_Click().
Some lines of code have been split onto two lines for ease of viewing, using the line continuation character "_". This should work 'as is' just copy and paste, or you could remove the "_" and bring the code back to one line.

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.
Exit design mode (first icon on the Controls Toolbox toolbar).

Make sure MS Word is open.

Click the 'Create Documents' button.

Note that because of the way that the code identifies the last record in column B, all cells in column B below the last record must be empty.

You could add code to ensure that MS Word was open, at present if Word is not open the Macro will fail.


Report •
Related Solutions

Ask Question