Question Generic Way to Manipulate Any Office Doc?

[SFLeatherBear]

I am brand new to VB.Net and need some assistance working with the Office Interop functionality.

Scenario:

I have a folder hierarchy containing miscellaneous Office documents. These documents contain hyperlinks pointing to external documents, images, etc. which reside on a networked file server ServerA. Those external documents have been moved from ServerA to ServerB. I need to open every document (regardless of whether it is Word, Excel, PowerPoint, etc.) and do a search/replace of hyperlinks to update the links to point to the new ServerB.

(i.e. All links in my documents to http://ServerA/Quality/Defect/Ticket123.doc need to be replaced with http://ServerB/Quality/Defect/Ticket123.doc)


My code:

I have written some code that works for Word documents.

Private Sub updateLinks(ByVal file As String)
Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim oLinks As Word.Hyperlinks
Dim oLink As Word.Hyperlink
Dim oldBasepath As String
Dim newBasepath As String

'Set the old and new basepath
oldBasepath = "https://ServerA"
newBasepath = "https://ServerB"

'Start Word and open the document.
oWord = New Word.Application
oWord.Visible = False
oDoc = oWord.Documents.Open(file)

'Get all hyperlinks
oLinks = oDoc.Hyperlinks

For Each oLink In oLinks
Dim oldDisplayText As String
Dim oldAddress As String
Dim newDisplayText As String
Dim newAddress As String

' Get the original display text and link address
oldDisplayText = oLink.TextToDisplay
oldAddress = oLink.Address

'Change the link address if it contains the old basepath
If oldAddress.Contains(oldBasepath) Then
newAddress = oldAddress.Replace(oldBasepath, newBasepath)
Else
newAddress = oldAddress
End If

'Change the link display text if it contains the old basepath
If oldDisplayText.Contains(oldBasepath) Then
newDisplayText = oldDisplayText.Replace(oldBasepath, newBasepath)
Else
newDisplayText = oldDisplayText
End If

oLink.Address = newAddress
oLink.TextToDisplay = newDisplayText

Next

oDoc.Save()
oDoc.Close()

oWord = Nothing

End Sub


Question:

Is there a way to re-write this so it can handle any type of Office document (Word, Excel, PowerPoint) using some abstract Application and Document object without instantiating the type-safe Word.Application, Word.Document, etc? Or do I need to write a separate function for each type of document I encounter?

Thanks for any guidance.

-Michael

 

[JohnH]

You can turn Option Strict off and use late-binding:

Sub ReplaceLinks(ByVal doc As Object)
    For Each link As Object In doc.Hyperlinks
        link.Address = link.Address.Replace("serverA", "serverB")
        link.TextToDisplay = link.TextToDisplay.Replace("serverA", "serverB")
    Next
End Sub

Use the application specific means to open the document and pass the instance to this function, that would be a Excel Worksheet, a Word Document and a PowerPoint Slide. It will work with all these as they have same object model for this. With late binding every object is treated as Object when you write code, and the member is called by name at runtime, intellisense won't help you write this code so you have to verify it with early bound typed code first. Notice the code sample has no reference to any type other than Object.