Consulting

Results 1 to 14 of 14

Thread: Excel cell to Word Autotext

  1. #1

    Excel cell to Word Autotext

    Hello hopefully somebody can help me out. I am trying to get some excel data that I read into VBA to a Word template with Autotext or Form Fields in the template. I want to make certain Form Fields equal to the data from Excel. I look around and try a different way to do it but always get different runtime errors. I am a just learning how to use VBA in Word but I am decent in VBA in Excel. Well there the last according to some site suppose to work.



    ExcelInformation = WordDoc.FormFields(WordDoc.Bookmarks(?x?).Name).Result



    But I get a runtime error from it about no Object.



    Well any help anybody can give will be great. Anybody got a opioion on a good book to learn VBA in Word I take that also. Thanks again for any help anybody can give thanks.

  2. #2
    Site Admin
    The Princess
    VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Yes, use bookmarks. Try this KB entry:
    http://vbaexpress.com/kb/getarticle.php?kb_id=381
    ~Anne Troy

  3. #3

    Excel to Word Autotext

    Thanks for the info.

  4. #4
    Oh ok i have tried it and this is what i get when try
    [VBA] Set WordDocument = CreateObject("Word.Application")
    WordDocuments.documents.Open ("path")
    WordDocuments.Visible = True
    WordDocument.formfield("name").Result = ExcelInfo
    [/VBA] i get run time error 438-Object dosen't support this proptery or method.

    To let you know what going on i am trying to do is to read in excel data from the cells, then open a word template, then fill the word template formfield with the excel data while running the VBA code in excel. I got as far as open the word template but for some reason i cant get the excel data into the word template formfields. Any help will be greatly appreciated. Thanks for anything people can do or suggest.

  5. #5
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Did you NAME the Word formfields (bookmarks) the SAME as the named ranges in Excel?
    ~Anne Troy

  6. #6
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    josephemery
    I have read through your problem twice now and I downloaded Kens kb entry that Dreamboat mentioned earlier in this thread. I have to tell you it sounds like the kb entry does exactly what you are trying to accomplish.



    If you haven't done it I suggest that you download the example file that will be at the bottom of the kb page and when you unzip it, run the excel file and the BCMerge Macro that is in it. Be sure to leave the pushmerge.dot or template file in the same directory as the excel file. After you run it and you see the results, you can go to file-open and open the pushmerge.dot file to see how it is set up.



    I apologize if you've already done that and are trying to do something different. If so it might help to upload a sample (you have to zip it up first) so some of the regulars in this forum can take a look at it. Be sure to remove any personal or proprietary info first.

    ps. when you post code it makes them easier to read if you select the code and click on "add vba tags" from the format bar. I edited your vba in your post #4 in this thread as an example.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  7. #7
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    You had better make sure - as you are running this code from Excel - that you have the WORD reference library actually referenced! Otherwise....you should get that error.

  8. #8
    Administrator
    VP-Knowledge Base VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by fumei
    You had better make sure - as you are running this code from Excel - that you have the WORD reference library actually referenced! Otherwise....you should get that error.
    The code posted is late binding so no reference needed!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  9. #9
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by josephemery
    [VBA] Set WordDocument = CreateObject("Word.Application")
    WordDocuments.documents.Open ("path")
    WordDocuments.Visible = True
    WordDocument.formfield("name").Result = ExcelInfo
    [/VBA] i get run time error 438-Object dosen't support this proptery or method.
    Ok some spelling errors here!

    You Set WordDocument to creat a Word Application Object (Server)

    Then you go ahead with this line
    WordDocuments.documents.Open ("path")

    The object you set is Without a "s"
    so it should be:[vba]
    WordDocument.documents.Open ("path")[/vba]

    And I do hope in your application there is a qualified path instead of "path" in the Open statement or a variable carying that? And then you should write it as:[vba]
    WordDocuments.documents.Open (path)[/vba] (If path is your string variable)

    And path would be a terrible name for a variable cause its a VBA property so you would overwrite the one that VBA is using...(Always think about naming strings)

    Also on the visible statement there is one s to much.

    Further more what is: ExcelInfo (A string variable?)

    In other words can you post your full code cause the code should work in general.
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  10. #10

    Excel cellto Word Autotext

    Thanks for the help so far i try to download the zip file and understand the code, but some reason i cant figure out the code for now. But i understand most of it.
    But the ExcelInfo can be numbers and Test, so i guess they excel info would be variant file type. The excel info is name and adress and number information of a company or person. So all that info will need to be put in a Word Template for email and saving the info in paper files for people to use for contact information. The path is the actual path i just used that to show there was a path for the document.
    I just want to Thanks for the help, since i still learning how to write VBA for word everything ben helpful. If anybody got a good book to recommend to learn how to write VBA for word I would greatful for that.

  11. #11
    VBAX Wizard
    Joined
    May 2004
    Posts
    6,713
    Location
    If you can find it:

    Word 2000 Developer's Handbook - Guy Hart-Davis, published by Sybex. While for 2000, most of the VBA is still valid for 2002, with some more changes for 2003. Still, as it is older you can probably find it cheap. Excellent for getting a solid grasp of Word.

    VBA Developer's Handbook - Getz, Gilbert, published by Sybex. Tons and tons of stuff for VBA. Lots of sample code.

  12. #12

    Excel to Word template

    Hello Everybody



    I am writing the code for the program and I am getting a run time error of 438- object doesn?t support this method or property. What it look likes is that Excel VBA doesn?t recognizes ActiveDocument command. I am not sure, but here my code. If it helpfully I am using Office 2003. ExcelInfo can be either string or number depends on the info.





    [VBA]

    Dim WdApps As Object
    Set WdApps = CreateObject("Word.Application")
    WdApps.documents.Open "path"
    WdApps.Visible = True
    Set Activedocuments = WdApps
    'put the excel info the word doc
    Set ExcelInfo = Activedocuments.Bookmarks("name").Range


    [/VBA]





    Any help given will be greatly appreciated. Thanks



    Joseph

  13. #13

    Excel to word template (mistake i caught)

    Hey Everybody I caught a mistake it suppose to be ActiveDocument not ActiveDocuments. I changed it and i get the rentime of 424-Object required. So if anybody can figure out what i am doing wrong or point some where to go i would be gratefully. Thanks again.

    Quote Originally Posted by josephemery
    Hello Everybody



    I am writing the code for the program and I am getting a run time error of 438- object doesn?t support this method or property. What it look likes is that Excel VBA doesn?t recognizes ActiveDocument command. I am not sure, but here my code. If it helpfully I am using Office 2003. ExcelInfo can be either string or number depends on the info.





    [VBA]

    Dim WdApps As Object
    Set WdApps = CreateObject("Word.Application")
    WdApps.documents.Open "path"
    WdApps.Visible = True
    Set Activedocument = WdApps
    'put the excel info the word doc
    Set ExcelInfo = Activedocuments.Bookmarks("name").Range


    [/VBA]





    Any help given will be greatly appreciated. Thanks



    Joseph

  14. #14

    EXcel to Word template

    Hello Everybody

    Thanks for the Help so far I got the problem of transferring the date form the excel sheet to the word template. I am now running into the problem of that I have more bookmarks in the template than data transferring. I want to protect the extra bookmark in the template, so people can fill in the missing data. I try to protect the template but the data wont transfer with a protected template. So I been trying to insert the data and then protect the template for further use. I have try to use

    [VBA]WordTemplateName.Protect [/VBA]

    but that wont work. Then I tried to write the code in the template that protect the document then try to call the macro of the temple from the excel macro by

    [VBA]Application.Run ?WordTemplateName.ModuleName.MacroName? [/VBA]

    But the macro wont run. I am not sure what the problem. Hopefully some will be able to help. Thanks.

Posting Permissions

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