none
MS Access 2010, Word 2010 Automation & Windows 7

    Question

  • Is there anything new or different with Office automation with Office 2010 and Windows 7. Such as a gotcha from Windows XP and Office XP/2003?

    I've seen varying and unexpected behavior that is tripping up one of my users. Code that previously ran fine now returns error number 91, or 'Object or With Variable Not Found'

    I was able to come up with something of a workaround by having the user first open Word by itself, then in Access run the routine that implements automation, and it runs without an error.

    Previously I defined the Word.Application object in a module, and later in that same module Set it to New Word.Application.

    Option Compare Database
    Option Explicit
    
    Dim objWord As Word.Application
    Dim objWordDoc As Word.Document
    
    '
    '
    '
    '
    Public Function GenerateOrderLetter(sClientInv As String, sFileType As String, sType As String, Optional sMatterNumber As String)
    
    Set objWord = New Word.Application
    Set objWordDoc = New Word.Document
    Set objWordDoc = objWord.Documents.Add("q:\PCT2000\templates\OrderLetter2K9.dot")
    objWord.Visible = True
    '
    '
    '
    '
    End Function

    I would also add that I recompiled the project on the user's computer, and ensured that there is a reference to Microsoft Word 14 Object Library (msword.olb), and that there are no 'MISSING' references.

     


    Facebook: Zamdrist
    Roleplaying: Elliquiy
    Wednesday, August 17, 2011 5:03 PM

Answers

  • The code tweak did resolve the problem for the user. It was a simple matter of first making the Word.Application instance visible before doing anything. Bear in mind, the code works fine either way in Office XP/2003, and likely earlier versions. But in Office 2010 at least, the order the code is written can, in some cases, cause issues.

    I suspect it has something do with both processor speed and the version of Office, or more specifically the version of VBA. The code was moving along to execute the next line before the template had finished loading into memory. The line failed, and the whole operation stopped. I observed this as a new instance of Word appeared, but no template was loaded.

    For the record, this code works (Any recent version of Office, not just 2010)

    Public Function OpenWord()
    
    Dim objWord As Word.Application
    Dim objWordDoc As Word.Document
    
    Set objWord = New Word.Application
    objWord.Visible = True
    objWord.Documents.Add "C:\AppFiledLetter2011.dot"
    objWord.Selection.GoTo wdGoToBookmark, , , "ApplicantName1"
    
    Set objWordDoc = Nothing
    Set objWord = Nothing
    
    End Function

    I suspect an earlier, and yet unresolved issue, is related (maybe), though has nothing to do with Office 2010.

    Method 'Add' of object '_CommandBars' Failed
    Facebook: Zamdrist
    Roleplaying: Elliquiy
    • Marked as answer by ITMn0403 Friday, August 19, 2011 8:41 PM
    Friday, August 19, 2011 8:13 PM

