|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
||||
|
||||
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 |
#3
|
||||
|
||||
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] |
#4
|
|||
|
|||
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 |
#5
|
||||
|
||||
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 |
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 |
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 |