Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-02-2021, 09:36 AM
newb newb is offline PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist Windows 10 PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist Office 2019
Novice
PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist
 
Join Date: Jun 2021
Posts: 3
newb is on a distinguished road
Default PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist

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)"

Code:
    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

Last edited by macropod; 06-02-2021 at 02:59 PM. Reason: Added code tags
Reply With Quote
  #2  
Old 06-02-2021, 04:41 PM
Guessed's Avatar
Guessed Guessed is offline PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist Windows 10 PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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.
Code:
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
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 06-02-2021, 09:38 PM
macropod's Avatar
macropod macropod is offline PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist Windows 10 PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by Guessed View Post
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 06-03-2021, 09:02 AM
newb newb is offline PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist Windows 10 PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist Office 2019
Novice
PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist
 
Join Date: Jun 2021
Posts: 3
newb is on a distinguished road
Default

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:

Code:
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

Last edited by macropod; 06-03-2021 at 03:02 PM. Reason: Added code tags
Reply With Quote
  #5  
Old 06-03-2021, 05:11 PM
Guessed's Avatar
Guessed Guessed is offline PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist Windows 10 PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply

Tags
runtime error 5941

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Word template with Macro keeps getting an error ''Run-time error 5941'' Marcel Word VBA 3 12-17-2019 04:55 PM
PLEASE HELP: Run-time error '5941', The requested member of the collection does not exist Word 2010 the requested member of the collection does not exist flyboy3300 Word VBA 15 08-02-2016 01:40 AM
Presentation (unknown member) : Object does not exist. Benazeer PowerPoint 0 03-08-2016 06:28 AM
Run-time error 5941 when deleting a row from a table jpb103 Word VBA 1 05-26-2014 07:08 AM
5941 requested member of collection does not exist Prevents Userform from Showing marksm33 Word VBA 1 02-22-2014 08:56 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:34 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft