Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 34

Thread: Solved: Excel VBA invokes Word

  1. #1

    Solved: Excel VBA invokes Word

    Hi,

    I'm a newbie, so excuse my ignorance... It was very difficult for me to get here! I write mainframe assembler... OO VBA is just a little different!

    The objective is to use a VBA macro in Excel to kick off a Word document, and to change some words (placeholders) in this document to values that come from the spreadsheet.

    The included code only attempts to make 1 change, and it dies with an error 449 from VBA. Here is the code:

    [VBA]
    Private Sub WordTest()
    Dim rcMsg As Integer
    Dim tCell As Long

    Dim wrdApp As Word.Application
    On Error GoTo errHandler

    rcMsg = MsgBox("WordTest 2.3 has been entered")

    Set wrdApp = New Word.Application

    ' wrdApp.Visible = True

    rcMsg = MsgBox("WordTestBefore Open")

    wrdApp.Documents.Open Filename:="c:\chuck\Recap.doc"

    rcMsg = MsgBox("WordTest Before Selection.Find.Clear...!")

    wrdApp.Selection.Find.ClearFormatting

    rcMsg = MsgBox("WordTest Before FIND.Repl!")

    wrdApp.Selection.Find.Replacement.ClearFormatting

    rcMsg = MsgBox("WordTest Before WITH!")

    With wrdApp.Selection.Find
    .Text = "AAAAAQ01"
    .Replacement.Text = "Charleston Square"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With

    rcMsg = MsgBox("WordTest Before Selection.Find.Execute!")

    wrdApp.Selection.Find.Execute

    rcMsg = MsgBox("WordTest Before With wrdApp.Selection!")

    With wrdApp.Selection
    If .Find.Forward = True Then
    .Collapse Direction:=wdCollapseStart
    Else
    .Collapse Direction:=wdCollapseEnd
    End If
    .Find.Execute Replace:=wdReplaceOne
    If .Find.Forward = True Then
    .Collapse Direction:=wdCollapseEnd
    Else
    .Collapse Direction:=wdCollapseStart
    End If
    .Find.Execute
    End With

    rcMsg = MsgBox("WordTest... Before Prt!")

    wrdApp.Application.PrintOut Filename:="", Range:=wdPrintAllDocument, Item:= _
    wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
    Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
    PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0

    rcMsg = MsgBox("Wordtest- Before Quit")

    wrdApp.Quit savechanges:=False

    rcMsg = MsgBox("Wordtst- Before wrdApp Nothing")

    Set wrdApp = Nothing

    tCell = Cells(1, 1)

    rcMsg = MsgBox(tCell) 'Another test

    rcMsg = MsgBox("WordTest is ending.")

    GoTo endIt

    errHandler:
    rcMsg = MsgBox("The error handler was entered!")

    wrdApp.Quit savechanges:=False

    Set wrdApp = Nothing

    rcMsg = MsgBox("The error handler issued Quit!")


    endIt:
    End Sub
    [/VBA]
    Edit Lucas: VBA tags added to code
    -------------------------------------------

    The program dies at:

    wrdApp.Selection.Find.Replacement.ClearFormatting

    The message "WordTest Before FIND.Repl!" is the last message displayed before going into the error handler.

    The word VBA came from recording a macro under Word to do the first change (Change "AAAAAQ01" to "Charleston Square"). Then I copied this to my XL macro and added the wrdApp to the object methods. This was the result of another problem, and I think this means the methods could be Word or XL and "wrdApp" makes the decision of which method to use. I may be misusing "method" as my OO terminology is not very good.

    It just seems to me that this has to be something pretty simple. If I remove all the "change" code, Word happily prints my document.

    The long names "AAAAAQ01" were chosen to make the spacing easier to "guess" on the Word document. Once I get this to work, I'll probably set this to just "Q01". I tried to use "bookmarks" in ".dot" file to no avail in Word. Perhaps my approach could be improved. The vast majority of the 15 changes to be made to the document is numbers that XL is calculating.
    Only one change is made in this example.

    TIA

  2. #2
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    I think if you use the Word selection object, you have to make the Word application visible first. I generally try to work with the Word range object instead. There are MSDN articles that describe how to do this, but I am having trouble finding them, because Microsoft changes the MSDN web site structure about once a month. Here are articles written for Office 2007:

    Working with the Selection Object
    http://msdn2.microsoft.com/en-us/library/bb221280.aspx

    Working with Range Objects
    http://msdn2.microsoft.com/en-us/library/bb221274.aspx

    Here are the corresponding articles for Office 2000:

    Working with the Selection Object
    http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

    Working with Range Objects
    http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

    (I know the URLs say office.10, but the articles are in the office 9 library).
    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com
    _______

  3. #3

    Thanks for the references

    Thank you for addressing my problem. I appreciate your time.

    It seems strange to me that the "macro recorder" would create code that would not work from outside its environment. Perhaps my problem was not knowing this fact in the first place.

    I originally addressed this problem using "bookmarks", but I was unable to figure out how to get rid of the bookmarks or the command to "change" the bookmark to text. From what I read in the MS documents, you can set a range and then replace text.

    Ideally, the range sounds like a good way to address this problem. I would insert the property name and date, and then the dollar amounts. None of these items are always the same length, so a true byte offset is altered by the insertion of the text (for all but the first inserted text). I am hoping the Range somehow accomodates this fact.

    Thanks again for your help.

  4. #4
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    The macro recorder records what you do, which isn't always the best way to code a solution, but it is a start. The macro recorder thus works on the selection in whatever application you're using, and it doesn't address the full qualification of objects you should always use.

    There must be MSDN articles about bookmarks. Also check out the Word MVP FAQ site:
    http://word.mvps.org/FAQs/MacrosVBA/index.htm

    I use bookmarks, and generally reference the BookMark.Range. When you change the bookmark's text, the bookmark is destroyed, but the range remains, and you can recreate the bookmark so it includes the new range.

    Be prepared for some frustration. I've always thought that the Word VBA Object Model was designed by an escapee from a mental institution.
    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com
    _______

  5. #5
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    jwise, if you select your code when posting and hit the button marked VBA, it will format it for easier reading. I have edited your first post in this thread.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  6. #6
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Why not just use Mail Merge?

  7. #7
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    Mail Merge is good for what it's good for, but if you're populating a whole set of locations of a Word document with bits of information from Excel, using VBA to manipulate Word bookmarks and Excel ranges is far more flexible and capable.
    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com
    _______

  8. #8

    Thanks again

    Thanks again to Jon and others who have replied to my question. I appreciate your time, patience, and expertise.

    I examined Jon's site and was very impressed. Thanks Jon for the reference and for the MS MVP site reference. I'm learning...

    I think I can assign labels to bookmarks. Let's assume there are ten bookmarks with names Q01, Q02, ... Q10. These names are defined in Word when the document is initially produced. The task in XL would be to "find" Q01, and insert the appropriate value from a cell. This would be done in a loop as all ten bookmarks would be replaced every time. The document is then printed, XL moves to the next row, and populates the Word document again. The number of Word documents produced is the number of data rows in the XL worksheet. The same document is used every time.

    TIA

    From MSDN I found:
    Dim rngDoc as Range
    Set rngDoc = ActiveDocument.Range(Start:=0, End:=10)

    So how do I set the Range to the Bookmark?

    TIA

  9. #9
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Jon

    Of course using VBA is more flexible, but it wasn't 100% clear what the OP was doing.

    I just thought I'd through in the idea of Mail Merge as I don't see any point in re-inventing the wheel when you have built-in functionality that might achieve your objective.

    Perhaps in this case VBA is the way to go, but I just thought I'd make the suggestion.

    Edit: I've just reread the OP's last post.

    Sounds to me like Mail Merge could be used.

  10. #10
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    A little example of filling in word (starten from excel) at specified places with values of cells in excel. Place the doc in a certain directory and specify this in the code. Have put some comment with it. This code will basically search for the text "bookmark1", "bookmark2" and "bookmark3" and will insert the textvalues of the cells at those places.

    Hope it helps a little.

    Charlize

  11. #11

    Thanks again & new angle

    Thanks again to all responders.

    I found some example code on a website that is very similar to what I'm doing..., i.e. it is an XL/VBA macro that invokes Word to modify a template. The example code is for a letter to be sent to job candidate for an interview.

    This means I'll go back to bookmarks and "with ActiveDocument", for example

    [VBA]
    With ActiveDocument
    .Bookmarks("Q01").Range.Text = "Charleston Square"
    .Bookmarks ...

    End With
    [/VBA]
    Edit: Please use the VBA Tags........
    We'll see how this works. My concern is now the proper OOP requirements.

    I will update later. Thanks again to all who have contributed. I was unable to include the website in this post because of some rule.

  12. #12
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Hi jwise,
    I think the problem your having with posting links will be gone after you have 5 posts in the forum......
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  13. #13
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    What 'proper OOP requirements' do you mean?

    You do realise that VBA isn't actually considered to be a 'true' OOP programming language by some folks.

  14. #14
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    ... and VBA developers aren't real developers. Big deal.
    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com
    _______

  15. #15
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    Quote Originally Posted by jwise
    [vba]
    With ActiveDocument
    .Bookmarks("Q01").Range.Text = "Charleston Square"
    .Bookmarks ...

    End With
    [/vba]
    Set a range to the bookmark so you can recreate it, because the bookmark is deleted when you replace its text.

    [vba]Dim rBkmk As Object ' or Word.Range
    With ActiveDocument
    Set rBkmk = .Bookmarks("Q01").Range
    rBkmk.Text = "Charleston Square"
    .Bookmarks.Add rBkmk, "Q01"
    End With[/vba]

    Better yet to have an array of bookmarks, and populate them from an array of Excel sources.

    Sometimes I'll keep a list in an Excel worksheet, Excel range in column 1, Work bookmark name in column 2. Or I'll have special bookmarks in Word which have a prefix "xl_", and I'll use an Excel name that matches, so the Word bookmark xl_Bkmk1 will be populated by the Excel range wd_Bkmk1; this way I can use either of the following to find them:

    [vba]For Each Bkmk In MyWdDoc.Bookmarks
    If Left$(Bkmk.Name, 3) = "xl_" Then[/vba]

    [vba]For Each nm In MyWkbk.Names
    If Left$(nm.Name, 3) = "wd_" Then[/vba]
    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com
    _______

  16. #16
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Jon

    I'm just wondering what the OP means and also mentioning some people's opinion.

    I'm in no way trying to offend anyone.

  17. #17
    MS Excel MVP VBAX Tutor
    Joined
    Mar 2005
    Posts
    246
    Location
    I didn't know what that meant either (OOP requirements??), so I was ignoring it.

    The thing with VBA is that if you keep in mind the OOP principles, even if the language doesn't really implement them all so well, it can make your own programming better.
    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com
    _______

  18. #18
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Jon

    Totally agree.

    I've got a feeling, though I may be wrong, that the OP is concerned about the automation aspect of his project.

    I suppose we'll just need to wait till they post back.

  19. #19
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Quote Originally Posted by JonPeltier
    Set a range to the bookmark so you can recreate it, because the bookmark is deleted when you replace its text.
    Hi Jon,
    I've been tinkering with Word a little while and I find that if your populating a template to send out that it doesn't matter if the bookmarks are destroyed in the process.....but I have had no need to go back and find them, etc. but in a case like that would it matter if they are gone after you do the replacement?

    I'm assuming a template from this in the op's first post...
    The objective is to use a VBA macro in Excel to kick off a Word document, and to change some words (placeholders) in this document to values that come from the spreadsheet.
    For that matter Charlize's find/replace would work too if ....if they are using a template....am I missing something?
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  20. #20

    Several items...

    Thanks again to all who posted. You are educating me.

    By "OOP requirements", I meant those somewhat strange statements like
    "Set XX as Object"
    etc. The strange is relative to someone who comes from a strictly procedural background (and mainframe at that). Sorry, but it just doesn't look like code. "N = N + 1" looks like code to an old asm programmer. There is nothing political in this at all. Perhaps I should be asking for a good OOP text reference. All of this was designed to give my advisors a clue as to my lack of OOP understanding.

    The "destroyed bookmark" could be a problem since I will essentially be printing one document for each row in my spreadsheet. I am assuming this means that I must recreate the bookmark after I insert the cell (or text) in the document. There are around ten bookmarks, so what I hearing is insert text 1 at bookmark 1, put bookmark back, go to remaining 9 in sequence, then print the page.

    The other thought was just to "end nosave" Word. It just seemed that restarting Word for each row is a lot of overhead. This may not be a consideration in this realm. In the mainframe realm, I processed millions of records and this could be a big problem. Today there will be around 40 rows in this spreadsheet, and if it takes two minutes, that's no big deal. I am just accustomed to thinking in the old way... this old dog is returning to his ______.

    Thanks again.

Posting Permissions

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