Avatar of CiceroBC

asked on 

Transform Document Library into Intranet Knowledge Base

The company I work for has a large knowledge base of work instructions, manuals, specifications, and general information all stored in a customized Document Library that uses meta tags to organize word (well, mostly word, but there are some excel files and pdf entries too) files into libraries and assign them a pre set document control number that always begins with a two letter prefix, followed by a dash and then 6 other numbers, followed by a second dash and then two more digits.  (IE: XX-000000-00
This system works really well if the document you need has all of the relevant information self contained within the file. However, we are discouraged from repeating information twice in the efforts of normalization and removing the chance of conflicting information in multiple documents.  Because of this, we are forced to make references between files, which the system does not support hyperlinking from within documents.
To better serve our internal customers that are growing frustrated with the system (and therefore relying on intuition and old documents to perform detailed work instructions) I am pursuing an intranet site where everything is interlinked within a browser interface.
I would like to automate a system (via macro or any other solution that works) that:
-Parses through a document and finds references to another document
-Hyperlinks that reference to the other document based on a URL prefix and the reference ID of the document
-Saves the document in an HTML format using the reference ID as the title and file name
Simple enough?  If so, please help me.  The next step is to automate this to run through multiple documents.
The final piece of relevant information is that this MUST be a fully automated process, as we currently have about 5,000 documents within the company that change at a rate of about 30 to 40 a week.
Project ManagementMicrosoft DevelopmentMicrosoft Word

Avatar of undefined
Last Comment
Avatar of irudyk
Flag of Canada image

Give the following code a try (covers the 3 criteria you set out above)
Sub ConvertFileToHTML()
Dim strURLPrefix As String
'change this to the correct http address 
strURLPrefix = "http://webserver/intranet/docs/"
Dim strURLInternalPath As String
'changes this to the internal UNC path of the URL
strURLInternalPath = "\\webserver\webserverdrive\webroot\intranet\docs\"
With Selection
    .HomeKey wdStory
    'find all items that are structured as 2 letters-6 numbers-2 numbers
    .Find.Execute FindText:="^$^$-^#^#^#^#^#^#-^#^#", MatchCase:=False, MatchWholeWord:=True, MatchWildcards:=False, _
    MatchSoundsLike:=False, MatchAllWordForms:=False, Forward:=True, Wrap:=wdFindContinue, Format:=False
    While .Find.Execute
        'if the results are already a hyperlink then remove the hyperlink
        If .Range.Hyperlinks.Count = 1 Then .Range.Hyperlinks(1).Delete
        'add the applicable hyperlink
        ActiveDocument.Hyperlinks.Add Anchor:=.Range, Address:=strURLPrefix & .Range.Text & ".html", TextToDisplay:=.Range.Text
End With
With ActiveDocument
    'set the title of the document
    .BuiltInDocumentProperties.Item("Title").Value = Left(ActiveDocument.Name, 12)
    'save the file as an HMTL document
    .SaveAs FileName:=strURLInternalPath & Left(.Name, 12) & ".html", FileFormat:=wdFormatFilteredHTML
    'close out the document without saving changes
    .Close wdDoNotSaveChanges
End With
End Sub

Open in new window

Avatar of CiceroBC


WOW!  That is perfect irudyk!

If I may be so bold as to place one more requirement on the list, can this be expanded to go through multiple documents within a directory?  Then I could download all the new documents to a specific folder and run the macro once.

If so, fantastic!  If not, just let me know and I'll happily award the points for the VBA code.

Thanks for a quick and precise solution!
Avatar of irudyk
Flag of Canada image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of CiceroBC


Brilliant.  (I think I even wept a little.)  

This fixes a huge gap in our distribution process.  Thank you very much!
Avatar of CiceroBC


This was by far the most straightforward and concise solution I have seen in a while on this site.  It is coded professionally and properly commented to make adjustments easy to implement.  Commendable effort!
Microsoft Development
Microsoft Development

Most development for the Microsoft platform is done utilizing the technologies supported by the.NET framework. Other development is done using Visual Basic for Applications (VBA) for programs like Access, Excel, Word and Outlook, with PowerShell for scripting, or with SQL for large databases.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo