none
Document.Add not creating a Document Collection Index? RRS feed

  • Question

  • Hi all,

    I'm new to Office 2010, and am testing an Excel app that does a Mail Merge after pulling data from a database.  It was written for Office 2003 and works fine, but I'm having an issue getting it to work in Excel 2010.  Specifically, this line:

    With WordDoc.MailMerge

    Generates a #91 runtime error: Object Variable not set

    Breaking on With WordDoc.MailMerge shows that the WordApp.Documents object contains 0 items in the collection, so it appears that the problem is originating here:

    Set WordDoc = WordApp.Documents.App(Path & FileName)

    The variables for Path and FileName are global and contain a valid path when parsed, so that's not the issue.  I assume it's a syntax problem going from 2003 -> 2010 but I haven't been able to nail it down.  Relevant bits of code are below, if anyone can shed some light.

    'Relevant bits of code
    
    Public WordApp As Word.Application
    Public WordDoc As Word.Document
    
    Set WordApp = New Word.Application
    WordApp.visible = False
    Set WordDoc = WordApp.Documents.App(Path & FileName)
    
    With WordDoc.MailMerge
        .OpenDataSource Name:=FName, ConfirmConversions:=False, ReadOnly:=False, _
         LinkToSource:=True, Revert:=False,   
         Format:=wdOpenFormatAuto
         
         With .DataSource
             .FirstRecord = wdDefaultFirstRecord
             .LastRecord = wdDefaultLastRecord
          End With
          .Execute Pause:=True
    End With
    

     

     

     

    Wednesday, September 28, 2011 8:47 PM

Answers

  • Hi Chris

    Very odd, indeed. Just out of curiosity, if you set WordApp.Visible = True does that change the behavior?

    "Traditionally", execution should wait, but they've started to make a number of things asynchronous in order to try to give the user the feeling Office apps are starting fast enough. If you could report bugs on Office on the Connect site I'd certainly recommend you do so in this case. But unfortunately, you can't...

    Something else you might try, that we use in VSTO, is check WordApp.Documents.Count in a loop. As long as it's <1, that new document Word generates when it starts isn't there, yet, so the app isn't loaded. That might be more reliable than an arbitrary sleep period (not all computers will have the same number of add-ins to load, for example, which can affect the time it takes).


    Cindy Meister, VSTO/Word MVP
    Friday, September 30, 2011 4:09 PM
    Moderator
  • Nailed it!

    See my previous post about it working if I added break points.  The root cause came in this series of statements:

    Set WordApp = New Word.Application
    WordApp.Visible = False
    Set WordDoc = WordApp.Documents.Add(Template:=FilePath & MergeMainFile)
    
    
    

    In Office 2003, Word was launching fast enough that WINWORD.exe was already running when the Set WordDoc statement executed.  In 2010, Word is launching too slow, so the WINDWORD.exe process wasn't fully launched by the next Set WordDoc statement, hence it was never being set, hence my WinDoc object was Nothing.

    Adding the break points worked because it gave time for Word to fully launch. I added a Sleep function like this:

    Set WordApp = New Word.Application
    WordApp.Visible = False
    Sleep 5000
    Set WordDoc = WordApp.Documents.Add(Template:=FilePath & MergeMainFile)
    
    
    

    To force execution to wait 5 seconds to give Word 2010 enough time to fully load.  This solved my problem, thanks all for your help :)

    Friday, September 30, 2011 3:52 PM

All replies

  • Shouldn't this command:

    Set WordDoc = WordApp.Documents.App(Path & FileName)

    Be:

    Set WordDoc = WordApp.Documents.Add(Path & FileName)

    ?


    Kind Regards, Rich ... http://greatcirclelearning.com
    Thursday, September 29, 2011 12:07 AM
  • Hi Rich,

    That was a typo on my part when putting the example in, yes it's written correctly in the module:

    Set WordDoc = WordApp.Documents.Add(FilePath & MergeMainFile)
    

     

    Thursday, September 29, 2011 2:33 PM
  • Hi Chris

    Please tell us EXACTLY which line of code is triggering the error. The "With" line has no collection, so I'm not sure what you're talking about.

    FWIW the sample code you show is also missing _ at the end of the second line of OpenDataSource.

    Please show us the exact and correct code - we can't help you if we're seeing typos and you say they aren't there in the original. That's a waste of our time and yours.

    MailMerge really hasn't changed from 2003 to 2010 - it did from 2002 to 2003. The big change, there, was that DDE was no longer the default connection method; OLE DB replaced it. I can't tell from your code which type of data connection you expect to have happen?

    If this could be the problem, I recommend recording a macro when linking to the data source then comparing that syntax to what you have. It's possible you need to use the SubType parameter.


    Cindy Meister, VSTO/Word MVP
    Thursday, September 29, 2011 5:27 PM
    Moderator
  • Hi Cindy, here's the actual code.  I copied the references as well that are declared outside the module the merge is running from.  Note that FName does contain a valid string when passed from another module, so that variable isn't an issue:

    Const FilePath As String = "C:\Temp"
    Const MergeSourceFile As String = "Address Labels Mail Merge Source "
    Const MergeMainFile As String = "Address Labels Mail Merge Main.doc"
    Const MergeFinalFile As String = "Address Labels Merged.doc"
    Public FName As String
    
    Public WordApp As Word.Application
    Public WordDoc As Word.Document
    
    Sub MergeLabels()
         MenuA.Hide
         
         Set WordApp = New Word.Application
         WordApp.Visible = False
         Set WordDoc = WordApp.Documents.Add(Template:=FilePath & MergeMainFile)
    
          'Error occurs here (Object Variable not set or With without End With)
          With WordDoc.MailMerge
               .OpenDataSource Name:=FName,ConfirmConversions:=False, ReadOnly:=False, _
    LinkToSource:=True,Revert:=False,Format:=wdOpenFormatAuto
    
                With .DataSource
                        .FirstRecord = wdDefaultFirstRecord
                        .LastRecord = wdDefaultLastRecord
                End With
                .Execute Pause:=True
          End With
    
          WordDoc.Close SaveChanges:=False
          Set WordDoc = WordApp.ActiveDocument
          WordDoc.SaveAs Filename:=FilePath + MergeFinalFile,FileFormat:=wdFormatDocument
          WordDoc.Close
          WordApp.Quit
          Set WordApp = Nothing
          MsgBox "Done."
    End Sub
    

     

    Thursday, September 29, 2011 6:14 PM
  • I'm still having issues with this line:

    Set WordDoc = WordApp.Documents.Add(Template:=FilePath & MergeMainFile)

    If this is the merge file then it's not a template, in fact your declare shows it as a doc file.  Thus shouldn't this say:

    Set WordDoc = WordApp.Documents.Open(FileName:=FilePath & MergeMainFile)

    ?


    Kind Regards, Rich ... http://greatcirclelearning.com
    Thursday, September 29, 2011 6:46 PM
  • Rich, thanks for the response.

    The line in question is to create an empty Document that uses the specified template.  Replacing the .Add with .Open results in a "Command Invalid" error. 

    I even removed Set WordDoc = from the following line just to see if an actual entry in the Documents collection would be created, but it keeps showing 'Nothing' and a count of 0 in the collection:

    WordApp.Documents.Add(Template:=FilePath & MergeMainFile)
    

     

    Thursday, September 29, 2011 8:54 PM
  • Cindy:

    To answer a point you made, the source data is coming from a text file, which is stored in the string FName:

    FName = FilePath & MergeSourceFile & CStr(Year(Now)) & CStr(Month(Now)) & CStr(Day(Now)) & "-" & CStr(Hour(Now)) & "-" & CStr(Minute(Now)) & "-" & CStr(Second(Now)) & ".txt"
    


    This variable is parsing fine, so I don't think the external data is the problem.

    Thursday, September 29, 2011 8:58 PM
  • Hi Chris

    Thanks for the additional code

    <<(Template:=FilePath & MergeMainFile)>>

    The variable FilePath doesn't include a backslash at the end.  MergeMainFile does not seem to have one at the front. So where is the backslash separating the path from the file name? That would be so "obvious" an error that I feel I need to put it at the top...


    Cindy Meister, VSTO/Word MVP
    Friday, September 30, 2011 7:10 AM
    Moderator
  • Hi Cindy, thanks for the continued replies :)

    The path is being built correctly, I'v verified that.  I also (after some debugging) accidently found a method that worked, but it's weird and I'm not sure I understand exactly why this is happening - it's not behavior I've seen before in VB.

    Basically if I put in two breakpoints at the specified points below (see inline comments), the function works perfectly, and I can trace the proper document variables between WordApp.Documents and WordDoc, and the error doesn't occur and the rest of the function processes correctly as I step through it.

    So punchline, using two breakpoints and stepping through them yields correct execution, but WITHOUT breakpoints I get the "Object Variable not set" error on the With WordDoc.MailMerge line:

    Sub MergeLabels()
         MenuA.Hide
         
         Set WordApp = New Word.Application
         WordApp.Visible = False
    
    'First breakpoint set on the line below     
    Set WordDoc = WordApp.Documents.Add(Template:=FilePath & MergeMainFile)
    
    'Second breakpoint set on the line below
          With WordDoc.MailMerge
               .OpenDataSource Name:=FName,ConfirmConversions:=False, ReadOnly:=False, _
    LinkToSource:=True,Revert:=False,Format:=wdOpenFormatAuto
    
                With .DataSource
                        .FirstRecord = wdDefaultFirstRecord
                        .LastRecord = wdDefaultLastRecord
                End With
                .Execute Pause:=True
          End With
    
          WordDoc.Close SaveChanges:=False
          Set WordDoc = WordApp.ActiveDocument
          WordDoc.SaveAs Filename:=FilePath + MergeFinalFile,FileFormat:=wdFormatDocument
          WordDoc.Close
          WordApp.Quit
          Set WordApp = Nothing
          MsgBox "Done."
    End Sub
    
    

     

    Friday, September 30, 2011 3:41 PM
  • Nailed it!

    See my previous post about it working if I added break points.  The root cause came in this series of statements:

    Set WordApp = New Word.Application
    WordApp.Visible = False
    Set WordDoc = WordApp.Documents.Add(Template:=FilePath & MergeMainFile)
    
    
    

    In Office 2003, Word was launching fast enough that WINWORD.exe was already running when the Set WordDoc statement executed.  In 2010, Word is launching too slow, so the WINDWORD.exe process wasn't fully launched by the next Set WordDoc statement, hence it was never being set, hence my WinDoc object was Nothing.

    Adding the break points worked because it gave time for Word to fully launch. I added a Sleep function like this:

    Set WordApp = New Word.Application
    WordApp.Visible = False
    Sleep 5000
    Set WordDoc = WordApp.Documents.Add(Template:=FilePath & MergeMainFile)
    
    
    

    To force execution to wait 5 seconds to give Word 2010 enough time to fully load.  This solved my problem, thanks all for your help :)

    Friday, September 30, 2011 3:52 PM
  • Hi Chris

    Very odd, indeed. Just out of curiosity, if you set WordApp.Visible = True does that change the behavior?

    "Traditionally", execution should wait, but they've started to make a number of things asynchronous in order to try to give the user the feeling Office apps are starting fast enough. If you could report bugs on Office on the Connect site I'd certainly recommend you do so in this case. But unfortunately, you can't...

    Something else you might try, that we use in VSTO, is check WordApp.Documents.Count in a loop. As long as it's <1, that new document Word generates when it starts isn't there, yet, so the app isn't loaded. That might be more reliable than an arbitrary sleep period (not all computers will have the same number of add-ins to load, for example, which can affect the time it takes).


    Cindy Meister, VSTO/Word MVP
    Friday, September 30, 2011 4:09 PM
    Moderator
  • I tried making it visible, and that helped lead me to the conclusion (I visibly saw no template being loaded before the error occured).

    You are right about the sleep method.  That was arbitrary, and may not account for all instances.  I'll use the method you described since that will account for all variations of startup times.  Thanks!

    Friday, September 30, 2011 4:26 PM