locked
Getting "error 13 Type mismatch" error with Range variable RRS feed

  • Question

  • I have some code that replaces text at a Word bookmark with a blank so new text can be copied from Excel to Word. On the line,

        Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range

    I get the 'error 13 Type mismatch' error. Here is the code:

    Sub UpdateBookmark(BookmarkToUpdate As String, TestToUse As String)
        Dim BMRange As Range
        Set BMRange = ActiveDocument.Bookmarks(BookmarkToUpdate).Range
        BMRange.Text = TextToUse
        ActiveDocument.Bookmarks.Add BookmarkToUpdate, BMRange
    End Sub

    The subroutine is run from other code like this:

        UpdateBookmark "B4", ""
        Range("B4").Copy
        wrdApp.ActiveDocument.Bookmarks("B4").Select
        wrdApp.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, _
     Placement:=wdInLine, DisplayAsIcon:=False
        Application.CutCopyMode = False 'Clears clipboard

    I am copying data from cells in Excel to bookmarks in word.  What do I need to change in the UpdateBookmark subroutine to fix the error. I thought Range is the proper use here.

    Tuesday, June 9, 2015 4:34 PM

Answers

  • HI TWIKLE,

    >>I get the 'error 13 Type mismatch' error. <<

    Since you have the reference libraries enables for both Excel and Word.  Please ensure that the Range is under the correct namespace, since both Excel and Word contain Range object.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.Hl Friday, July 10, 2015 9:07 AM
    Tuesday, June 23, 2015 6:29 AM

All replies

  • Hello:

    Have you posted this on the Word Developer's forum yet?  If not, perhaps you could also post it there, because I have received answers in the past regarding inserting data from Excel into Word.

    A few questions:  Did you add the Microsoft Word Reference Library to the References section of your VBE?

    If you look at the following code, while it doesn't address your actual issue, it shows how to manipulate Word Objects from Excel.  Again, make sure you added the Word Reference Library.

    This code was from a project in which I wrote data to Word objects:

    Option Explicit
    Dim strFilePathWord As String
    Dim dlgOpenFile As FileDialog
    Dim intNoOfRows
    Dim intNoOfColumns
    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    Dim objRange As Word.Range
    Dim shp As Word.Shape
    Dim i As Integer
    Dim j As Integer
    Dim strMyData As String
    Dim strMyHeader As String
    Dim wkbThisWorkbook As Workbook
    Dim wksDataToTransfer As Worksheet
    Dim rngTextFrame As Range
    
    Public Sub WriteToTextBoxExisting()
    
    Set wkbThisWorkbook = ThisWorkbook
    Set wksDataToTransfer = wkbThisWorkbook.Sheets("SampleData")
    
    ' *******************************************************
    ' Get An Existing Word Doc File Name and Path
    ' *******************************************************
    Call SelectWordDocumentToOpen
    
    If strFilePathWord = "" Then
        MsgBox ("No File Selected")
        Exit Sub
    End If
    
    ' *******************************************************
    ' Build The Header String
    ' *******************************************************
    'strMyHeader = vbTab & wksDataToTransfer.Cells(1, 1).Value & vbTab & wksDataToTransfer.Cells(1, 2).Value
    ' *******************************************************
    ' Build A String Containing Excel Row & Column Data
    ' *******************************************************
    strMyData = ""
    intNoOfRows = 26
    intNoOfColumns = 2
    
    For i = 2 To intNoOfRows
         For j = 1 To intNoOfColumns
            If j = 1 Then
                strMyData = strMyData & wksDataToTransfer.Cells(i, j).Value & vbTab
            Else
                If i <> 26 Then
                    strMyData = strMyData & wksDataToTransfer.Cells(i, j).Value & vbCrLf
                Else
                    strMyData = strMyData & wksDataToTransfer.Cells(i, j).Value
                End If
            End If
         Next j
    Next i
    
    ' *******************************************************
    ' Create The Word Objects from Excel And Open An
    ' Existing Word Document
    ' *******************************************************
    Set objWord = CreateObject("Word.Application")
    objWord.Visible = True
    Set objDoc = objWord.Documents.Open(strFilePathWord)
    
    ' *******************************************************
    ' Insert The Header Text
    ' *******************************************************
    'For Each shp In objDoc.Shapes
    '    If shp.Name = "Text Box 12" Then
    '        shp.TextFrame.TextRange.Text = strMyHeader
    '        Exit For
    '    End If
    'Next
    
    'Set objRange = shp.TextFrame.TextRange
    'objRange.ParagraphFormat.SpaceBefore = 0
    'objRange.ParagraphFormat.SpaceAfter = 0
    
    ' *******************************************************
    ' Insert Text Into A Textbox
    ' Set Up Bullets Before loading text into textbox
    ' *******************************************************
    For Each shp In objDoc.Shapes
        If shp.Name = "Text Box 11" Then
            Call SetUpBullets
            shp.TextFrame.TextRange.Text = strMyData
            Exit For
        End If
    Next
    
    ' *******************************************************
    ' At This Point I Would Like Select Certain Lines within
    ' The Textbox and Assign a Different Font
    ' *******************************************************
    With shp.TextFrame.TextRange.Find
            .Text = "Item*^13"
            .Replacement.Text = "^&"
            .MatchWildcards = True
            .ClearFormatting
            .Replacement.ClearFormatting
            With .Replacement.Font
                .Name = "Times New Roman"
                .Size = 14
                .Bold = True
                .ColorIndex = wdRed
            End With
            .Execute Replace:=wdReplaceOne
    End With
    
    objDoc.Activate
    
    ' shp.TextFrame.TextRange.GoTo What:=wdGoToLine, Which:=wdGoToAbsolute, Count:=1
    ' shp.TextFrame.TextRange.ListFormat.RemoveNumbers NumberType:=wdNumberParagraph
    ' shp.TextFrame.TextRange.Collapse wdCollapseEnd
    ' shp.TextFrame.TextRange.ListFormat.RemoveNumbers NumberType:=wdNumberParagraph
    ' shp.TextFrame.TextRange.InsertBefore ("Hello")
    
    End Sub
    
    Private Sub SelectWordDocumentToOpen()
    
    strFilePathWord = ""
    Set dlgOpenFile = Application.FileDialog(msoFileDialogOpen)
    With dlgOpenFile
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add "Word Document", "*.docx"
        .Filters.Add "2003 Word Document", "*.doc"
        .FilterIndex = 2
        .Show
        If .SelectedItems.Count < 1 Then
            Exit Sub
        End If
        strFilePathWord = .SelectedItems(1)
    End With
    
    End Sub
    
    Private Sub SetUpBullets()
      With objWord.ListGalleries(wdBulletGallery).ListTemplates(1).ListLevels(1)
            .NumberFormat = ChrW(61558)
            .TrailingCharacter = wdTrailingTab
            .NumberStyle = wdListNumberStyleBullet
            .NumberPosition = objWord.InchesToPoints(0.25)
            .Alignment = wdListLevelAlignLeft
            .TextPosition = objWord.InchesToPoints(0.5)
            .TabPosition = wdUndefined
            .ResetOnHigher = 0
            .StartAt = 1
            .Font.Name = "Wingdings"
            .LinkedStyle = ""
        End With
        objWord.ListGalleries(wdBulletGallery).ListTemplates(1).Name = ""
        shp.TextFrame.TextRange.ListFormat.ApplyListTemplateWithLevel ListTemplate:= _
            objWord.ListGalleries(wdBulletGallery).ListTemplates(1), ContinuePreviousList:= _
            False, ApplyTo:=wdListApplyToSelection, DefaultListBehavior:= _
            wdWord10ListBehavior
    End Sub
    


    Rich Locus, Logicwurks, LLC

    http://www.logicwurks.com

    Tuesday, June 9, 2015 11:55 PM
  • I have the reference libraries enables for both Excel and Word.  I can copy cells from Excel to Word bookmarks. I just want to have code that clears the bookmarks before pasting text to them so the user can update the Word document without duplicating bookmark entries.
    Wednesday, June 10, 2015 4:53 PM
  • HI TWIKLE,

    >>I get the 'error 13 Type mismatch' error. <<

    Since you have the reference libraries enables for both Excel and Word.  Please ensure that the Range is under the correct namespace, since both Excel and Word contain Range object.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.Hl Friday, July 10, 2015 9:07 AM
    Tuesday, June 23, 2015 6:29 AM