first question, excuse me if has been solved, i've searched thoroughly , cannot find answer:
i have linked several named ranges word document. word doc (and related excel workbook named ranges) template: it's coworker make many copies of these templates (of both word doc , excel workbook).
i include command button in word doc that, when clicked, update sources linked named ranges. specifically, want set workbook same name worddoc, source.
the issue not named range have entered. the:
run-time error '6083': objects in document contain links files cannot found. linked information not updated.`
however, have quadrupled-checked excel doc, named range exists. and, when hit alt+f9 in word, see link contains named range!
{link excel.sheet.8 c:\users\marc\documents\wip_sss.xlsm ced \a \p}
here code:
public sub changesource() dim filename variant dim fieldcount integer dim x integer filename = left(application.activedocument.name, len(application.activedocument.name) - 4) & "xlsm" fieldcount = activedocument.fields.count x = 1 fieldcount 'debug.print activedocument.fields(x).type if activedocument.fields(x).type = 56 activedocument.fields(x).linkformat.sourcefullname = activedocument.path & "\" & _ filename & "!ced" end if next x end sub if don't enter named range @ all, macro works, embeds entire excel worksheet (which not want do). ideas on how/ why not liking named range?
thanks, marc
update: bibadia, found solution; in addition, want document strange behavior exhibited word vba:
first off, solution code:
public sub changesource() dim filename variant dim fieldcount integer dim x integer filename = thisdocument.path & "\" & left(application.activedocument.name, len(application.activedocument.name) - 4) & "xlsm" fieldcount = activedocument.fields.count x = 1 fieldcount on error resume next if activedocument.fields(x).type = 56 activedocument.fields(x).delete end if next x activedocument.bookmarks("r1").range.inlineshapes.addoleobject filename:=filename & "!range1", linktofile:=true end sub i first deleted type 56 fields (linked object, or more technically, "wdfieldlinked"). then, added oleobjects @ pre-set bookmark locations. interestingly, bibadia noted, key input linktofile:=true code. seems word not accept object if embedded: if remove line, error word cannot obtain data c:\...\document!namedrange link.
finally, found 1 other odd behavior: when trying replace link, using code,
activedocument.fields(1).linkformat.sourcefullname = filepath+name & _ "!ced" 'that named range it work once, when changed both word document's , excel workbook's filenames (see original message context). so, when new filepath+name did not match existing filepath+name, word vba accepted change. however, once updated, if tried run macro again, get:
run-time error '6083': objects in document contain links files cannot found. linked information not updated.
i error if changed named range named range in same worksheet (and same workbook). appears word vba not "updating" filepath+name when filepath+name not change.
just didn't know (like me) knows. sorry long update, wanted thorough.
i not sure of this, little long comment.
as far know, can set linkformat.fullsourcename name of file, not fullname + subset name, trying when appending "!ced". although can read subset name (ced) oleformat.label, can't modify it's read-only property.
so if need modify subset name (ced), afaics way delete , reinsert link field. if reinsert using fields.add, specify text of field, can file name , subset name right. confusing if insert link using inlineshapes.addoleobject, can specify fullname+subset name in way trying in code.
however, not think are trying modify subset name. let's assume have link field along lines of
{ link excel.sheetmacroenabled.12 "the full pathname of .xlsm file" ced \a f 0 \p } word able update link if path+filename valid (i.e. there's .xlsm @ location, workbook has range name called ced, , range name in first sheet. otherwise, have specify sheet name well, e.g.
{ link excel.sheetmacroenabled.12 "the full pathname of .xlsm file" sheet2!ced \a f 0 \p } it's guess, if code trying connect workbook range defined ced not in first sheet, see error describe.
further, scope of ced range name has either "workbook" or name of first sheet. otherwise, if scope first sheet range in sheet, or vice versa, not think word can make connection whatever subset name provide (my guess word never caught excel after excel introduced multi-sheet workbooks).
if ced can reference sheets other first one, think have use excel object model discover sheet range in, construct appropriate subset name, , delete/re-insert link field.
Comments
Post a Comment