+ Reply to Thread
Results 1 to 9 of 9

Generating Word docs from Excel

  1. #1
    Jadedshade
    Guest

    Generating Word docs from Excel

    In excel how do I send data from fields to fill in spaces in a word document
    template? I would also like to create a button in excel so that this can be
    done easily.
    I want to make buttons that can be clicked in excel that will enter a large
    amount of specified text into the word document.
    I cannot figure out how to do any of this.
    Could someone give me examples of how to do these things?
    It would be much appreciated.
    Many thanks.

  2. #2
    JMB
    Guest

    RE: Generating Word docs from Excel

    One example. You have to set a reference to Word as the following code uses
    early binding (instead of late binding). In VBA, click Tools/References -
    check the Microsoft Word Library. In the word template, I usually set up
    placeholders, such as %NAME%, then use a search/replace operation to replace
    these placeholders with data from Excel. Below, instead of hardcoding the
    replacement text as I have done, you could refer to a particular value in one
    of your Excel worksheets, such as

    Worksheets("Sheet1").Range("A1").Value


    Sub Test()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document

    Set wdApp = New Word.Application
    Set wdDoc = wdApp.Documents.Open("I:\Excel\Test.doc")

    With wdDoc
    With .Content.Find
    .Text = "%NAME%"
    .Replacement.Text = "JEFF"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute Replace:=wdReplaceAll
    End With
    .Close savechanges:=True
    End With

    Set wdDoc = Nothing
    Set wdApp = Nothing

    End Sub

    "Jadedshade" wrote:

    > In excel how do I send data from fields to fill in spaces in a word document
    > template? I would also like to create a button in excel so that this can be
    > done easily.
    > I want to make buttons that can be clicked in excel that will enter a large
    > amount of specified text into the word document.
    > I cannot figure out how to do any of this.
    > Could someone give me examples of how to do these things?
    > It would be much appreciated.
    > Many thanks.


  3. #3
    JLatham
    Guest

    RE: Generating Word docs from Excel

    You say you are working with a Word document template. I assume that this
    means you have a standard Word document that needs to be updated with data
    from your Excel spreadsheet to create a new, unique Word document?

    Why not approach it from a different direction - that of having Word get
    information from Excel? Excel can be used as the data source for a Word Mail
    Merge. You could create the new Word document from a template. If you are
    going to do this often, you could modify your template to contain the Mail
    Merge field indicators for repeated use.

    If this all sounds confusing, I think this Excel 'tutorial' workbook will
    explain. It shows how to do this starting with a blank document - but during
    the process you are offered the choice to start with blank document or
    template, you'd just choose the template at that point. You'd need to set up
    a separate sheet in the workbook to grab the data to be placed into the Word
    document in the row layout that is needed in a Mail Merge. I think you'll
    understand when you look at this:
    http://www.jlathamsite.com/Teach/Wor...DataSource.xls

    "Jadedshade" wrote:

    > In excel how do I send data from fields to fill in spaces in a word document
    > template? I would also like to create a button in excel so that this can be
    > done easily.
    > I want to make buttons that can be clicked in excel that will enter a large
    > amount of specified text into the word document.
    > I cannot figure out how to do any of this.
    > Could someone give me examples of how to do these things?
    > It would be much appreciated.
    > Many thanks.


  4. #4
    JMB
    Guest

    RE: Generating Word docs from Excel

    would work much better to change

    ..Close savechanges:=True

    to
    ..SaveAs "YourNewFileName"
    ..Close

    If you have a lot of data that goes into different places in Word template,
    you should probably check into JLatham's mail merge example.


    "JMB" wrote:

    > One example. You have to set a reference to Word as the following code uses
    > early binding (instead of late binding). In VBA, click Tools/References -
    > check the Microsoft Word Library. In the word template, I usually set up
    > placeholders, such as %NAME%, then use a search/replace operation to replace
    > these placeholders with data from Excel. Below, instead of hardcoding the
    > replacement text as I have done, you could refer to a particular value in one
    > of your Excel worksheets, such as
    >
    > Worksheets("Sheet1").Range("A1").Value
    >
    >
    > Sub Test()
    > Dim wdApp As Word.Application
    > Dim wdDoc As Word.Document
    >
    > Set wdApp = New Word.Application
    > Set wdDoc = wdApp.Documents.Open("I:\Excel\Test.doc")
    >
    > With wdDoc
    > With .Content.Find
    > .Text = "%NAME%"
    > .Replacement.Text = "JEFF"
    > .Forward = True
    > .Wrap = wdFindContinue
    > .Format = False
    > .MatchCase = False
    > .MatchWholeWord = False
    > .MatchWildcards = False
    > .MatchSoundsLike = False
    > .MatchAllWordForms = False
    > .Execute Replace:=wdReplaceAll
    > End With
    > .Close savechanges:=True
    > End With
    >
    > Set wdDoc = Nothing
    > Set wdApp = Nothing
    >
    > End Sub
    >
    > "Jadedshade" wrote:
    >
    > > In excel how do I send data from fields to fill in spaces in a word document
    > > template? I would also like to create a button in excel so that this can be
    > > done easily.
    > > I want to make buttons that can be clicked in excel that will enter a large
    > > amount of specified text into the word document.
    > > I cannot figure out how to do any of this.
    > > Could someone give me examples of how to do these things?
    > > It would be much appreciated.
    > > Many thanks.


  5. #5
    blasds78
    Guest

    RE: Generating Word docs from Excel

    JLatham

    Your document was helpful, but I'm running into a different problem. I'm
    actually trying to create labels from an Excel document. I get to the point
    of merging to a new document, and it only prints one of the records. I can't
    get it to print multiple different labels. It wants to repeat the same
    record. Do you know how to do this?

    Please help...

    "JLatham" wrote:

    > You say you are working with a Word document template. I assume that this
    > means you have a standard Word document that needs to be updated with data
    > from your Excel spreadsheet to create a new, unique Word document?
    >
    > Why not approach it from a different direction - that of having Word get
    > information from Excel? Excel can be used as the data source for a Word Mail
    > Merge. You could create the new Word document from a template. If you are
    > going to do this often, you could modify your template to contain the Mail
    > Merge field indicators for repeated use.
    >
    > If this all sounds confusing, I think this Excel 'tutorial' workbook will
    > explain. It shows how to do this starting with a blank document - but during
    > the process you are offered the choice to start with blank document or
    > template, you'd just choose the template at that point. You'd need to set up
    > a separate sheet in the workbook to grab the data to be placed into the Word
    > document in the row layout that is needed in a Mail Merge. I think you'll
    > understand when you look at this:
    > http://www.jlathamsite.com/Teach/Wor...DataSource.xls
    >
    > "Jadedshade" wrote:
    >
    > > In excel how do I send data from fields to fill in spaces in a word document
    > > template? I would also like to create a button in excel so that this can be
    > > done easily.
    > > I want to make buttons that can be clicked in excel that will enter a large
    > > amount of specified text into the word document.
    > > I cannot figure out how to do any of this.
    > > Could someone give me examples of how to do these things?
    > > It would be much appreciated.
    > > Many thanks.


  6. #6
    JLatham
    Guest

    RE: Generating Word docs from Excel

    I don't do mail merges often, I'll have to check out some of the possible
    causes of this. Maybe another who does it often who knows right off the top
    of their head why it would only print one. It may be the process your are
    using also, that is, you might be missing a step in your process to print
    more than one label.

    "blasds78" wrote:

    > JLatham
    >
    > Your document was helpful, but I'm running into a different problem. I'm
    > actually trying to create labels from an Excel document. I get to the point
    > of merging to a new document, and it only prints one of the records. I can't
    > get it to print multiple different labels. It wants to repeat the same
    > record. Do you know how to do this?
    >
    > Please help...
    >
    > "JLatham" wrote:
    >
    > > You say you are working with a Word document template. I assume that this
    > > means you have a standard Word document that needs to be updated with data
    > > from your Excel spreadsheet to create a new, unique Word document?
    > >
    > > Why not approach it from a different direction - that of having Word get
    > > information from Excel? Excel can be used as the data source for a Word Mail
    > > Merge. You could create the new Word document from a template. If you are
    > > going to do this often, you could modify your template to contain the Mail
    > > Merge field indicators for repeated use.
    > >
    > > If this all sounds confusing, I think this Excel 'tutorial' workbook will
    > > explain. It shows how to do this starting with a blank document - but during
    > > the process you are offered the choice to start with blank document or
    > > template, you'd just choose the template at that point. You'd need to set up
    > > a separate sheet in the workbook to grab the data to be placed into the Word
    > > document in the row layout that is needed in a Mail Merge. I think you'll
    > > understand when you look at this:
    > > http://www.jlathamsite.com/Teach/Wor...DataSource.xls
    > >
    > > "Jadedshade" wrote:
    > >
    > > > In excel how do I send data from fields to fill in spaces in a word document
    > > > template? I would also like to create a button in excel so that this can be
    > > > done easily.
    > > > I want to make buttons that can be clicked in excel that will enter a large
    > > > amount of specified text into the word document.
    > > > I cannot figure out how to do any of this.
    > > > Could someone give me examples of how to do these things?
    > > > It would be much appreciated.
    > > > Many thanks.


  7. #7
    blasds78
    Guest

    RE: Generating Word docs from Excel

    My company just switched from Office NT to Office 2003. So, the other users
    don't know how to do this, either. I've looked through Office Online as much
    as I could, but haven't found an answer yet. Thanks for looking into this!

    Doug

    "JLatham" wrote:

    > I don't do mail merges often, I'll have to check out some of the possible
    > causes of this. Maybe another who does it often who knows right off the top
    > of their head why it would only print one. It may be the process your are
    > using also, that is, you might be missing a step in your process to print
    > more than one label.
    >
    > "blasds78" wrote:
    >
    > > JLatham
    > >
    > > Your document was helpful, but I'm running into a different problem. I'm
    > > actually trying to create labels from an Excel document. I get to the point
    > > of merging to a new document, and it only prints one of the records. I can't
    > > get it to print multiple different labels. It wants to repeat the same
    > > record. Do you know how to do this?
    > >
    > > Please help...
    > >
    > > "JLatham" wrote:
    > >
    > > > You say you are working with a Word document template. I assume that this
    > > > means you have a standard Word document that needs to be updated with data
    > > > from your Excel spreadsheet to create a new, unique Word document?
    > > >
    > > > Why not approach it from a different direction - that of having Word get
    > > > information from Excel? Excel can be used as the data source for a Word Mail
    > > > Merge. You could create the new Word document from a template. If you are
    > > > going to do this often, you could modify your template to contain the Mail
    > > > Merge field indicators for repeated use.
    > > >
    > > > If this all sounds confusing, I think this Excel 'tutorial' workbook will
    > > > explain. It shows how to do this starting with a blank document - but during
    > > > the process you are offered the choice to start with blank document or
    > > > template, you'd just choose the template at that point. You'd need to set up
    > > > a separate sheet in the workbook to grab the data to be placed into the Word
    > > > document in the row layout that is needed in a Mail Merge. I think you'll
    > > > understand when you look at this:
    > > > http://www.jlathamsite.com/Teach/Wor...DataSource.xls
    > > >
    > > > "Jadedshade" wrote:
    > > >
    > > > > In excel how do I send data from fields to fill in spaces in a word document
    > > > > template? I would also like to create a button in excel so that this can be
    > > > > done easily.
    > > > > I want to make buttons that can be clicked in excel that will enter a large
    > > > > amount of specified text into the word document.
    > > > > I cannot figure out how to do any of this.
    > > > > Could someone give me examples of how to do these things?
    > > > > It would be much appreciated.
    > > > > Many thanks.


  8. #8
    Registered User
    Join Date
    07-06-2006
    Posts
    2
    I'm usually a question asker but .. since it doesnt seem anyone has suggested this .. It seems you have not completed your merge .. step 4: Arrange your labels .. Replicate labels, create your first label & click update labels.. All the labels after the first one should include: «Next Record»

  9. #9
    JLatham
    Guest

    Re: Generating Word docs from Excel

    I'm inclined to agree with you - goes with what I said before about
    "something in the process' not being right. Also your comment about using
    <<Next Record>> is something that was going thru my head when I wrote that
    previous post - I just needed to go back and check when that needs to be used
    (which I haven't done yet).

    Basically I think you've probably solved the problem and I'm going to treat
    this as a kind of dead issue unles blasds78 replies and tells us that your
    suggestions haven't cured his/her problem.

    "pjm" wrote:

    >
    > I'm usually a question asker but .. since it doesnt seem anyone has
    > suggested this .. It seems you have not completed your merge .. step
    > 4: Arrange your labels .. Replicate labels, create your first label &
    > click update labels.. All the labels after the first one should
    > include: «Next Record»
    >
    >
    > --
    > pjm
    > ------------------------------------------------------------------------
    > pjm's Profile: http://www.excelforum.com/member.php...o&userid=36132
    > View this thread: http://www.excelforum.com/showthread...hreadid=557820
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1