Export data to Word

**piersuk** · 08-30-2007, 02:20 PM

Sorry if this has been asked before, I have tried to find a similar post.

I'm a complete newbie when it comes to VB, Excel and Word other than the most basic of use.

My question is this, I have a word document which I would like to automatically populate data from Excel into.

For instance

------A---------B----------C--------D-----------E----------F

1----Fred------Blue------12345-----Left-------£55.55-----abcde
2----John------Red------54321-----Right------£99.99-----edcba

I would like to extract the data from A1, B1, C1, E1 into one word document.

Then work down the list and extract A1, B1, C1, E1 into another word document.

The colums I wish to export will be the same, it's only the row that will change. Ideally I would like to add a button at G1, G2, G3, G4 etc and by the clicking of this button the word document is created.

I would then be looking at adding the Word document automatically to an email to the person in the A column, their names would already be stored in Outlooks address book.

If I'm asking too much then please tell me, if the facility to do this is too difficult equally let me know. If however, there is a place on the net where someone has done the hard work and is offering the code, then please let me know.

Any help appreciated.

Piers

**Kaak** · 08-31-2007, 02:46 AM

This maybe a start


Sub CallTest()

    Dim i As Integer

    For i = 1 To 5    
        Test (i)    
    Next i

End Sub


Sub Test(ByVal iRow As Integer)

    Dim WordApp As Object

    Set WordApp = CreateObject("Word.Application")
    
    With WordApp
    
         .Documents.Open "C:\Test.doc"
         .Visible = True
    
         .ActiveDocument.FormFields("Field0").Result = Range("A" & iRow).Value
         .ActiveDocument.FormFields("Field1").Result = Range("B" & iRow).Value
         .ActiveDocument.FormFields("Field2").Result = Range("C" & iRow).Value
         .ActiveDocument.FormFields("Field3").Result = Range("D" & iRow).Value
   
         .ActiveDocument.SaveAs ("C:\TestRow" & iRow & ".doc")
         .ActiveDocument.Close 
    
    End With
    
    WordApp.Quit
    Set WordApp = Nothing

End Sub

**piersuk** · 08-31-2007, 01:18 PM

Very kind, thanks.

What do i do with the code though? I did say I was a complete novice

sorry to sound daft

Thanks

Piers

**piersuk** · 09-03-2007, 06:14 PM

Originally Posted by Kaak

This maybe a start


Sub CallTest()

    Dim i As Integer

    For i = 1 To 5    
        Test (i)    
    Next i

End Sub

Thanks Kaak,

I managed to use the code and running the macro I see how it should work, trouble is that all it does is open a blank word document and does not send any of the data from A1, D1, E1 etc across.

Suggestions appreciated.

Thanks
Piers

**Kaak** · 09-05-2007, 08:34 AM

These lines assume youo have inserted 4 formfields in your document
and fills these with data from row iRow

         .ActiveDocument.FormFields("Field0").Result = Range("A" & iRow).Value
         .ActiveDocument.FormFields("Field1").Result = Range("B" & iRow).Value
         .ActiveDocument.FormFields("Field2").Result = Range("C" & iRow).Value
         .ActiveDocument.FormFields("Field3").Result = Range("D" & iRow).Value

Export data to Word

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Export data to Word

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions