PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist

[newb]

hello everyone,

I am trying to link a word doc template with a complex excel financial model. I named all the cells in excel that i would like transferred and i build out tables in word and bookmarked all the "cells" for the corresponding names in excel. The thing is, not all the columns and rows in word always apply. so i want the users in word to be able to cut out columns and rows that are N/A. however, it appears when they do this, that bookmarks are deleted, which I think is resulting in this runtime error...

is there a way around this? is there a way to alter the code so that if the cell that contain the bookmark is no longer there, it just proceeds anyway? i have enclosed an excerpt from the code below. any help is greatly appreciated. the line that is getting debugged is " Set cl = item.Range.Cells(1)"

    Dim item As Bookmark
    Dim itemName As String
    Dim a As Long
    
    For a = wDoc.Bookmarks.Count To 1 Step -1
            Set item = wDoc.Bookmarks(a)
                itemName = item.Name
            Set cl = item.Range.Cells(1)
                fValue = RangeValue(itemName)
                    If fValue <> "" Then
                        If IsNumeric(fValue) Then
                            Select Case formatCell
                                Case "Percentage"
                                    item.Range.Text = Format(fValue, formatCell)
                                Case "0%"
                                    item.Range.Text = Format(fValue, formatCell)
                                Case "0.0%"
                                    item.Range.Text = Format(fValue, formatCell)
                                Case "0.00%"
                                    item.Range.Text = Format(fValue, formatCell)
                                Case "0.00""x"""
                                    item.Range.Text = Format(fValue, "0.00""x""")
                                Case Else
                                    item.Range.Text = Format(fValue, "#,##0;(#,##0);"" - """)
                            End Select
                        Else
                            item.Range.Text = fValue
                        End If
                    End If
            wDoc.Bookmarks.Add itemName, cl.Range
    Next

[Guessed]

The bookmark must be there otherwise it wouldn't be in the loop, however the bookmark may not include a table cell so you should avoid that complication and just write to the bookmarked range instead. Note that doing this deletes the bookmark so you need to re-add it at the end (like you do).

Is the RangeValue function looking Word or Excel? I presume it must be in Excel and it is not clear that the function tests whether the named range exists in Excel so that could fail if that is the case.

Dim aRng as Range
For a = wDoc.Bookmarks.Count To 1 Step -1
  Set item = wDoc.Bookmarks(a)
  itemName = item.Name
  aRng = item.Range
  fValue = RangeValue(itemName)
  If IsNumeric(fValue) Then
     Select Case formatCell
         Case "Percentage", "0%", "0.0%", "0.00%", "0.00""x"""
             aRng.Text = Format(fValue, formatCell)
         Case Else
             aRng.Text = Format(fValue, "#,##0;(#,##0);"" - """)
     End Select
  Else
     aRng.Text = fValue
  End If
  wDoc.Bookmarks.Add itemName, aRng 
Next a

[macropod]

Originally Posted by Guessed

The bookmark must be there otherwise it wouldn't be in the loop

The problem may be related to the Excel workbook. If the named ranges corresponding to the Word bookmarks are being deleted, that would cause such problems.

[newb]

thanks for the reply. I'm not sure the answer to your question, i actually inherited this code from someone who wrote it years ago so i am not familiar with all of it. the functionality is transferring information to the bookmarks in word which are named after named cells in excel.

I had a separate thread that asked about a separate issue, but the thread was closed. if anyone could help me with the formatting issue i am also having, i would appreciate it. thanks so much:

I am trying to import data from excel to word. the excel cells that i want transferred are named and i built out tables in word that have the corresponding "cells" bookmarked.

for the numeric data, i have an issue. some are percentages, some need two decimals, some i would prefer as whole numbers. the issue is that certain items that i would need 2 decimal places for come through as whole number. so 1.15 comes in as 1. 1.2 as 1. .65 as 1, etc.... i inherited the code for this and tried to edit it but had no luck. does anyone know how i can have the formatting change based on how it is displayed in excel? also, is there a certain way i should format in excel so that the data transfers as intended?

I have posted the entire code below:

Dim oExcel As Object
Dim eBook As Object

Dim formatCell As String
Sub ImportData()

    Dim ExcelFileName As String
    Dim Dated As Date
    
    ExcelFileName = SelectFile("Excel", "xlsm")
    If ExcelFileName = "" Then MsgBox "Excel file not selected - Import canceled.", vbInformation: Exit Sub
    
    Dim wDoc As Document
    
    Set wDoc = ActiveDocument
    Set oExcel = CreateObject("Excel.Application")
    Set eBook = oExcel.Workbooks.Open(ExcelFileName)
    
    'oExcel.Visible = True
    
    Application.StatusBar = "Please be patient as the data is imported..."
    Application.ScreenUpdating = False
    'On Error GoTo 10
    
    Dim tbl As Table
    Dim iSource As String
    Dim fValue As String
    
    Dim cl As Cell
    
    Dim item As Bookmark
    Dim itemName As String
    Dim a As Long
    
    For a = wDoc.Bookmarks.Count To 1 Step -1
            Set item = wDoc.Bookmarks(a)
                itemName = item.Name
            Set cl = item.Range.Cells(1)
                fValue = RangeValue(itemName)
                    If fValue <> "" Then
                        If IsNumeric(fValue) Then
                            Select Case formatCell
                                Case "Percentage"
                                    item.Range.Text = Format(fValue, formatCell)
                                Case "0%"
                                    item.Range.Text = Format(fValue, formatCell)
                                Case "0.0%"
                                    item.Range.Text = Format(fValue, formatCell)
                                Case "0.00%"
                                    item.Range.Text = Format(fValue, formatCell)
                                Case "0.00""x"""
                                    item.Range.Text = Format(fValue, "0.00""x""")
                                Case Else
                                    item.Range.Text = Format(fValue, "#,##0;(#,##0);"" - """)
                            End Select
                        Else
                            item.Range.Text = fValue
                        End If
                    End If
            wDoc.Bookmarks.Add itemName, cl.Range
    Next
    
    eBook.Close False
    oExcel.Quit
    Application.ScreenUpdating = True
    
    
    Set eSheet = Nothing
    Set eBook = Nothing
    Set oExcel = Nothing
    DoEvents
    Application.StatusBar = ""
    MsgBox "Import Completed.", vbInformation
    Exit Sub
10:
    eBook.Close False
    oExcel.Quit
    Application.ScreenUpdating = True
    Set eSheet = Nothing
    Set eBook = Nothing
    Set oExcel = Nothing
    MsgBox Err.Description
End Sub


Private Function SelectFile(FileTypeName As String, FileType As String) As String
    
    Dim FD As FileDialog
    
    SelectFile = ""
    
    Set FD = Application.FileDialog(msoFileDialogFilePicker)
    With FD
        .Title = "Select " & FileTypeName & " file"
        .AllowMultiSelect = False
        .Filters.Clear
        .Filters.Add FileTypeName & " File", "*." & FileType
    End With
    If FD.Show = -1 Then
        SelectFile = FD.SelectedItems(1)
    End If

End Function

Private Function RangeValue(RangeName As String) As String

    Dim i As Long
    Dim RangeToFind As Range
    Dim str As String

    Err.Clear
    On Error Resume Next
    With eBook
        For i = 1 To .Sheets.Count
            str = .Sheets(i).Range(RangeName).Value
            If Err.Number = 0 Then
                RangeValue = str
                formatCell = .Sheets(i).Range(RangeName).NumberFormat

                Exit For
            Else
                Err.Clear
            End If
        Next i
    End With
    On Error GoTo 0
    Err.Clear
End Function

[Guessed]

For the Range Value you would get the same number format as Excel simply by using
.Sheets(i).Range(RangeName).Text
instead of
.Sheets(i).Range(RangeName).Value
You could then do away with a bunch of the fiddly code trying to replicate that.

Your code to return the RangeValue has an error handler but it makes a fatal assumption that the named range is a single cell. So not only does the bookmark name in Word need to exist in the Excel workbook (that the user selected), but the capitalisation of the bookmark/name must match and it must be only a single cell. A clean way to deal with the capitalisation issue is shown here Excel Named Ranges - Does a Named Range Exist

To also handle the possibility of the Excel named range existing but being more than one cell, lets assume that you want the value in the first cell
.Sheets(i).Range(RangeName).Cells(1).Text

Post a sample document and workbook pair if you want the code fixed so that it works with your sample. It is much easier for us to work out the code flaws when we see what your ACTUAL input is.