VBA list of filepaths of linked objects in document

Question

I have a number of large Microsoft Word documents with many linked files from many Microsoft Excel spreadsheets. When opening a Word document, even with the 'update linked files at open' option unchecked:

Word still checks each link at its source by opening and closing the relevant excel spreadsheet for each individual link (so for x number of links, even if from the same spreadsheet, Word will open and close the spreadsheet x times). This means opening documents takes a very long time.

I have found that documents open faster if the spreadsheets containing the source of linked objects are already open, so Word doesn't keep opening, closing, reopening them.

So far, the beginnings of a solution I have is to create a list of all the filepaths of the linked objects, done by following VBA code:

Sub TypeArray()

Dim List(), Path As String
Dim i, x As Integer
Dim s As InlineShape
Dim fso As FileSystemObject, ts As TextStream

Set fso = New FileSystemObject
Set ts = fso.OpenTextFile("C:\MyFolder\List.txt", 8, True)

With ts
    .WriteLine (ActiveDocument.InlineShapes.Count)
End With

For Each s In ActiveDocument.InlineShapes
        Path = s.LinkFormat.SourcePath & "\" _
        & s.LinkFormat.SourceName
        With ts
            .WriteLine (Path)
        End With
Next s
End Sub

'--------------------------------------------------------------------------------------
Private Sub WriteStringToFile(pFileName As String, pString As String)

    Dim intFileNum As Integer

    intFileNum = FreeFile

    Open pFileName For Append As intFileNum
    Print #intFileNum, pString
    Close intFileNum

End Sub
'--------------------------------------------------------------------------------------
Private Sub SendFileToNotePad(pFileName As String)

    Dim lngReturn As Long

    lngReturn = Shell("NOTEPAD.EXE " & pFileName, vbNormalFocus)

End Sub

which works well, but can only be used after a document is already open, which defeats its purpose.

So, finally, my question(s) are these:

1) Is there a way to run this code (or any better, more efficient code - suggestions are welcome) before opening a Word document and waiting through the long process of checking each link at its source?

2) Is there a way to avoid all this and simply have Word not check the links when it I open a document?

Sorry for the long question, and thank you for the help!

Answer 1

I also found that activedocument.fields can contain links to external objects (in my case, an Excel sheet).

Use this code to parse them:

for each f in activedocument.fields
  debug.print f.code
next

And use activedocument.fields(FIELDNUMBER) to select each object, to figure out where it is in the document.

Maybe also activedocument.Variables and activedocument.Hyperlinks can contain links to external objects? (not in my case).

Answer 2

I just found out that you can set/modify a DelayOleSrvParseDisplayName registry entry and a NoActivateOleLinkObjAtOpen registry entry to modify the global behaviour:

See http://support.microsoft.com/kb/970154

Answer 3

If you are still looking for something on this front, I created the following in a combination of VBA and VB.NET (in VS 2010) to show what can be done quite easily using that system. If VB.NET is no use to you, sorry, but there are reasons why I don't really want to spend time on the pure VBA approach.

At present, it is a "console" application which means you'll probably see a box flash up when it runs, but also means that you are more likely to be able to create this app without VS if you absolutely had to (AFAICR the VB.NET /compiler/ is actually free). It just fetches the link info. (i.e. there's currently no facility to modify links).

The overview is that you have a small piece of VBA (say, in your Normal template) and you need an open document. The VBA starts a Windows Shell, runs the VB.NET program and passes it the full path name of the document you want to open.

The VB.NET program opens the .docx (or whatever) and looks at all the Relationships of type "oleObject" that are referenced from the Main document part (so right now, the code ignores headers, footers, footnotes, endnotes and anywhere else you might have a link)

The VB.NET program automates Word (which we know is running) and writes each link URL into a sequence of Document Variables in the active document. These variables are called "Link1", "Link2", etc. If there are no links (I haven't actually tested that path properly) or the program can't find the file, "Link0" should be set to "0". Otherwise it should be set to the link count.

The shell executes synchronously, so your VBA resumes when it's done. Then you either have 0 links, or a set of links that you can process.

The VBA is like this:

Sub getLinkInfo()
' the full path name of the program, quoted if there are any spaces in it
' You would need to modify this
Const theProgram As String = """C:\VBNET\getmaindocumentolelinks.exe"""
' You will need a VBA reference to the "Windows Script Host Object Model"
Dim oShell As WshShell

Set oShell = CreateObject("WScript.Shell")
' plug your document name in here (again, notice the double quotes)
If oShell.Run(theProgram & " ""c:\a\testdocexplorer.docx""", , True) = 0 Then
  With ActiveDocument.Variables
    For i = 1 To CInt(.Item("Link0").Value)
      Debug.Print .Item("Link" & CStr(i))
    Next
  End With
Else
  MsgBox "Attempt to retrieve links failed"
End If
End Sub

For the VB.NET, you would need the Office Open XML SDK (I think it's version 2.5). You need to make references to that, and Microsoft.Office.Interop.Word.

The code is as follows:

Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.IO
Imports System.Xml
Imports System.Xml.Linq
Imports DocumentFormat.OpenXml.Packaging
Imports Word = Microsoft.Office.Interop.Word


Module Module1
  Const OLEOBJECT As String = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/oleObject"

  Sub Main()
    Dim s() As String = System.Environment.GetCommandLineArgs()
    If UBound(s) > 0 Then
      Dim wordApp As Word.Application
      Try
        wordApp = GetObject(, "Word.Application")
        Dim targetDoc As Word.Document = wordApp.ActiveDocument
        Try
          Dim OOXMLDoc As WordprocessingDocument = WordprocessingDocument.Open(path:=s(1), isEditable:=False)
          Dim linkUris As IEnumerable(Of System.Uri) = From rel In OOXMLDoc.MainDocumentPart.ExternalRelationships _
                                                       Where rel.RelationshipType = OLEOBJECT _
                                                       Select rel.Uri
          For link As Integer = 0 To linkUris.Count - 1
            targetDoc.Variables("Link" & CStr(link + 1)).Value = linkUris(link).ToString
          Next
          targetDoc.Variables("Link0").Value = CStr(linkUris.Count)
          OOXMLDoc.Close()
        Catch ex As Exception
          targetDoc.Variables("Link0").Value = "0"
        End Try
      Finally
        wordApp = Nothing
      End Try
    End If
  End Sub

End Module

I originally wrote the .NET code as a COM object, which would be slightly easier to use from VBA, but significantly harder to set up on the .NET side and (frankly) much harder to modify & debug as you have constantly to close Word to release the references to the COM DLLs.

If you actually wanted to fix up the LINK paths, as far as I can tell, modifying them in the relationship records is enough to get Word to update the relevant LINK fields when it opens Word, which saves having to modify the XML code for the LINK fields as well. But that's another story...

Answer 4

If I am not wrong there should be Document_Open event according to msdn. This should actually be a before open document and should be fired before updating links (at least it in excel it is fired before calculation).

Try opening the files on document open. Then you will face another problem, and so when to close the files, but that is a much easier thing to do. (probably document_close event...)

EDITTED:

As comments state, this is too late. You can create a word opener (as a single app or as an addin). The logic basically is:

'1) on something_open run GetOpenFileName dialog
'2) before opening the real thing, open all files accompanied
'3) open the document itself
'4) close all files
'5) close the opener itself

This is not the most trivial way, but I use this logic for exampe to make sure, that my applications always runs in a fresh copy of excel etc. But I understand that this is a workaround rather then a solution.