Microsoft Office Forums - View Single Post

ptmuldoon · 02-16-2015, 10:05 AM

I'm going crazy over this Stuff !!

Ok. I have a macro that typically replaces the FilePath and FileName of each OLE Excel table and chart in my word file. Now, I fear I've uncovered an annoying bug when saving in the new .docx file format vs the older .doc file format. And hoping someone can confirm and possibly offer a solution.

In testing. I have a Word 2013 file saved as a .docx file with 2 links. One is linked to and using a Named Range, and the other is linked to cell range. When you first copy that to Word, the Link Field code look as follows:

Code:

{LINK Excel.SheetMacroEnabled.12 "C:\\Data\\Sample\\Sample.xlsm" "Cover Charts!Charts_Cover_Main" \a \p}

{LINK Excel.SheetMacroEnabled.12 "C:\\Data\\Sample\\Sample.xlsm" "Cover Charts!R28C10:R34C14" \a \p

Now after you save the file and then reopen it, that Link code is being slightly modified as follows.

Code:

{LINK Excel.SheetMacroEnabled.12 "C:\\Data\\Sample\\Sample.xlsm!Cover Charts!Charts_Cover_Main" "" \a \p}

{LINK Excel.SheetMacroEnabled.12 "C:\\Data\\Sample\\Sample.xlsm!Cover Charts!R28C10:R34C14" "" \a \p}

If you look carefully, you can see when Word first opens, the OLE code is being modified to include an exclamation point after the file name and before the cell/named range as well as pair of quotes at the end. But if you update links, it changes back to the way it should be.

The Problem.... When you first open the Word document, that modification of the Word file 'breaks' the ability to read the file name. when I open the word document, and run the following;

Code:

Sub SourceFileName()
Dim s As Variant

For Each s In ActiveDocument.InlineShapes
 MsgBox s.LinkFormat.SourceName
Next s
End Sub

Using the above, the SourceName returns inaccurately giving the following

For the NamedRange, it gives the filename plus the named range of

Code:

Sample.xlsm!Cover Charts!Charts_Cover_Main

and when using a cell reference, it returns only the cell reference and not even the file name of

Code:

R34C14

Can anyone confirm this behavior and have thoughts on returning just the file name when you first open a document and read the OLE link information?

Thanks
PT