[RESOLVED] Fun One Here! Exporting Information from Excel to Word

[IGPOD]

Okay... I have predefined content that I need to insert into an excel macro which will auto populate a word document.

Description:
I have about 96 mail-outs that I have to put together, each with a different contractor's name, address, contract number etc.

Most of the content is static, but I need to create a macro that will take the information from my spreadsheet and create a custom letter to the contractors.

Request:
If you could identify where I can place my static content in the code as: "[PLACE STATIC CONTENT HERE]" it would be much appreciated.

One more thing:
To simplify I need this format:

Reference: [Contract Number]

[Contractor Name]
[Street Address]
[City], [State] [Zip]

Dear Mr./Mrs. [Contractor Name]

[Static Content]



EACH OF THE ABOVE IN BRACKETS HAS IT'S OWN CELL REFERENCE.

Thank you in advance, I know someone can do this.

[vbfbryce]

I know a lot of people can...have you tried anything yet? Can we see?

[IGPOD]

I'm not familiar with VBA code from excel to word... I'm working on something now, I'll post soon. You know these things can be puzzles.

I'm reworking some code I created for an automatic emailer, but I don't know enough about VBA to formulate correct code; very frustrating, but understandable.

[IGPOD]

This is the best I can come up with for now, but it's not working...

Sub PopWordDoc()

Dim WordApp As word.Application
Dim Msg As String
Dim cell As Range

'Populates Word Document
Set WordApp = New word.Application


Msg = ""
Msg = Msg & "Reference Contract Number:"
Msg = Msg & Range("E22").Text & vbCrLf & vbCrLf
Msg = Msg & Range("E3").Text & vbCrLf
Msg = Msg & Range("E6").Text & vbCrLf
Msg = Msg & Range("E9").Text("E12").Text("E15").Text & vbCrLf & vbCrLf

Msg = Msg & "Dear Mr./Mrs. "
Msg = Msg & Range("E3").Text

Msg = Msg & [Static Content Here]

[vbfbryce]

OK, will take a look shortly

[TnTinMN]

I recommend that you create and use Named Ranges in your spreadsheet for this as it will make your code easier to maintain.

Here is one possible way to do this.

    Dim app As New Word.Application
    app.Visible = True
    
    Dim doc As Word.Document
    Set doc = app.Documents.Add()
    
    With doc.Styles(Word.wdStyleNormal)
        .Font.Name = "Arial"
        .Font.Size = 12
        .ParagraphFormat.Alignment = Word.wdAlignParagraphJustify
        Dim tabs As Single
        For tabs = 0.5 To 4 Step 0.5
            .ParagraphFormat.TabStops.Add Position:=app.InchesToPoints(tabs), Alignment:=Word.wdAlignTabLeft
        Next tabs
    End With
    
    Dim para As Word.Paragraph
    Set para = doc.Paragraphs(1) ' select 1st paragraph
    
    para.LineSpacing = doc.Styles(Word.wdStyleNormal).Font.Size
    para.SpaceAfter = 0
    para.SpaceBefore = 0
    
    Dim text As String
    
    text = "Reference Contract Number:  " & Range("Contract_No").text & vbCrLf & vbCrLf & _
           Range("Contractor_Name").text & vbCrLf & _
           Range("Address").text & vbCrLf & _
           Range("City").text & ", " & Range("State").text & vbTab & Format(Range("Zip").Value, "0####") _
           & vbCrLf & vbCrLf & "Dear Mr./Mrs. " & Range("Contractor_Name").text & ":" & vbCrLf & vbCrLf
           
    para.Range.text = text
    
    ' each vbcrlf starts a new paragraph that inherits from the previous format
    
    Set para = doc.Paragraphs(doc.Paragraphs.Count) ' get last paragraph
    
    para.Range.text = "static content"

'    doc.SaveAs Filename:="filename.docx", FileFormat:=Word.WdSaveFormat.wdFormatXMLDocument
'    doc.Close (Word.wdDoNotSaveChanges)
'    app.Quit
    
    Set para = Nothing
    Set doc = Nothing
    Set app = Nothing

[IGPOD]

TnTinMN,

Thanks for the code, however, It's not working despite defining the named ranges as suggested. GREAT TIP BY THE WAY, THANK YOU!

I'm getting a "Compile Error: User-defined type not defined" message; the name of the Macro: "PopWordDoc()" is highlighted yellow and the first line of your code after Dim: "...app As New Word.Application" is highlighted blue.

I don't really know how to trouble shoot this, but for some reason -- even in other code that I've used -- that line isn't accepted. I don't know why? Any suggestions?

Thanks.

[vbfbryce]

You need to add a reference to the Microsoft Word Object Library. In the VBA window, click on Tools, then References. Scroll down until you find it, and check it.

[TnTinMN]

TnTinMN,

Thanks for the code, however, It's not working despite defining the named ranges as suggested. GREAT TIP BY THE WAY, THANK YOU!

I'm getting a "Compile Error: User-defined type not defined" message; the name of the Macro: "PopWordDoc()" is highlighted yellow and the first line of your code after Dim: "...app As New Word.Application" is highlighted blue.

I don't really know how to trouble shoot this, but for some reason -- even in other code that I've used -- that line isn't accepted. I don't know why? Any suggestions?

Thanks.

I had assumed that since you had used the "Word" namespace in your posted code that you had added a reference to "Microsoft Word XX.Y Object Library" (XX.Y depends on your installed version).

Go to the menu and click on Tools->References and add that library and all should be good.

[IGPOD]

VbfBryce, TnTinMN,

I did not have that selected, I just did, but it's still not working.

Sub DCH_Pop_Doc()

Dim app As New Word.Application
    app.Visible = True
    
    Dim doc As Word.Document
    Set doc = app.Documents.Add()
    
    With doc.Styles(Word.wdStyleNormal)
        .Font.Name = "Arial"
        .Font.Size = 12
        .ParagraphFormat.Alignment = Word.wdAlignParagraphJustify
        Dim tabs As Single
        For tabs = 0.5 To 4 Step 0.5
            .ParagraphFormat.TabStops.Add Position:=app.InchesToPoints(tabs), Alignment:=Word.wdAlignTabLeft
        Next tabs
    End With
    
    Dim para As Word.Paragraph
    Set para = doc.Paragraphs(1) ' select 1st paragraph
    
    para.LineSpacing = doc.Styles(Word.wdStyleNormal).Font.Size
    para.SpaceAfter = 0
    para.SpaceBefore = 0
    
    Dim text As String
    
    text = "Reference Contract Number:  " & Range("Contract_Number").text & vbCrLf & vbCrLf & _
           Range("Contractor_mngr").text & vbCrLf & _
           Range("Address").text & vbCrLf & _
           Range("City").text & ", " & Range("State").text & vbTab & Format(Range("Zip").Value, "0####") _
           & vbCrLf & vbCrLf & "Dear Mr./Mrs. " & Range("Contractor_Name").text & ":" & vbCrLf & vbCrLf
           
    para.Range.text = text
    
    ' each vbcrlf starts a new paragraph that inherits from the previous format
    
    Set para = doc.Paragraphs(doc.Paragraphs.Count) ' get last paragraph
    
    para.Range.text = "static content"

'    doc.SaveAs Filename:="filename.docx", FileFormat:=Word.WdSaveFormat.wdFormatXMLDocument
'    doc.Close (Word.wdDoNotSaveChanges)
'    app.Quit
    
    Set para = Nothing
    Set doc = Nothing
    Set app = Nothing
    End Sub

I really do appreciate your help in this, it's obviously above my knowledge level, but I'm learning a lot from you two.

[vbfbryce]

you're getting the same error? or something else?

[IGPOD]

vbfbryce,

I went back in, I had selected "Microsoft Office" instead of "Microsoft Word"...

Now that I have that reference, I ran the macro that TnTinMN posted (with my named ranges inserted) and I get this:

Microsoft Visual Basic "400":


I looked it up, it seems that something in the code isn't compatible with my version of Microsoft Office (I may be mistaken) ... I'm running '07.

[vbfbryce]

How are you creating your "named ranges?" Do they refer to only 1 cell, or multiple cells?

That code worked for me.

[TnTinMN]

I looked it up, it seems that something in the code isn't compatible with my version of Microsoft Office (I may be mistaken) ... I'm running '07.

The example I provided was written using 2007.

I suspect that you may have a named range typo. Try changing this:

    text = "Reference Contract Number:  " & Range("Contract_Number").text & vbCrLf & vbCrLf & _
           Range("Contractor_mngr").text & vbCrLf & _
           Range("Address").text & vbCrLf & _
           Range("City").text & ", " & Range("State").text & vbTab & Format(Range("Zip").Value, "0####") _
           & vbCrLf & vbCrLf & "Dear Mr./Mrs. " & Range("Contractor_Name").text & ":" & vbCrLf & vbCrLf
           
    para.Range.text = text

to this:

'    text = "Reference Contract Number:  " & Range("Contract_Number").text & vbCrLf & vbCrLf & _
         '  Range("Contractor_mngr").text & vbCrLf & _
         '  Range("Address").text & vbCrLf & _
         '  Range("City").text & ", " & Range("State").text & vbTab & Format(Range("Zip").Value, "0####") _
         '  & vbCrLf & vbCrLf & "Dear Mr./Mrs. " & Range("Contractor_Name").text & ":" & vbCrLf & vbCrLf
           
    para.Range.text = "I got a Word Document" & VbCrLf 'text

If that works then verify the named ranges names. (Try saying that 10 times real fast!).

[IGPOD]

TnTinMN,

The problem with the code I was using was what you suspected, a named range issue. I had "Contract_Mngr" you had "Contractor_Name" which to me is synonymous to one another, so my bad.

The Error 400 disappeared, and now I have this fella showing up.


Here is the code I have which is yielding me this message:

Sub DCH_Pop_Doc()