All replies

  • Hi Zamdrist,

     

    Can you post the whole code of your Function?

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Wednesday, August 17, 2011 5:16 PM
  • danishani - I can, and will. Bear in mind the code works in Windowx XP & Office 2003, and most of the code I am posting is irrelevant to my problem. Thank you.

     

    Public Function GenerateOrderLetter(sClientInv As String, sFileType As String, sType As String, Optional sMatterNumber As String)
    
    'Letter Types
    '
    'National Phase
    'EP Validation
    'Direct National
    'Design
    '
    Set objWord = New Word.Application
    Set objWordDoc = New Word.Document
    Set objWordDoc = objWord.Documents.Add("q:\PCT2000\templates\OrderLetter2K9.dot")
    objWord.Visible = True
    
    Dim iInvRow As Integer
    Dim r As Integer
    Dim sInvText As String
    Dim iPriorityRow As Integer
    Dim iPriorityDate As Integer
    Dim sPriorityText As String
    Dim sMembers As String
    Dim sPriorityApplication As String
    
    Set rsInventors = cmdStoredProc("spOrderLetterInventors", sClientInv, adVarChar, sFileType, adChar).Execute
    Set rsCheckPriority = cmdStoredProc("spCheckPriorityDates", sMatterNumber, adVarChar, sFileType, adVarChar).Execute
    Set rsPriority = cmdStoredProc("spOrderLetterPriorities", sClientInv, adVarChar, sFileType, adChar).Execute
    Set rsMisc = cmdStoredProc("spOrderLetterInvention_ParaInfo", sClientInv, adVarChar, sFileType, adChar).Execute
    
    If Not rsCheckPriority.BOF Or Not rsCheckPriority.EOF Then
      rsCheckPriority.MoveFirst
      If rsCheckPriority.Fields("CheckDate") = True Then
        MsgBox "One or more priority dates are greater than or equal to today's date. Resolve first before running Order Letters"
        Exit Function
      End If
    End If
    
    rsCheckPriority.Close
    Set rsCheckPriority = Nothing
      
    Select Case sType
      Case Is = "EP Validation"
        'EP Grant Date (Not available)
      Case Is = "Design"
        iPriorityDate = 6
      Case Is = "National Phase"
        iPriorityDate = 30 'iChapII
      Case Is = "Direct National"
        iPriorityDate = 12
    End Select
    
    'Work around here to fix Design deadline date increment
    If Forms!frmMatters!cmbInventionType.Value = 2 Then iPriorityDate = 6
      
    Select Case sType
    
    '*********************************************************************************
      Case Is = "National Phase"
        objWord.Selection.GoTo wdGoToBookmark, , , "Lang1"
        objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Lang1_NationalPhase").Insert objWord.Selection.Range, RichText:=True
        objWord.Selection.GoTo wdGoToBookmark, , , "Inv"
        objWord.Selection.TypeText "INVENTOR(S): see PCT publication" & vbCrLf
        objWord.Selection.TypeText vbCrLf & "PRIORITY: see PCT publication" & vbCrLf
        objWord.Selection.GoTo wdGoToBookmark, , , "Notes"
        objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Notes_NationalPhase").Insert objWord.Selection.Range, RichText:=True
        
        Set rsCountries = cmdStoredProc("spOrderLetterCountriesPCT", sMatterNumber, adVarChar).Execute
        Set rsRegions = cmdStoredProc("spOrderLetterRegionsPCT", sMatterNumber, adVarChar).Execute
        
        'National Countries
        Do While Not rsCountries.EOF
          With objWordDoc
            r = r + 1
            .Tables(1).Rows.Add
            .Tables(1).Rows(r).Cells(1).Range.Text = rsCountries.Fields("fldMGFileCRSel").Value
            .Tables(1).Rows(r).Cells(1).Range.Bold = False
            .Tables(1).Rows(r).Cells(2).Range.Text = rsCountries.Fields("fldCountry").Value
            .Tables(1).Rows(r).Cells(2).Range.Bold = False
            .Tables(1).Rows(r).Cells(3).Range.Text = IIf(IsNull(rsCountries.Fields("fldFirmName").Value), "", rsCountries.Fields("fldFirmName").Value)
            .Tables(1).Rows(r).Cells(3).Range.Bold = False
          End With
          rsCountries.MoveNext
        Loop
        If rsCountries.RecordCount > 0 Then objWordDoc.Tables(1).Rows(objWordDoc.Tables(1).Rows.Count).Delete
        
        'Region Countries
        'Set rsMembers = cmdStoredProc("dbo.spOrderLetterMembersByRegion", rsPriority.Fields("fldFilingDate").Value, adDate, rsRegions.Fields("fldPCTRegAbbr").Value, adChar).Execute
        
        Do While Not rsRegions.EOF
          With objWordDoc
            sMembers = ""
            r = r + 1
            .Tables(1).Rows.Add
            .Tables(1).Rows(r).Cells(1).Range.Text = rsRegions.Fields("fldPCTRegFileNo").Value
            .Tables(1).Rows(r).Cells(1).Range.Bold = False
            
              rsPriority.MoveFirst
              'Set rsMembers = cmdStoredProc("dbo.spOrderLetterEPMembers", rsPriority.Fields("fldFilingDate").Value, adDate).Execute
              Set rsMembers = cmdStoredProc("dbo.spOrderLetterMembersByRegion", rsPriority.Fields("fldFilingDate").Value, adDate, rsRegions.Fields("fldPCTRegAbbr").Value, adChar).Execute
              
              Do While Not rsMembers.EOF
                sMembers = sMembers & ", " & RTrim(LTrim(rsMembers.Fields("fldCountry")))
                rsMembers.MoveNext
              Loop
            .Tables(1).Rows(r).Cells(2).Range.Text = rsRegions.Fields("fldPCTRegName").Value & " - " & Right(sMembers, Len(sMembers) - 2)
            .Tables(1).Rows(r).Cells(2).Range.Bold = False
            .Tables(1).Rows(r).Cells(3).Range.Text = rsRegions.Fields("fldFirmName").Value
            .Tables(1).Rows(r).Cells(3).Range.Bold = False
          End With
          
          rsRegions.MoveNext
        Loop
        'If rsMembers.RecordCount > 0 Then objWordDoc.Tables(1).Rows(objWordDoc.Tables(1).Rows.Count).Delete
        
        Set rsPCTStatusInfo = cmdStoredProc("spPCTStatusInfo", sMatterNumber, adVarChar).Execute
        If Not (rsPCTStatusInfo.BOF Or rsPCTStatusInfo.EOF) Then
          objWordDoc.FormFields("FilingDate1").Result = rsPCTStatusInfo.Fields("fldDeadlineChapII").Value
          objWordDoc.FormFields("FilingDate2").Result = rsPCTStatusInfo.Fields("fldDeadlineChapII").Value
        End If
        Set rsPCTStatusInfo = Nothing
            
        objWordDoc.FormFields("Title").Result = rsMisc.Fields("fldInventTitle").Value
        objWordDoc.FormFields("MG1").Result = sMatterNumber
        objWordDoc.FormFields("MG2").Result = sMatterNumber
        objWordDoc.FormFields("ApplicationNumber").Result = Replace(IIf(IsNull(rsMisc.Fields("fldIntlAppNum").Value), "", rsMisc.Fields("fldIntlAppNum").Value), "PCT/US", "")
    
    '*********************************************************************************
      Case Is = "EP Validation"
        Set rsCountries = cmdStoredProc("spEPValidationCountries", sClientInv, adVarChar, sFileType, adVarChar).Execute
        Set rsEP = cmdStoredProc("spEPValidationInfo", sMatterNumber, adVarChar, sClientInv, adVarChar, sFileType, adVarChar).Execute
        
        objWord.Selection.GoTo wdGoToBookmark, , , "Lang1"
        objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Lang1_Validation").Insert objWord.Selection.Range, RichText:=True
        objWord.Selection.GoTo wdGoToBookmark, , , "Notes"
        objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Notes_Validation").Insert objWord.Selection.Range, RichText:=True
        objWordDoc.FormFields("MG1").Result = rsEP.Fields("fldPCTRegFileNo").Value
        objWordDoc.FormFields("MG2").Result = rsEP.Fields("fldPCTRegFileNo").Value
        
        'Leave dates blank for user to supply, as per FFD Department 11/02/2010
        'objWordDoc.FormFields("FilingDate1").Result = DateAdd("m", iPriorityDate, rsPriority.Fields("fldFilingDate").Value)
        'objWordDoc.FormFields("FilingDate2").Result = DateAdd("m", iPriorityDate, rsPriority.Fields("fldFilingDate").Value)
        
        objWordDoc.FormFields("ApplicationNumber").Result = IIf(IsNull(rsEP.Fields("fldPCTRegPatNo").Value), "", rsEP.Fields("fldPCTRegPatNo").Value)
        objWordDoc.FormFields("AssocRefNo").Result = IIf(IsNull(rsEP.Fields("fldPCTRegClientRef").Value), "", rsEP.Fields("fldPCTRegClientRef").Value)
        objWordDoc.FormFields("Title").Result = rsMisc.Fields("fldInventTitle").Value
        
        Do While Not rsCountries.EOF
            With objWordDoc
              r = r + 1
              .Tables(1).Rows.Add
              .Tables(1).Rows(r).Cells(1).Range.Text = rsCountries.Fields("fldMGFileCRSel").Value
              .Tables(1).Rows(r).Cells(1).Range.Bold = False
              .Tables(1).Rows(r).Cells(2).Range.Text = rsCountries.Fields("fldCountry").Value
              .Tables(1).Rows(r).Cells(2).Range.Bold = False
              .Tables(1).Rows(r).Cells(3).Range.Text = IIf(IsNull(rsCountries.Fields("fldFirmName").Value), "", rsCountries.Fields("fldFirmName").Value)
              .Tables(1).Rows(r).Cells(3).Range.Bold = False
            End With
          rsCountries.MoveNext
        Loop
        objWordDoc.Tables(1).Rows(objWordDoc.Tables(1).Rows.Count).Delete
    '*********************************************************************************
      Case Is = "Design"
        objWord.Selection.GoTo wdGoToBookmark, , , "Lang1"
        objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Lang1_Design").Insert objWord.Selection.Range, RichText:=True
        objWord.Selection.GoTo wdGoToBookmark, , , "Inv"
        objWord.Selection.TypeText "INVENTOR(S):" & vbCrLf & vbCrLf
              
          If Not rsInventors.BOF Or Not rsInventors.EOF Then
            For iInvRow = 1 To rsInventors.RecordCount
              objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Inv_Long").Insert objWord.Selection.Range, RichText:=True
              sInvText = sInvText & rsInventors.Fields("fldFullName").Value & vbCrLf & rsInventors.Fields("fldStreet").Value & vbCrLf & rsInventors.Fields("fldInvCity").Value & ", " & rsInventors.Fields("fldStateName").Value & " " & rsInventors.Fields("fldInvZip").Value & vbCrLf & rsInventors.Fields("fldCountry").Value & vbCrLf & rsInventors.Fields("CorpCountry").Value
              objWordDoc.Tables(2).Rows(iInvRow).Cells(2).Range.Text = sInvText
              rsInventors.MoveNext
              sInvText = ""
            Next iInvRow
          End If
        
        objWord.Selection.TypeText vbCrLf & "PRIORITIES:" & vbCrLf & vbCrLf
        
          If Not rsPriority.BOF Or Not rsPriority.EOF Then
            For iPriorityRow = 1 To rsPriority.RecordCount
              rsPriority.MoveFirst
              objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Priority_Long").Insert objWord.Selection.Range, RichText:=True
              sPriorityText = sPriorityText & rsPriority.Fields("fldCountry").Value & vbCrLf & rsPriority.Fields("fldApplicationNum").Value & vbCrLf & rsPriority.Fields("fldFilingDate").Value
              objWordDoc.Tables(3).Rows(iPriorityRow).Cells(2).Range.Text = sPriorityText
              rsPriority.MoveNext
              sPriorityText = ""
            Next iPriorityRow
          End If
        
        objWordDoc.FormFields("Title").Result = rsMisc.Fields("fldInventTitle").Value
        objWord.Selection.GoTo wdGoToBookmark, , , "Notes"
        objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Notes_Design").Insert objWord.Selection.Range, RichText:=True
    '*********************************************************************************
      Case Is = "Direct National"
        
        Set rsCountries = cmdStoredProc("spOrderLetterCountriesDN", sClientInv, adVarChar, sFileType, adChar).Execute
        Set rsRegions = cmdStoredProc("spDirectNationalRegionsSelected", sClientInv, adVarChar, sFileType, adChar).Execute
        Set rsEP = cmdStoredProc("spEPValidationInfo", sMatterNumber, adVarChar, sClientInv, adVarChar, sFileType, adVarChar).Execute
        
        If rsMisc.Fields("fldInvTypeID").Value = 2 Then
          objWord.Selection.GoTo wdGoToBookmark, , , "Lang1"
          objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Lang1_Design").Insert objWord.Selection.Range, RichText:=True
          objWord.Selection.GoTo wdGoToBookmark, , , "Notes"
          objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Notes_Design").Insert objWord.Selection.Range, RichText:=True
        Else
          objWord.Selection.GoTo wdGoToBookmark, , , "Lang1"
          objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Lang1_DirectNational").Insert objWord.Selection.Range, RichText:=True
          objWord.Selection.GoTo wdGoToBookmark, , , "Notes"
          objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Notes_DirectNational").Insert objWord.Selection.Range, RichText:=True
        End If
        
        objWord.Selection.GoTo wdGoToBookmark, , , "Inv"
        objWord.Selection.TypeText "INVENTOR(S):" & vbCrLf & vbCrLf
        objWordDoc.FormFields("MG1").Result = sClientInv
        objWordDoc.FormFields("MG2").Result = sClientInv
        objWordDoc.FormFields("FilingDate1").Result = DateAdd("m", iPriorityDate, rsPriority.Fields("fldFilingDate").Value)
        objWordDoc.FormFields("FilingDate2").Result = DateAdd("m", iPriorityDate, rsPriority.Fields("fldFilingDate").Value)
        
        Do While Not rsPriority.EOF
          sPriorityApplication = rsPriority.Fields("fldApplicationNum").Value & "; " & sPriorityApplication
          rsPriority.MoveNext
        Loop
        rsPriority.MoveFirst
        
        objWordDoc.FormFields("ApplicationNumber").Result = Left(RTrim(sPriorityApplication), Len(RTrim(sPriorityApplication)) - 1)
        objWordDoc.FormFields("Title").Result = rsMisc.Fields("fldInventTitle").Value
        
          Do While Not rsCountries.EOF
              With objWordDoc
                r = r + 1
                .Tables(1).Rows.Add
                .Tables(1).Rows(r).Cells(1).Range.Text = rsCountries.Fields("fldMGFileCRSel").Value
                .Tables(1).Rows(r).Cells(1).Range.Bold = False
                .Tables(1).Rows(r).Cells(2).Range.Text = rsCountries.Fields("fldCountry").Value
                .Tables(1).Rows(r).Cells(2).Range.Bold = False
                .Tables(1).Rows(r).Cells(3).Range.Text = IIf(IsNull(rsCountries.Fields("fldFirmName").Value), "", rsCountries.Fields("fldFirmName").Value)
                .Tables(1).Rows(r).Cells(3).Range.Bold = False
              End With
            rsCountries.MoveNext
          Loop
          
          Do While Not rsRegions.EOF
            With objWordDoc
                r = r + 1
                .Tables(1).Rows.Add
                .Tables(1).Rows(r).Cells(1).Range.Text = sClientInv & Mid(rsRegions.Fields(0).Value, 11, 2) & rsRegions.Fields("fldFileType").Value
                .Tables(1).Rows(r).Cells(1).Range.Bold = False
                
                  rsPriority.MoveFirst
                  Set rsMembers = cmdStoredProc("dbo.spOrderLetterEPMembers", rsPriority.Fields("fldFilingDate").Value, adDate).Execute
                  
                  Do While Not rsMembers.EOF
                    sMembers = sMembers & ", " & RTrim(LTrim(rsMembers.Fields("fldCountry")))
                    rsMembers.MoveNext
                  Loop
                  
                .Tables(1).Rows(r).Cells(2).Range.Text = Mid(rsRegions.Fields(0).Value, 11, 2) & " - " & Right(sMembers, Len(sMembers) - 2)
                .Tables(1).Rows(r).Cells(2).Range.Bold = False
                .Tables(1).Rows(r).Cells(3).Range.Text = rsRegions.Fields("fldFirmName").Value
                .Tables(1).Rows(r).Cells(3).Range.Bold = False
            End With
            rsRegions.MoveNext
          Loop
          
          objWordDoc.Tables(1).Rows(objWordDoc.Tables(1).Rows.Count).Delete
          
          objWord.Selection.GoTo wdGoToBookmark, , , "Inv"
          objWord.Selection.EndKey wdLine
          objWord.Selection.TypeText vbCrLf & vbCrLf
          
          If Not rsInventors.BOF Or Not rsInventors.EOF Then
            For iInvRow = 1 To rsInventors.RecordCount
              objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Inv_Long").Insert objWord.Selection.Range, RichText:=True
              sInvText = sInvText & rsInventors.Fields("fldFullName").Value & vbCrLf & rsInventors.Fields("fldStreet").Value & vbCrLf & rsInventors.Fields("fldInvCity").Value & ", " & rsInventors.Fields("fldStateName").Value & " " & rsInventors.Fields("fldInvZip").Value & vbCrLf & rsInventors.Fields("fldCountry").Value & vbCrLf & rsInventors.Fields("CorpCountry").Value
              objWordDoc.Tables(2).Rows(iInvRow).Cells(2).Range.Text = sInvText
              rsInventors.MoveNext
              sInvText = ""
            Next iInvRow
          End If
          
    
        objWord.Selection.TypeText vbCrLf & "PRIORITIES:" & vbCrLf & vbCrLf
        
          If Not rsPriority.BOF Or Not rsPriority.EOF Then
            For iPriorityRow = 1 To rsPriority.RecordCount
              objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Priority_Long").Insert objWord.Selection.Range, RichText:=True
              sPriorityText = sPriorityText & rsPriority.Fields("fldCountry").Value & vbCrLf & rsPriority.Fields("fldApplicationNum").Value & vbCrLf & rsPriority.Fields("fldFilingDate").Value
              objWordDoc.Tables(3).Rows(iPriorityRow).Cells(2).Range.Text = sPriorityText
              rsPriority.MoveNext
              sPriorityText = ""
            Next iPriorityRow
          End If
    
    End Select
    
    If objWordDoc.Bookmarks.Exists("EPAssociate") = True Then objWordDoc.FormFields("EPAssociate").Result = rsEP.Fields("fldFirmName").Value
    objWord.Selection.GoTo wdGoToBookmark, , , "Applicant"
    objWord.Selection.InsertAfter rsMisc.Fields("fldClientName").Value & vbCrLf & rsMisc.Fields("fldAddress") & vbCrLf & rsMisc.Fields("fldCity") & ", " & rsMisc.Fields("fldStateName") & " " & rsMisc.Fields("fldZip").Value & vbCrLf & rsMisc.Fields("fldCountry").Value
    If objWordDoc.Bookmarks.Exists("ApplicantName") = True Then objWordDoc.FormFields("ApplicantName").Result = rsMisc.Fields("fldClientName").Value
    If objWordDoc.Bookmarks.Exists("StateCorp") = True Then objWordDoc.FormFields("StateCorp").Result = rsMisc.Fields("CorpSt").Value
    If objWordDoc.Bookmarks.Exists("PubNumber1") = True Then objWordDoc.FormFields("PubNumber1").Result = Replace(IIf(IsNull(rsMisc.Fields("fldAppPublishNo").Value), "", rsMisc.Fields("fldAppPublishNo").Value), "WO ", "")
    If objWordDoc.Bookmarks.Exists("PubNumber2") = True Then objWordDoc.FormFields("PubNumber2").Result = Replace(IIf(IsNull(rsMisc.Fields("fldAppPublishNo").Value), "", rsMisc.Fields("fldAppPublishNo").Value), "WO ", "")
    objWord.Selection.HomeKey wdStory
    MsgBox "Order Letter Complete"
    
    End Function



    Facebook: Zamdrist
    Roleplaying: Elliquiy
    Wednesday, August 17, 2011 5:23 PM
  • Just for interest, did you try late binding?

    Set objWord = CreateObject("Word.Application")
    Set objWordDoc = CreateObject("Word.Document")
    



    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog biztoolblogs.tumblr.com
    Wednesday, August 17, 2011 5:30 PM
  • Yes it is quite odd indeed, just a few tweeks I recommend:

    Option Compare Database
    Option Explicit
    
    
    Public Function GenerateOrderLetter(sClientInv As String, sFileType As String, sType As String, Optional sMatterNumber As String)
    
    'Letter Types
    '
    'National Phase
    'EP Validation
    'Direct National
    'Design
    '
    'put these within the function
    Dim objWord As Word.Application
    Dim objWordDoc As Word.Document
    
    Set objWord = New Word.Application
    Set objWordDoc = New Word.Document
    Set objWordDoc = objWord.Documents.Add("q:\PCT2000\templates\OrderLetter2K9.dot")
    objWord.Visible = True
    
    Dim iInvRow As Integer
    Dim r As Integer
    Dim sInvText As String
    Dim iPriorityRow As Integer
    Dim iPriorityDate As Integer
    Dim sPriorityText As String
    Dim sMembers As String
    Dim sPriorityApplication As String
    
    Set rsInventors = cmdStoredProc("spOrderLetterInventors", sClientInv, adVarChar, sFileType, adChar).Execute
    Set rsCheckPriority = cmdStoredProc("spCheckPriorityDates", sMatterNumber, adVarChar, sFileType, adVarChar).Execute
    Set rsPriority = cmdStoredProc("spOrderLetterPriorities", sClientInv, adVarChar, sFileType, adChar).Execute
    Set rsMisc = cmdStoredProc("spOrderLetterInvention_ParaInfo", sClientInv, adVarChar, sFileType, adChar).Execute
    
    If Not rsCheckPriority.BOF Or Not rsCheckPriority.EOF Then
     rsCheckPriority.MoveFirst
     If rsCheckPriority.Fields("CheckDate") = True Then
     MsgBox "One or more priority dates are greater than or equal to today's date. Resolve first before running Order Letters"
     Exit Function
     End If
    End If
    
    rsCheckPriority.Close
    Set rsCheckPriority = Nothing
     
    Select Case sType
     Case Is = "EP Validation"
     'EP Grant Date (Not available)
     Case Is = "Design"
     iPriorityDate = 6
     Case Is = "National Phase"
     iPriorityDate = 30 'iChapII
     Case Is = "Direct National"
     iPriorityDate = 12
    End Select
    
    'Work around here to fix Design deadline date increment
    If Forms!frmMatters!cmbInventionType.Value = 2 Then iPriorityDate = 6
     
    Select Case sType
    
    '*********************************************************************************
     Case Is = "National Phase"
     objWord.Selection.GoTo wdGoToBookmark, , , "Lang1"
     objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Lang1_NationalPhase").Insert objWord.Selection.Range, RichText:=True
     objWord.Selection.GoTo wdGoToBookmark, , , "Inv"
     objWord.Selection.TypeText "INVENTOR(S): see PCT publication" & vbCrLf
     objWord.Selection.TypeText vbCrLf & "PRIORITY: see PCT publication" & vbCrLf
     objWord.Selection.GoTo wdGoToBookmark, , , "Notes"
     objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Notes_NationalPhase").Insert objWord.Selection.Range, RichText:=True
     
     Set rsCountries = cmdStoredProc("spOrderLetterCountriesPCT", sMatterNumber, adVarChar).Execute
     Set rsRegions = cmdStoredProc("spOrderLetterRegionsPCT", sMatterNumber, adVarChar).Execute
     
     'National Countries
     Do While Not rsCountries.EOF
      With objWordDoc
      r = r + 1
      .Tables(1).Rows.Add
      .Tables(1).Rows(r).Cells(1).Range.Text = rsCountries.Fields("fldMGFileCRSel").Value
      .Tables(1).Rows(r).Cells(1).Range.Bold = False
      .Tables(1).Rows(r).Cells(2).Range.Text = rsCountries.Fields("fldCountry").Value
      .Tables(1).Rows(r).Cells(2).Range.Bold = False
      .Tables(1).Rows(r).Cells(3).Range.Text = IIf(IsNull(rsCountries.Fields("fldFirmName").Value), "", rsCountries.Fields("fldFirmName").Value)
      .Tables(1).Rows(r).Cells(3).Range.Bold = False
      End With
      rsCountries.MoveNext
     Loop
     If rsCountries.RecordCount > 0 Then objWordDoc.Tables(1).Rows(objWordDoc.Tables(1).Rows.Count).Delete
     
     'Region Countries
     'Set rsMembers = cmdStoredProc("dbo.spOrderLetterMembersByRegion", rsPriority.Fields("fldFilingDate").Value, adDate, rsRegions.Fields("fldPCTRegAbbr").Value, adChar).Execute
     
     Do While Not rsRegions.EOF
      With objWordDoc
      sMembers = ""
      r = r + 1
      .Tables(1).Rows.Add
      .Tables(1).Rows(r).Cells(1).Range.Text = rsRegions.Fields("fldPCTRegFileNo").Value
      .Tables(1).Rows(r).Cells(1).Range.Bold = False
      
       rsPriority.MoveFirst
       'Set rsMembers = cmdStoredProc("dbo.spOrderLetterEPMembers", rsPriority.Fields("fldFilingDate").Value, adDate).Execute
       Set rsMembers = cmdStoredProc("dbo.spOrderLetterMembersByRegion", rsPriority.Fields("fldFilingDate").Value, adDate, rsRegions.Fields("fldPCTRegAbbr").Value, adChar).Execute
       
       Do While Not rsMembers.EOF
       sMembers = sMembers & ", " & RTrim(LTrim(rsMembers.Fields("fldCountry")))
       rsMembers.MoveNext
       Loop
      .Tables(1).Rows(r).Cells(2).Range.Text = rsRegions.Fields("fldPCTRegName").Value & " - " & Right(sMembers, Len(sMembers) - 2)
      .Tables(1).Rows(r).Cells(2).Range.Bold = False
      .Tables(1).Rows(r).Cells(3).Range.Text = rsRegions.Fields("fldFirmName").Value
      .Tables(1).Rows(r).Cells(3).Range.Bold = False
      End With
      
      rsRegions.MoveNext
     Loop
     'If rsMembers.RecordCount > 0 Then objWordDoc.Tables(1).Rows(objWordDoc.Tables(1).Rows.Count).Delete
     
     Set rsPCTStatusInfo = cmdStoredProc("spPCTStatusInfo", sMatterNumber, adVarChar).Execute
     If Not (rsPCTStatusInfo.BOF Or rsPCTStatusInfo.EOF) Then
      objWordDoc.FormFields("FilingDate1").Result = rsPCTStatusInfo.Fields("fldDeadlineChapII").Value
      objWordDoc.FormFields("FilingDate2").Result = rsPCTStatusInfo.Fields("fldDeadlineChapII").Value
     End If
     Set rsPCTStatusInfo = Nothing
      
     objWordDoc.FormFields("Title").Result = rsMisc.Fields("fldInventTitle").Value
     objWordDoc.FormFields("MG1").Result = sMatterNumber
     objWordDoc.FormFields("MG2").Result = sMatterNumber
     objWordDoc.FormFields("ApplicationNumber").Result = Replace(IIf(IsNull(rsMisc.Fields("fldIntlAppNum").Value), "", rsMisc.Fields("fldIntlAppNum").Value), "PCT/US", "")
    
    '*********************************************************************************
     Case Is = "EP Validation"
     Set rsCountries = cmdStoredProc("spEPValidationCountries", sClientInv, adVarChar, sFileType, adVarChar).Execute
     Set rsEP = cmdStoredProc("spEPValidationInfo", sMatterNumber, adVarChar, sClientInv, adVarChar, sFileType, adVarChar).Execute
     
     objWord.Selection.GoTo wdGoToBookmark, , , "Lang1"
     objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Lang1_Validation").Insert objWord.Selection.Range, RichText:=True
     objWord.Selection.GoTo wdGoToBookmark, , , "Notes"
     objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Notes_Validation").Insert objWord.Selection.Range, RichText:=True
     objWordDoc.FormFields("MG1").Result = rsEP.Fields("fldPCTRegFileNo").Value
     objWordDoc.FormFields("MG2").Result = rsEP.Fields("fldPCTRegFileNo").Value
     
     'Leave dates blank for user to supply, as per FFD Department 11/02/2010
     'objWordDoc.FormFields("FilingDate1").Result = DateAdd("m", iPriorityDate, rsPriority.Fields("fldFilingDate").Value)
     'objWordDoc.FormFields("FilingDate2").Result = DateAdd("m", iPriorityDate, rsPriority.Fields("fldFilingDate").Value)
     
     objWordDoc.FormFields("ApplicationNumber").Result = IIf(IsNull(rsEP.Fields("fldPCTRegPatNo").Value), "", rsEP.Fields("fldPCTRegPatNo").Value)
     objWordDoc.FormFields("AssocRefNo").Result = IIf(IsNull(rsEP.Fields("fldPCTRegClientRef").Value), "", rsEP.Fields("fldPCTRegClientRef").Value)
     objWordDoc.FormFields("Title").Result = rsMisc.Fields("fldInventTitle").Value
     
     Do While Not rsCountries.EOF
      With objWordDoc
       r = r + 1
       .Tables(1).Rows.Add
       .Tables(1).Rows(r).Cells(1).Range.Text = rsCountries.Fields("fldMGFileCRSel").Value
       .Tables(1).Rows(r).Cells(1).Range.Bold = False
       .Tables(1).Rows(r).Cells(2).Range.Text = rsCountries.Fields("fldCountry").Value
       .Tables(1).Rows(r).Cells(2).Range.Bold = False
       .Tables(1).Rows(r).Cells(3).Range.Text = IIf(IsNull(rsCountries.Fields("fldFirmName").Value), "", rsCountries.Fields("fldFirmName").Value)
       .Tables(1).Rows(r).Cells(3).Range.Bold = False
      End With
      rsCountries.MoveNext
     Loop
     objWordDoc.Tables(1).Rows(objWordDoc.Tables(1).Rows.Count).Delete
    '*********************************************************************************
     Case Is = "Design"
     objWord.Selection.GoTo wdGoToBookmark, , , "Lang1"
     objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Lang1_Design").Insert objWord.Selection.Range, RichText:=True
     objWord.Selection.GoTo wdGoToBookmark, , , "Inv"
     objWord.Selection.TypeText "INVENTOR(S):" & vbCrLf & vbCrLf
       
      If Not rsInventors.BOF Or Not rsInventors.EOF Then
      For iInvRow = 1 To rsInventors.RecordCount
       objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Inv_Long").Insert objWord.Selection.Range, RichText:=True
       sInvText = sInvText & rsInventors.Fields("fldFullName").Value & vbCrLf & rsInventors.Fields("fldStreet").Value & vbCrLf & rsInventors.Fields("fldInvCity").Value & ", " & rsInventors.Fields("fldStateName").Value & " " & rsInventors.Fields("fldInvZip").Value & vbCrLf & rsInventors.Fields("fldCountry").Value & vbCrLf & rsInventors.Fields("CorpCountry").Value
       objWordDoc.Tables(2).Rows(iInvRow).Cells(2).Range.Text = sInvText
       rsInventors.MoveNext
       sInvText = ""
      Next iInvRow
      End If
     
     objWord.Selection.TypeText vbCrLf & "PRIORITIES:" & vbCrLf & vbCrLf
     
      If Not rsPriority.BOF Or Not rsPriority.EOF Then
      For iPriorityRow = 1 To rsPriority.RecordCount
       rsPriority.MoveFirst
       objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Priority_Long").Insert objWord.Selection.Range, RichText:=True
       sPriorityText = sPriorityText & rsPriority.Fields("fldCountry").Value & vbCrLf & rsPriority.Fields("fldApplicationNum").Value & vbCrLf & rsPriority.Fields("fldFilingDate").Value
       objWordDoc.Tables(3).Rows(iPriorityRow).Cells(2).Range.Text = sPriorityText
       rsPriority.MoveNext
       sPriorityText = ""
      Next iPriorityRow
      End If
     
     objWordDoc.FormFields("Title").Result = rsMisc.Fields("fldInventTitle").Value
     objWord.Selection.GoTo wdGoToBookmark, , , "Notes"
     objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Notes_Design").Insert objWord.Selection.Range, RichText:=True
    '*********************************************************************************
     Case Is = "Direct National"
     
     Set rsCountries = cmdStoredProc("spOrderLetterCountriesDN", sClientInv, adVarChar, sFileType, adChar).Execute
     Set rsRegions = cmdStoredProc("spDirectNationalRegionsSelected", sClientInv, adVarChar, sFileType, adChar).Execute
     Set rsEP = cmdStoredProc("spEPValidationInfo", sMatterNumber, adVarChar, sClientInv, adVarChar, sFileType, adVarChar).Execute
     
     If rsMisc.Fields("fldInvTypeID").Value = 2 Then
      objWord.Selection.GoTo wdGoToBookmark, , , "Lang1"
      objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Lang1_Design").Insert objWord.Selection.Range, RichText:=True
      objWord.Selection.GoTo wdGoToBookmark, , , "Notes"
      objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Notes_Design").Insert objWord.Selection.Range, RichText:=True
     Else
      objWord.Selection.GoTo wdGoToBookmark, , , "Lang1"
      objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Lang1_DirectNational").Insert objWord.Selection.Range, RichText:=True
      objWord.Selection.GoTo wdGoToBookmark, , , "Notes"
      objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Notes_DirectNational").Insert objWord.Selection.Range, RichText:=True
     End If
     
     objWord.Selection.GoTo wdGoToBookmark, , , "Inv"
     objWord.Selection.TypeText "INVENTOR(S):" & vbCrLf & vbCrLf
     objWordDoc.FormFields("MG1").Result = sClientInv
     objWordDoc.FormFields("MG2").Result = sClientInv
     objWordDoc.FormFields("FilingDate1").Result = DateAdd("m", iPriorityDate, rsPriority.Fields("fldFilingDate").Value)
     objWordDoc.FormFields("FilingDate2").Result = DateAdd("m", iPriorityDate, rsPriority.Fields("fldFilingDate").Value)
     
     Do While Not rsPriority.EOF
      sPriorityApplication = rsPriority.Fields("fldApplicationNum").Value & "; " & sPriorityApplication
      rsPriority.MoveNext
     Loop
     rsPriority.MoveFirst
     
     objWordDoc.FormFields("ApplicationNumber").Result = Left(RTrim(sPriorityApplication), Len(RTrim(sPriorityApplication)) - 1)
     objWordDoc.FormFields("Title").Result = rsMisc.Fields("fldInventTitle").Value
     
      Do While Not rsCountries.EOF
       With objWordDoc
       r = r + 1
       .Tables(1).Rows.Add
       .Tables(1).Rows(r).Cells(1).Range.Text = rsCountries.Fields("fldMGFileCRSel").Value
       .Tables(1).Rows(r).Cells(1).Range.Bold = False
    
       .Tables(1).Rows(r).Cells(2).Range.Text = rsCountries.Fields("fldCountry").Value
       .Tables(1).Rows(r).Cells(2).Range.Bold = False
       .Tables(1).Rows(r).Cells(3).Range.Text = IIf(IsNull(rsCountries.Fields("fldFirmName").Value), "", rsCountries.Fields("fldFirmName").Value)
       .Tables(1).Rows(r).Cells(3).Range.Bold = False
       End With
      rsCountries.MoveNext
      Loop
      
      Do While Not rsRegions.EOF
      With objWordDoc
       r = r + 1
       .Tables(1).Rows.Add
       .Tables(1).Rows(r).Cells(1).Range.Text = sClientInv & Mid(rsRegions.Fields(0).Value, 11, 2) & rsRegions.Fields("fldFileType").Value
       .Tables(1).Rows(r).Cells(1).Range.Bold = False
       
        rsPriority.MoveFirst
        Set rsMembers = cmdStoredProc("dbo.spOrderLetterEPMembers", rsPriority.Fields("fldFilingDate").Value, adDate).Execute
        
        Do While Not rsMembers.EOF
        sMembers = sMembers & ", " & RTrim(LTrim(rsMembers.Fields("fldCountry")))
        rsMembers.MoveNext
        Loop
        
       .Tables(1).Rows(r).Cells(2).Range.Text = Mid(rsRegions.Fields(0).Value, 11, 2) & " - " & Right(sMembers, Len(sMembers) - 2)
       .Tables(1).Rows(r).Cells(2).Range.Bold = False
       .Tables(1).Rows(r).Cells(3).Range.Text = rsRegions.Fields("fldFirmName").Value
       .Tables(1).Rows(r).Cells(3).Range.Bold = False
      End With
      rsRegions.MoveNext
      Loop
      
      objWordDoc.Tables(1).Rows(objWordDoc.Tables(1).Rows.Count).Delete
      
      objWord.Selection.GoTo wdGoToBookmark, , , "Inv"
      objWord.Selection.EndKey wdLine
      objWord.Selection.TypeText vbCrLf & vbCrLf
      
      If Not rsInventors.BOF Or Not rsInventors.EOF Then
      For iInvRow = 1 To rsInventors.RecordCount
       objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Inv_Long").Insert objWord.Selection.Range, RichText:=True
       sInvText = sInvText & rsInventors.Fields("fldFullName").Value & vbCrLf & rsInventors.Fields("fldStreet").Value & vbCrLf & rsInventors.Fields("fldInvCity").Value & ", " & rsInventors.Fields("fldStateName").Value & " " & rsInventors.Fields("fldInvZip").Value & vbCrLf & rsInventors.Fields("fldCountry").Value & vbCrLf & rsInventors.Fields("CorpCountry").Value
       objWordDoc.Tables(2).Rows(iInvRow).Cells(2).Range.Text = sInvText
       rsInventors.MoveNext
       sInvText = ""
      Next iInvRow
      End If
      
    
     objWord.Selection.TypeText vbCrLf & "PRIORITIES:" & vbCrLf & vbCrLf
     
      If Not rsPriority.BOF Or Not rsPriority.EOF Then
      For iPriorityRow = 1 To rsPriority.RecordCount
       objWord.ActiveDocument.AttachedTemplate.AutoTextEntries("Priority_Long").Insert objWord.Selection.Range, RichText:=True
       sPriorityText = sPriorityText & rsPriority.Fields("fldCountry").Value & vbCrLf & rsPriority.Fields("fldApplicationNum").Value & vbCrLf & rsPriority.Fields("fldFilingDate").Value
       objWordDoc.Tables(3).Rows(iPriorityRow).Cells(2).Range.Text = sPriorityText
       rsPriority.MoveNext
       sPriorityText = ""
      Next iPriorityRow
      End If
    
    End Select
    
    If objWordDoc.Bookmarks.Exists("EPAssociate") = True Then objWordDoc.FormFields("EPAssociate").Result = rsEP.Fields("fldFirmName").Value
    objWord.Selection.GoTo wdGoToBookmark, , , "Applicant"
    objWord.Selection.InsertAfter rsMisc.Fields("fldClientName").Value & vbCrLf & rsMisc.Fields("fldAddress") & vbCrLf & rsMisc.Fields("fldCity") & ", " & rsMisc.Fields("fldStateName") & " " & rsMisc.Fields("fldZip").Value & vbCrLf & rsMisc.Fields("fldCountry").Value
    If objWordDoc.Bookmarks.Exists("ApplicantName") = True Then objWordDoc.FormFields("ApplicantName").Result = rsMisc.Fields("fldClientName").Value
    If objWordDoc.Bookmarks.Exists("StateCorp") = True Then objWordDoc.FormFields("StateCorp").Result = rsMisc.Fields("CorpSt").Value
    If objWordDoc.Bookmarks.Exists("PubNumber1") = True Then objWordDoc.FormFields("PubNumber1").Result = Replace(IIf(IsNull(rsMisc.Fields("fldAppPublishNo").Value), "", rsMisc.Fields("fldAppPublishNo").Value), "WO ", "")
    If objWordDoc.Bookmarks.Exists("PubNumber2") = True Then objWordDoc.FormFields("PubNumber2").Result = Replace(IIf(IsNull(rsMisc.Fields("fldAppPublishNo").Value), "", rsMisc.Fields("fldAppPublishNo").Value), "WO ", "")
    objWord.Selection.HomeKey wdStory
    MsgBox "Order Letter Complete"
    
    
    'release the objects
    Set objWordDoc = Nothing
    Set objWord = Nothing
    
    End Function

    As Andrey suggested Late binding is preferable, no problems with References, altough that's not the cause of your problem.

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    • Edited by danishani Wednesday, August 17, 2011 5:50 PM corrected typo
    Wednesday, August 17, 2011 5:48 PM
  • Thank you both for the ideas.

    Remind me again which is recommended as best practice, late or early binding?
    Facebook: Zamdrist
    Roleplaying: Elliquiy
    Wednesday, August 17, 2011 6:01 PM
  • Well the best advantage using Late binding is not to worry about the References (different Word versions).

    Then Early binding is faster in executing the code then Late binding.

     

    Below some reads:

    http://support.microsoft.com/kb/245115

    http://word.mvps.org/faqs/interdev/earlyvslatebinding.htm

    http://www.granite.ab.ca/access/latebinding.htm

     

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Wednesday, August 17, 2011 6:09 PM
  • The happy medium is to develop with early and to distribute with late.

    Early binding allows using IntelliSense and corresponding constant like wdGoToBookmark. These constants are not available w/o having an appropriate reference (i.e. with full late binding). So, at least try to change these 2 lines but don't remove the reference to Word library. If it doesn't help, then remove the reference to the Word library and try to compile. Frankly, can't see anything wrong with your code. There should be no difference between Automation concepts in A03/07/10.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru/
    Wednesday, August 17, 2011 6:10 PM
  • If the code, as Andrey has suggested, isn't wrong at all (and I don't see why there would be a problem), you might try just doing a DECOMPILE (make a copy of the db first though just as a precaution) and see if that helps.  Sometimes that will "reset" things which can get off with VBA, especially during version changes.
    Bob Larson, Access MVP 2008-2010, 2011
    Wednesday, August 17, 2011 6:16 PM
  • If the code, as Andrey has suggested, isn't wrong at all (and I don't see why there would be a problem), you might try just doing a DECOMPILE (make a copy of the db first though just as a precaution) and see if that helps.  Sometimes that will "reset" things which can get off with VBA, especially during version changes.
    Bob Larson, Access MVP 2008-2010, 2011
    Decompile? That's functionality I've not heard of? Bear in mind I'm running as an ADP, testing and ultimately compiling to an ADE.

    Facebook: Zamdrist
    Roleplaying: Elliquiy
    Wednesday, August 17, 2011 7:39 PM
  • Check this about decompilation http://www.fmsinc.com/MicrosoftAccess/Performance/Decompile.asp
    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, August 17, 2011 7:45 PM
  • Then Early binding is faster in executing the code then Late binding.

    In theory, but I've never seen any difference in practice. If somebody can show an example when this difference has place, it will be interesting.
    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, August 17, 2011 7:50 PM
  • Hi Andrey,

    I just did a test for the heck of it, not to complex code, and there is indeed hardly any difference in executing it.

     

    Cheers,

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Wednesday, August 17, 2011 8:20 PM
  • Gah! No luck. Tried the decompile method without success.

    Fortunately I'm able to repeat the error on my computer. Frustrating because I've done this sort of thing in my sleep for years now. Now though I'm all turned around.

    Ideally the code should launch a new instance of Word, add a new document from a template to that instance of Word, and then proceed, i.e. .Selection.Goto wdBookmark (etc.).

    I've tried different variations and means of binding, but with varying results.


    Rolling back to where I was in the beginning, here is what I have:

    Public Function GenerateOrderLetter(sClientInv As String, sFileType As String, sType As String, Optional sMatterNumber As String)
    
    Dim objWord As Word.Application
    Dim objWordDoc As Word.Document
    '
    '
    '
    '
    Set objWord = New Word.Application
    Set objWordDoc = New Word.Document
    Set objWordDoc = objWord.Documents.Add("q:\PCT2000\templates\OrderLetter2K9.dot")
    objWord.Visible = True
    '
    '
    '
    '
    Set objWordDoc = Nothing
    Set objWord = Nothing
    

    On this line here, is where I subsequently get the error: Object Variable or With Block Variable Not Set

    objWord.Selection.GoTo wdGoToBookmark, , , "Lang1"

    Oddly, with Word closed the first time, it runs WIHTOUT error, subsequent running of the procedure WITH Word open, it fails on the above error.

    Gah! LOL


    Facebook: Zamdrist
    Roleplaying: Elliquiy
    Wednesday, August 17, 2011 8:24 PM
  • So I figured I'd try the same code in a brand new database, outside of the current project and see what happens.

    Same behavior. Runs once correctly, leave the document and instance of Word open, the second time it fails.

    Code:

    Public Function OpenWord()
    
    Dim objWord As Word.Application
    Dim objWordDoc As Word.Document
    
    Set objWord = New Word.Application
    Set objWordDoc = New Word.Document
    Set objWordDoc = objWord.Documents.Add("Q:\PCT2000\Templates\AppFiledLetter2011.dot")
    objWord.Visible = True
    
    objWord.Selection.GoTo wdGoToBookmark, , , "ApplicantName1"
    
    Set objWordDoc = Nothing
    Set objWord = Nothing
    
    End Function

     

    An interesting side note. If I run the code (the second time, leaving Word open), in debug mode, magically it works! WTH?


    Facebook: Zamdrist
    Roleplaying: Elliquiy
    Wednesday, August 17, 2011 8:38 PM
  • Try explicit sequentional object setting:

    Dim sel As Word.Selection
    Set sel = objWord.Selection
    sel.GoTo wdGoToBookmark, , , "ApplicantName1"

    It's just a suggestion. I faced the issue one time when Excel automation had the same error until using the way above described. Had some problems with xlSheet.Range("A1"), but solved with Dim xlRange As Excel.Range etc. At least, give a try, who knows. :)

     


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, August 17, 2011 8:49 PM
  • Try explicit sequentional object setting:

     

    Dim sel As Word.Selection
    Set sel = objWord.Selection
    sel.GoTo wdGoToBookmark, , , "ApplicantName1"
    

     

    It's just a suggestion. I faced the issue one time when Excel automation had the same error until using the way above described. Had some problems with xlSheet.Range("A1"), but solved with Dim xlRange As Excel.Range etc. At least, give a try, who knows. :)

     


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Thanks. Same behavior, same error. Fails second time around

    Facebook: Zamdrist
    Roleplaying: Elliquiy
    Wednesday, August 17, 2011 9:02 PM
  • Oddly, with Word closed the first time, it runs WIHTOUT error, subsequent running of the procedure WITH Word open, it fails on the above error.

    It sounds like Andrey has it spot on.  This is EXACTLY the same behaviour that happens when you don't use an instantiated object for something that needs to be as it opens another instance of the application which can't be referenced just to handle the item.  Then it will cause the code to fail on subsequent runs until you close Access and reopen, at which time it kills the hanging process so it isn't still open.

    The only problem is that Andrey was having you try with the wrong item.  Selection is fine where you used it because it was tied to an instantiated object - objWord.  The trick is going to be to go through the ENTIRE code to find where it isn't referenced to an instantiated object.  I will look but I thought I had better give you this first.  And take a look in your Task Manager under processes and you should probably find an EXTRA WORD.EXE hanging about, if you haven't closed Access yet.


    Bob Larson, Access MVP 2008-2010, 2011
    Wednesday, August 17, 2011 9:13 PM
  • Bob,

    in this context this step isn't clear for me:

    Set objWordDoc = New Word.Document
    Set objWordDoc = objWord.Documents.Add("Q:\PCT2000\Templates\AppFiledLetter2011.dot")
    
    


     Is it correct? I mean one variable - double setting. It seams that first "= New" is extra one? I didn't play with Word automation, Excel and Outlook only, but as a simple comparison:

    'ADO
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open ...
    
    'DAO
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(...)
    
    Two different ways but they are never combined/mixed. 


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, August 17, 2011 9:24 PM
  • Bob,

    in this context this step isn't clear for me:

    Set objWordDoc = New Word.Document
    
    
    
    Set objWordDoc = objWord.Documents.Add("Q:\PCT2000\Templates\AppFiledLetter2011.dot")
    
    
    
    
    
    
    
    

     


     Is it correct? I mean one variable - double setting. It seams that first "= New" is extra one? I didn't play with Word automation, Excel and Outlook only, but as a simple comparison:

     

    'ADO
    
    
    
    Dim rs As ADODB.Recordset
    
    
    
    Set rs = New ADODB.Recordset
    
    
    
    rs.Open ...
    
    
    
    
    
    
    
    'DAO
    
    
    
    Dim rs As DAO.Recordset
    
    
    
    Set rs = CurrentDb.OpenRecordset(...)
    
    
    
    
    Two different ways but they are never combined/mixed. 

     


    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru

    Yeah, I didn't see that before.  That is not good.  They should have it like:

    Dim objWordDoc As Word.Document 
    Set objWordDoc = objWord.Documents.Add("Q:\PCT2000\Templates\AppFiledLetter2011.dot")

     And not use the New Word Document




    Bob Larson, Access MVP 2008-2010, 2011
    • Edited by zzzzzzz Wednesday, August 17, 2011 9:33 PM fix messed up info that I originally typed
    Wednesday, August 17, 2011 9:31 PM
  • Yes, I thought as well. But! I've just tested it and couldn't reproduce this error. All works fine for me even with double settings. And after second run (with leaving first document opened) there are only 2 WINWORD.EXE processes as expected.
    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Wednesday, August 17, 2011 9:37 PM
  • Andrey:

     

    So that should eliminate that from being the suspect.  If I had a copy of the database and Word template it would be easier to troubleshoot but since I don't then it is still a searching game to find what could be only a very small, single item that is out there causing it.  All it takes is one reference to something that should be connected to an instantiated object that isn't but right now I'm not seeing it. 

    My question for Zamdrist is where does wdGoToBookmark come from?  Is that your own variable or is it a newer Word Constant?  I am only working with 2003 here at work (I have 2010 at home) so I can't tell for sure.


    Bob Larson, Access MVP 2008-2010, 2011
    Wednesday, August 17, 2011 9:54 PM
  • I'm at home now, not in the same environment but with Access 2010, Word 2010 and Windows 7. Using the most recent code I supplied above, I wasn't able to repeat the same error. No error occurred. While I suspect a rouge Word Add-In I'll also review the above code clarifications and make adjustments back at work tomorrow.

    Bob - wdGotoBookmark is a named constant, a GoTo location type, in this case a bookmark. It's applicable to all recent versions of Word, including Word 2010.

    Thanks all. I'll report back. You're right, I shouldn't have to create a 'New Word.Document' but merely add the template to the Word.Application Documents collection.


    Facebook: Zamdrist
    Roleplaying: Elliquiy
    Wednesday, August 17, 2011 10:09 PM
  • I believe this code below is the most terse, most correct means to launch Word, add a template, and thereafter use the methods and properties of Word via Access, or really any Office application.

    Public Function OpenWord()
    
    Dim objWord As Word.Application
    Dim objWordDoc As Word.Document
    
    Set objWord = New Word.Application
    objWord.Documents.Add "C:\MyWordLocation\Doc1.dotm"
    objWord.Visible = True
    
    objWord.Selection.GoTo wdGoToBookmark, , , "TestBookmark"
    
    Set objWordDoc = Nothing
    Set objWord = Nothing
    
    End Function


    If someone knows of a more succinct means of doing the same thing, I'd like to see it. In all actuality I could probably leave out 'Set objWordDoc = Nothing' as I never actually 'set' it to anything. 

    I have to get my administrator to take me out of a policy group before I can exclude any extraneous add-ins. As it is now, in my work environment, even the code above fails (the second time around).


    Facebook: Zamdrist
    Roleplaying: Elliquiy
    Wednesday, August 17, 2011 10:39 PM
  • Hi again,

     

    Interesting thread and I could not help myself to investigate more and get the cause of your error

    I could reproduce the error 91, by doing the following:

    Private Sub cmdTest_Click()
    
    Dim strDoc As String
    Dim objApp As Word.Application
    Dim objDoc As Word.Document
    
       
       Set objApp = New Word.Application
     
       strDoc = "C:\Users\Daniel\Documents\test.dotx"
       Set objdDoc = Documents.Add(strDoc)
       
       objApp.Visible = True
       objApp.Selection.GoTo wdGoToBookmark, , , "testbookmark"
       
      
     Set objApp = Nothing
     Set objDoc = Nothing
    
    
    End Sub
    

    In debug the Goto () command lits up which causes the error. I am not entirely sure, but the Set objDoc might be the cause of this.

    Infact this Object is not needed at all. So the following code works well without error:

    Private Sub cmdTest2_Click()
     
    Dim strDoc As String
    Dim objApp As Word.Application
    
       
       Set objApp = New Word.Application
     
       strDoc = "C:\Users\Daniel\Documents\test.dotx"
       
      With objApp
       .Documents.Add (strDoc)
       .Visible = True
       .Selection.GoTo wdGoToBookmark, , , "testbookmark"
      End With
    
     
     Set objApp = Nothing
    
    End Sub
    


    Hope this helps,

     

     

     


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"
    Thursday, August 18, 2011 1:28 AM
  • Daniel,

    I share your obsession. Thank you for sharing your observations. I can access my desktop at work via VPN and Remote Desktop so I can test out the code.

    Using code much like yours, the key point being not creating a new Document instance, I can repeat the error (91) but only after running the code a second, and subsequent times. If I close all instances of Word, then I can repeat the process again.

    Couple observations. One, I had Task Manager open and observed only one instance of Word running for each time I executed the code. Two, when it does fail, the instance of Word doesn't add the template, it is an empty (document-less) instance of Word. This probably explains why the error is generated. No document, no valid selection object.

    Another point to emphasize. At home, outside of my work environment, the following code runs error-free every time.

    Public Function OpenWord()
    
    Dim objWord As Word.Application
    Dim objWordDoc As Word.Document
    
    Set objWord = New Word.Application
    objWord.Documents.Add "Q:\PCT2000\Templates\AppFiledLetter2011.dot"
    objWord.Visible = True
    
    objWord.Selection.GoTo wdGoToBookmark, , , "ApplicantName1"
    
    Set objWordDoc = Nothing
    Set objWord = Nothing
    
    End Function



    Facebook: Zamdrist
    Roleplaying: Elliquiy
    Thursday, August 18, 2011 3:01 AM
  • Interesting observation. By moving the line: objWord.Visible = True immediately following it's creation, I no longer get the error.

    It may be that since the template is located on a network share, it takes a moment longer to load, but the code has already moved on to the next line, which fails because the template hasn't yet loaded. 

    .
    .
    .

    This is definately the problem. I know this because if I step through the code line by line, I also do not get the error. Why, because each line is forced to execute before moving to the next.

     


    Facebook: Zamdrist
    Roleplaying: Elliquiy

    Thursday, August 18, 2011 3:17 AM
  • Did you try to use a local template to confirm this? If this is the only reason for the error, then I'm sure you know a workaround.
    Andrey V Artemyev | Saint-Petersburg, Russia
    Russian blog artemyev.biztoolbox.ru
    English blog enblog.biztoolbox.ru
    Thursday, August 18, 2011 7:08 AM
  • Here at work now and testing again. I copied the template from the netowrk share to the root of my C drive. I can still repeat the error, but it I move up objWord.Visible to immediately follow the instance of the Word.Application object, I no longer get the error.

    The real test will come at the user's machine. So the jury is still out. I'll report back.

    It may have as much (or more!) to do with the size of the template as the location of it. It isn't enormous by any means however, 62KB.

    This has been a useful exchange. I wish I wasn't the only programmer type in the office.
    Facebook: Zamdrist
    Roleplaying: Elliquiy
    Thursday, August 18, 2011 1:33 PM
  • Argh! The user is out of the office today, so I can't do a real, live test. Nevertheless I tweaked the code, as well as did a search through other areas of the code for similar patterns and tweaked them also.

    In some cases, you do have to set an object variable to Word.Document in order to access methods and properties that are only available in that in context. Such as FormFields for instance. Though ActiveDocument too would also work I imagine.

    I'll follow up with a summary after I have a chance to test on the user's machine.


    Facebook: Zamdrist
    Roleplaying: Elliquiy

    Thursday, August 18, 2011 2:13 PM
  • The code tweak did resolve the problem for the user. It was a simple matter of first making the Word.Application instance visible before doing anything. Bear in mind, the code works fine either way in Office XP/2003, and likely earlier versions. But in Office 2010 at least, the order the code is written can, in some cases, cause issues.

    I suspect it has something do with both processor speed and the version of Office, or more specifically the version of VBA. The code was moving along to execute the next line before the template had finished loading into memory. The line failed, and the whole operation stopped. I observed this as a new instance of Word appeared, but no template was loaded.

    For the record, this code works (Any recent version of Office, not just 2010)

    Public Function OpenWord()
    
    Dim objWord As Word.Application
    Dim objWordDoc As Word.Document
    
    Set objWord = New Word.Application
    objWord.Visible = True
    objWord.Documents.Add "C:\AppFiledLetter2011.dot"
    objWord.Selection.GoTo wdGoToBookmark, , , "ApplicantName1"
    
    Set objWordDoc = Nothing
    Set objWord = Nothing
    
    End Function

    I suspect an earlier, and yet unresolved issue, is related (maybe), though has nothing to do with Office 2010.

    Method 'Add' of object '_CommandBars' Failed
    Facebook: Zamdrist
    Roleplaying: Elliquiy
    • Marked as answer by ITMn0403 Friday, August 19, 2011 8:41 PM
    Friday, August 19, 2011 8:13 PM