none
Object has been deleted RRS feed

  • Question


  • Hello
    I am working on a VBA function on excel to write information from the spread sheet to msword bookmark point its all working fine until  the look hit the inputbox where the user enter the value
    And once the when the next loop start it through Object has been deleted error
    All the help would be appreciated
    Thanks

    Set oDoc = oWordApp.Documents.Open(sNewFilename)
        For Each oBookmark In oDoc.Bookmarks
            For i = 1 To 50
              
                If LCase(oBookmark.Name) = "site_number" And LCase(ColumnHeaders(i)) = "subject_id" Then
                    oBookmark.Range.Text = Mid(Sheet1.Cells(RowNumber, Columns(i)).Text, 1, 4)
                   
                  Else
                    If LCase(ColumnHeaders(i)) = LCase(oBookmark.Name) Then
                      oBookmark.Range.Text = Sheet1.Cells(RowNumber, Columns(i)).Text
                   End If
               End If
               If (LCase(oBookmark.Name) = "fax_number") Then
                 oBookmark.Range.Text = InputBox(Prompt:="Please Enter Fax Number", _
                  Title:="Fax Number ")
                 
                End If
              
            Next i
        Next oBookmark


    Wednesday, March 14, 2012 2:17 PM

Answers

  • When you set oBookmark.Range.Text, you destroy the bookmark - only the text remains.

    If you try to set the text for the same bookmark more than once in the For i = 1 To 50 ... Next i loop, the bookmark will be gone the second time. So you should exit the loop once you've found a match and set the text:

        Set oDoc = oWordApp.Documents.Open(sNewFilename)
        For Each oBookmark In oDoc.Bookmarks
            For i = 1 To 50
                If (LCase(oBookmark.Name) = "fax_number") Then
                    oBookmark.Range.Text = InputBox(Prompt:="Please Enter Fax Number", Title:="Fax Number ")
                    Exit For
                ElseIf LCase(oBookmark.Name) = "site_number" And LCase(ColumnHeaders(i)) = "subject_id" Then
                    oBookmark.Range.Text = Mid(Sheet1.Cells(RowNumber, Columns(i)).Text, 1, 4)
                    Exit For
                ElseIf LCase(ColumnHeaders(i)) = LCase(oBookmark.Name) Then
                    oBookmark.Range.Text = Sheet1.Cells(RowNumber, Columns(i)).Text
                    Exit For
                End If
            Next i
        Next oBookmark


    Regards, Hans Vogelaar

    • Marked as answer by Rushdy Najath Wednesday, March 14, 2012 3:23 PM
    Wednesday, March 14, 2012 3:05 PM

All replies

  • When you set oBookmark.Range.Text, you destroy the bookmark - only the text remains.

    If you try to set the text for the same bookmark more than once in the For i = 1 To 50 ... Next i loop, the bookmark will be gone the second time. So you should exit the loop once you've found a match and set the text:

        Set oDoc = oWordApp.Documents.Open(sNewFilename)
        For Each oBookmark In oDoc.Bookmarks
            For i = 1 To 50
                If (LCase(oBookmark.Name) = "fax_number") Then
                    oBookmark.Range.Text = InputBox(Prompt:="Please Enter Fax Number", Title:="Fax Number ")
                    Exit For
                ElseIf LCase(oBookmark.Name) = "site_number" And LCase(ColumnHeaders(i)) = "subject_id" Then
                    oBookmark.Range.Text = Mid(Sheet1.Cells(RowNumber, Columns(i)).Text, 1, 4)
                    Exit For
                ElseIf LCase(ColumnHeaders(i)) = LCase(oBookmark.Name) Then
                    oBookmark.Range.Text = Sheet1.Cells(RowNumber, Columns(i)).Text
                    Exit For
                End If
            Next i
        Next oBookmark


    Regards, Hans Vogelaar

    • Marked as answer by Rushdy Najath Wednesday, March 14, 2012 3:23 PM
    Wednesday, March 14, 2012 3:05 PM
  • thanks yes it works
    Wednesday, March 14, 2012 3:23 PM
  • Hi, I am using office 2010 in Win 7 x64

    Function f() Dim a As Object Dim b As Object Dim k As Integer Dim cnt As Integer Dim mm As Integer

    cnt = ThisDocument.Shapes.Count Set a = ThisDocument.Shapes(1) Set b = a.GroupItems(1) k = a.GroupItems.Count mm = b.height

    End Function

    I have one group (in code it is 'a') and there are three items in it. Now I am doing "mm = b.height",

    It gives me this error : "Object has been deleted"

    Monday, August 5, 2013 7:47 PM
  • It's not clear what your function is supposed to do - it doesn't do anything and it doesn't return a value.

    It would be better to start a new thread for this question - it has nothing to do with the rest of the thread. In the new thread, please indicate what you want to accomplish.


    Regards, Hans Vogelaar

    Monday, August 5, 2013 7:54 PM