Dim app As New Word.Application
    app.Visible = True
    
    Dim doc As Word.Document
    Set doc = app.Documents.Add()
    

    text = "Reference Contract Number:  " & Range("Contract_Number").text & vbCrLf & vbCrLf & _
           Range("Contract_mngr").text & vbCrLf & _
           Range("Address").text & vbCrLf & _
           Range("City").text & ", " & Range("State").text & vbTab & Format(Range("Zip").Value, "0####") _
           & vbCrLf & vbCrLf & "Dear Mr./Mrs. " & Range("Contract_mngr").text & ":" & vbCrLf & vbCrLf
           
    para.Range.text = "I got a Word Document" & vbCrLf 'text

End Sub

[vbfbryce]

do you get the error on the "text =..." line?

[TnTinMN]

If you did not abbreviate the last code you posted, you have deleted the "para" definition. Add "Option Explicit" to the top of your module and set Require Variable Declaration to checked on from the menu Tools->Options->Editor Tab. This setting will not change any existing Modules, but will automatically insert "Option Explicit" on all new modules.

[IGPOD]

No, the word document pops up, but there is nothing inserted into the document, it's completely blank.

[vbfbryce]

show us your entire code one more time, please

[IGPOD]

vbfbryce, TnTinMN,

Here is the New Error Message:


Here (below) is the code I'm using; I defined the para and the text... the word doc is popping up, but there is no content.

Option Explicit
Sub DCH_Pop_Doc()

Dim app As New Word.Application
    app.Visible = True
    
    Dim TEXT As String
    Dim PARA As Word.Paragraph
    Dim doc As Word.Document
    Set doc = app.Documents.Add()
    Set PARA = doc.Paragraphs
       

    TEXT = "Reference Contract Number:  " & Range("Contract_Number").TEXT & vbCrLf & vbCrLf & _
           Range("Contract_mngr").TEXT & vbCrLf & _
           Range("Address").TEXT & vbCrLf & _
           Range("City").TEXT & ", " & Range("State").TEXT & vbTab & Format(Range("Zip").Value, "0####") _
           & vbCrLf & vbCrLf & "Dear Mr./Mrs. " & Range("Contract_mngr").TEXT & ":" & vbCrLf & vbCrLf
           
    PARA.Range.TEXT = "I got a Word Document" & vbCrLf 'text

End Sub

[TnTinMN]

Set PARA = doc.Paragraphs

should be : Set PARA = doc.Paragraphs(1)

[IGPOD]

TntinMN,

Thanks... there is however one more issue... this is all I have when I run the Macro:


All the other stuff
[Contract Manager]
[Address]
[City]...

All missing

[vbfbryce]

because of this:

para.Range.text = "I got a Word Document" & vbCrLf 'text

[IGPOD]

Gentlemen (and/or Ladies),

Thank you so much for your help. This problem is resolved, you TWO ROCK! Here is a copy of the final code which works like a charm.

Option Explicit
Sub DCH_Pop_Doc()

Dim app As New Word.Application
    app.Visible = True
    
    Dim TEXT As String
    Dim PARA As Word.Paragraph
    Dim doc As Word.Document
    Set doc = app.Documents.Add()
    Set PARA = doc.Paragraphs(1)
       

    PARA.Range.TEXT = "Reference Contract Number:  " & Range("Contract_Number").TEXT & vbCrLf & vbCrLf & _
           Range("Contract_mngr").TEXT & vbCrLf & _
           Range("Address").TEXT & vbCrLf & _
           Range("City").TEXT & ", " & Range("State").TEXT & vbTab & Format(Range("Zip").Value, "0####") _
           & vbCrLf & vbCrLf & "Dear Mr./Mrs. " & Range("Contract_mngr").TEXT & ":" & vbCrLf & vbCrLf
           
    PARA.Range.TEXT = "I got a Word Document" & vbCrLf 'text

End Sub

[vbfbryce]

I don't see how that can work. Don't you want:

para.Range.text = "I got a Word Document" & vbCrLf  & text

[GBeats]

Hi, sorry to jump in here, but i thought i would add some ideas

I havent done this using VBA as its a pain in the butt, but i recently did something similar using VB.NET with an Excel Addin.

my first step was extracting all data using the array you get with a ranges.value2 property, if speed isnt an issue just use ranges

so basically if you have a sheet with data somewhere just extract if and put it somewhere, i used dictionaries, you will use arrays if your using vba

next is create a word template and use bookmarks to set points of insertion for your data so for example a field for names might have <NAME> as temporary data you set that whole range as a bookmark named "Name".......

in the end you will have a list of bookmarks representing ranges with descriptive or coded names

then you tie up the data you extracted in excel with the names of the bookmarks, id recommend a user defined type for the excel array holding the data so each array element has 2 variables, a name and the data, the name should be the same as the word bookmark for simplicity.

then loop through the word bookmarks, test each one with the array names, if its matched then replace the bookmark range with the data.

easy then save the document and be sure to use templates for your template, so it isn't edited by mistake.

the code is straight forward just simple loops.

[IGPOD]

vbfbryce,

the " 'text " is an explanation of what is in "" ... you can remove that; but I'm sure you're already aware. That code above is a working copy, I just repeated the "para.range.text = "*****" coupled with " & vbcrlf " to shape the paragraphs and additional content.

Thanks Everybody!

[vbfbryce]

my bad...I only looked at the last line; missed the previous ones!