How to create relative links in MS Word via MS Excel using Visual Basic
May 1, 2017
I recently had to do a bunch of work in Visual Basic for Applications and wire together a Word document with an Excel spreadsheet. This blog post documents all the things I wish I’d found via googling.
I assume you’re reading this because it came up via a search engine, so I put all the snippets first and my motivating story at the end.
All of the following snippets of code are executed within the context of a Word document. I lightly edited the code and changed some variable and function names, so I make no guarantees that the code will copy and paste in without errors.
How to open an Excel document with user input, from a Word document
First, make sure you add a reference to Microsoft Excel Objects via the Tools menu.
Dim xl As Excel.Application
Dim wkbk As Excel.Workbook
Dim ex_sheet As Excel.Worksheet
Dim t_sheet As Excel.Worksheet
Dim j_sheet As Excel.Worksheet
Set xl = Excel.Application
fileToOpen = xl.GetOpenFilename
If fileToOpen = "False" Then
MsgBox "Sorry, gotta pick a file!"
End
End If
Set wkbk = xl.Workbooks.Open(fileToOpen)
Set ex_sheet = wkbk.Sheets(1)
Set t_sheet = wkbk.Sheets(2)
Set j_sheet = wkbk.Sheets(3)
' do work here
wkbk.Close SaveChanges:=False
How to search through every part of a Word document, given a wildcard pattern
One of the workhorses in Microsoft Office is the concept of a ‘Range’, which represents a subset of the document you’re editing. You execute searches on a Range, which will then ‘select’ the sub-subset of the document you’re interested in. A Word document, as far as I can tell, consists of many ‘Stories’, which stand in for the different sections of the document.
In order to search through an entire document (i.e. main body, headers, footnotes, etc), we must iterate through the Range that represents each ‘Story’. Also, a Story may contain subranges. I found the original source of this code through VBA documentation.
' provide this function with a Word document, and an Excel Worksheet
' you can pass down to your 'DoTheWorkWeWant' sub function.
' in our case this will be the 'AddHyperlinkToARange' function from the next
' code block
Function SearchDocument(curdoc As Document, ex_sheet As Excel.Worksheet)
Dim count As Integer
Dim mypattern As String
Dim sRange As Range
count = 0
mypattern = "YOUR WILDCARD PATTERN HERE"
' by default, i.e. the Selection.Find range, Word won't
' search thru every single part of the document. So, we
' instead have to iterate over every 'story'/doc section
' and search those ranges individually.
For Each sRange In curdoc.StoryRanges
ExecuteSearch sRange, mypattern
While sRange.Find.Found
' having found a search hit, we dispatch
' this to a sub function; pick a better name
DoTheWorkWeWant sRange, count, ex_sheet
' necessary for moving the range forward
' or else when we reexecute the search
' it'll just start at the beginning again
' see superuser.com/questions/1009085
sRange.Collapse Direction:=wdCollapseEnd
count = count + 1
sRange.Find.Execute
Wend
' but, apparently, each StoryRange is also
' composed of many sub-ranges, so you have to
' also iterate over the StoryRange substories?
' This was copied from Msft documentation.
While Not (sRange.NextStoryRange Is Nothing)
Set sRange = sRange.NextStoryRange
ExecuteSearch sRange, mypattern
While sRange.Find.Found
DoTheWorkWeWant sRange, count, ex_sheet
sRange.Collapse Direction:=wdCollapseEnd
count = count + 1
sRange.Find.Execute
Wend
Wend
Next
End Function
Function ExecuteSearch(sRange As Range, mypattern As String)
With sRange.Find
.ClearFormatting
.Text = mypattern
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchWildcards = True
.Execute
End With
End Function
How to insert a relative hyperlink into a Word document
' DoTheWorkWeWant from the function provided above
' Assumes ActiveDocument is the word doc you want to edit,
' and that you're provided with an Excel file to edit
Function AddHyperlinkToARange(sr As Range, count As Integer, wsheet As Worksheet)
' sanity check to prevent infinite loops
' if you make a mistake in your code that
' iterates thru Word document searches,
' this can end up in an infinite loop
If count > 2000 Then
End
End If
' in the problem I had to solve,
' I had to extract two numbers from the
' provided substring
Dim info() As String
info = ExtractSearchInformation(sr.Text)
If info(0) <> "" Then
hyperlink = FetchHyperlinkFromExcel(wsheet, info(0), info(1))
If hyperlink <> "" Then
' note the "..\" string
' in our case, the files we were referencing were one file path down
ActiveDocument.Hyperlinks.Add Anchor:=sr, Address:="..\" & hyperlink
End If
End If
End Function
How to search through two Excel Worksheet columns at the same time
Function FetchHyperlinkFromExcel(wsheet As Excel.Worksheet, colAValue As String, colBValue As String)
Dim cRange As Excel.Range
Dim cFound As Excel.Range
' we set a range on the full A columns
' so we only search within there
Set cRange = wsheet.Columns("A:A")
Dim hyperlink As String
' xl's Range.Find works a bit differently.
' gotta be careful to specify LookAt:=xlWhole, or else it'll
' match substrings, which is NOT what we want
Set cFound = cRange.Find(colAValue, LookIn:=xlValues, LookAt:=xlWhole)
If Not cFound Is Nothing Then
' the only way to avoid infinite loops, apparently, is to keep
' track of the first found address and loop until we see it again
cFirstAddress = cFound.Address
' the format we're looking for depends on two columns (A,B)
' so once we find the first column, we refine our search
' by iterating over every found row and peeking inside the B col
' Debug.Print "addr: " & cFound.Row & "x" & cFound.Column
foundColB = wsheet.Cells(cFound.Row, 2).Value
' if the foundColB is empty AND our colBValue is empty, we
' found what we're looking for
If (IsEmpty(foundColB) And colBValue = "") Or foundColB = colBValue Then
' in this case, we were looking for the value of 4th column
hyperlink = wsheet.Cells(cFound.Row, 4).Value
Else
Do
Set cFound = cRange.FindNext(cFound)
foundColB = wsheet.Cells(cFound.Row, 2).Value
If foundColB = colBValue Then
hyperlink = wsheet.Cells(cFound.Row, 4).Value
End If
Loop Until (foundColB = colBValue) Or (cFound.Address = cFirstAddress)
End If
End If
FetchHyperlinkFromExcel = hyperlink
End Function
How to create a new Word file and write any given text to it
' note that this will shift your ActiveDocument Set logger = Word.Application.Documents.Add logger.Content.InsertAfter Text:="This is the dumbest, easiest way" & vbNewLine
Why did you do this?
Recently, as a favour, I was asked to step in and perform some Microsoft Office automation.
A legal firm was producing a lengthy court submission with thousands of citations. These citations all referenced existing files. This submission would be delivered on a USB key along side the files it cited. The trick was, they wanted their submitted document to link directly to these files, as they came up in footnotes. Like any website, except only referencing a local filesystem.
They were extremely short for time, the intern that normally handled this was away on exams, and every other computer person they knew turned them down. All I had to do was parse a spreadsheet, search through a Word file and insert a relative link in the right place.
I said sure, how hard could it be? and promptly wildly underestimated how long this task would take me.
I’ve spent enough time in my career parsing OpenOffice documents to know that, while I could probably crack open MS Word files using some half-baked library, down that path also lies madness.
I rolled up my sleeves and dug into Visual Basic for Applications.1 This proved to be challenging,2 mostly because of the runtime: it obviously hasn’t been updated in ten or fifteen years, and the version I was dealing with couldn’t be bothered to give me line numbers when errors occurred. I lost hours and hours fighting cryptic syntax and runtime errors and runtime errors reported as syntax errors.3
-
As far as I can tell this is still the Best Way to automate things in MS Office. I probably could’ve done this in .NET, but that would involve installing .NET runtimes on aggressively locked down Windows laptops, and that felt like a non-starter. ↩
-
A few years ago, in a post about Brendan Eich, I’d joked about Microsoft VisualBasicScript winning the browser scripting wars; this experience gave me a new appreciation for how reasonable Javascript is in comparison. ↩
-
VBA at a glance seems loosely typed, but is in fact strongly, statically typed. It’s not smart enough to infer types returned from methods. It distinguishes between primitives and objects with different syntax. It ships with poor support for even basic data structures. Etc, etc. ↩