MS Word tries to connect to server hosting templates

We had set our computers running MS Office 2003 to use group templates from a corporate server so that every one uses the same templates with our company logo, font styles, etc (Tools -> Options -> File locations -> Group templates). Some years ago the templates were located on a shared resource (everyone readonly access) in one of our servers (let's call it \\OLDSERVER\OfficeTemplates$\Word). Years have passed by and with Windows 2003 R2's new features, we have moved on to use replication, DFS and domain-based namespaces to unify all the shares into a single entry point for everyone. The new group templates location was set to H:\Common files\Office templates\Word. Everything seemed to be perfectly set up and working. Eventually, the server that used to host the templates was removed and the problem showed up: Every time you try to open a .doc file that was created using the original path template (based on a shared resource) MS Word took from 2 to 3 minutes to show up the file. It finally opens, but most users think there is a problem with the servers, the file, MS Word or their computers and rush to call IT support. We started to investigate the issue and found that Adam Leinss was experiencing the same problem. From Adam Leinss’ Tech Tips: Remove Novell and Microsoft Word Goes Ape:

Our company is moving from Novell to Microsoft for our file and directory services. We removed the Novell client from everyone’s workstation and that seemed to work just fine. Then we removed everyone’s rights to said Novell server and everything is still fine. Then shut down said Novell server and bam: opening some Word documents takes 2 to 3 minutes!

We tried to use the macro/script he suggests. You need to download dsofile.dll - Microsoft Developer Support OLE File Property Reader 2.0 Sample (KB 224351) in order to make it run and still make some modifications to the code since there seem to be two different dsofile.dll versions and minor changes are needed in order to run it using the new dsofile.dll version. Extracted from March 2005 Tales from the Script (Historias de Secuencias de Comandos):

Old dsofile.dll version:

Set objPropertyReader = CreateObject("DSOleFile.PropertyReader")
Set objDocument = objPropertyReader.GetDocumentProperties("C:\Scripts\New_users.xls")
Wscript.Echo "Author: " & objDocument.Author

Code modified to make it run with the new version:

Set objFile = CreateObject("DSOFile.OleDocumentProperties")
objFile.Open("C:\Scripts\New_users.xls")
Wscript.Echo "Author: " & objFile.SummaryProperties.Author

Summing up, here is the modified version of the script that we used:

Sub TemplateBatchChangeModified()
    Dim objPropertyReader
    Dim strFolder As String
    Dim strFileName As String
    Dim objThisDoc As Word.Document
    Dim strFindTemplate As String
    Dim strReplaceTemplate As String
    Dim strAffectedDocs As String

    On Error Resume Next

    'Create the PropertyReader object

    Set objPropertyReader = CreateObject("DSOFile.OleDocumentProperties")
    If Err.Number <> 0 Then
        MsgBox "You must install the DSOleFile component. See " & _
            "http://support.microsoft.com/support/kb/articles/Q224/3/51.ASP"
        GoTo FinishUp
    End If

    'Get the template names
    strFindTemplate = UCase(InputBox("Name of template to find (exclude the .dot)") & ".dot")

    strReplaceTemplate = InputBox("Name of replacement template (exclude the .dot)") & ".dot"

    'Make sure it's a valid template. Try to create a new document based on it.
    Set objThisDoc = Word.Documents.Add(strReplaceTemplate, Visible:=False)
    If Err.Number <> 0 Then
        'No such template
        MsgBox "There is no accessible template named " & strReplaceTemplate
        GoTo FinishUp
    End If
    'Close the test document
    objThisDoc.Close wdDoNotSaveChanges

    On Error GoTo ErrorHandler
    'Get the current documents path
    strFolder = "drive:\directory from with you want to replace templates\"

    'Examine all Word documents in the directory

    'Get the first document name
    strFileName = Dir(strFolder & "*.doc")

    While strFileName <> ""
        'Look at the template name
        objPropertyReader.Open (strFolder & strFileName)
        If UCase(objPropertyReader.SummaryProperties.Template) = strFindTemplate Then
            objPropertyReader.Close

            'It matches. Open the document
            Set objThisDoc = Word.Documents.Open(FileName:=strFolder & strFileName, _
                                                 Visible:=False)

            'Change the attached template
            objThisDoc.AttachedTemplate = strReplaceTemplate

            'Save the change
            objThisDoc.Close wdSaveChanges

            'Note the document
            strAffectedDocs = strAffectedDocs & strFileName & ", "
        End If
        'Get the next document
        strFileName = Dir
    Wend

    'Report the results
    If strAffectedDocs = "" Then
        MsgBox "No documents were changed.", , "Template Batch Change"
    Else
        'Remove the trailing comma and space
        strAffectedDocs = Left(strAffectedDocs, Len(strAffectedDocs) - 2)

        MsgBox "These documents were changed: " & strAffectedDocs, , "Template Batch Change"
    End If
    GoTo FinishUp

ErrorHandler:
    Set objThisDoc = Nothing
    Set objPropertyReader = Nothing
    Err.Raise vbError + 1001, "TemplateBatchChange", "TemplateBatchChange encountered an error: " & Err.Description

FinishUp:
    'Release object references
    Set objThisDoc = Nothing
    Set objPropertyReader = Nothing
End Sub

There is however a problem with this script, or with MS Word API to be more precise: In our case the old and the new templates are called the same. The only change is WHERE do they are located (the unexisting shared resource and the new location). Having that in mind, if you run: /

objThisDoc.AttachedTemplate = strReplaceTemplate

no change is actually done, and the file is not modified at all (despite that the scripts tells you that it has been changed). The only way to effectively change the files showing this delay when being opened is to change the name of the template. For instance we had a template called Corporate+TÜV Envelope English.dot, we would need to create a copy of it with the name Corporate+TÜV Envelope EN.dot for instance and then run the script telling to change Corporate+TÜV Envelope English.dot into Corporate+TÜV Envelope EN.dot FOR EVERY .DOC FILE created during the last 3 years that resides on the server, and this script repeated FOR EVERY template we use. If you have set your group templates on a shared resource I advise you not doing it any more. You will run into problems whenever you need to replace your server. You can check how the full path to the template is saved with your documents saving them as a .xml file and the opening it with notepad. You will find the following key:

<w:attachedTemplate w:val="\\OLDSERVER\OfficeTemplates$\Word\Corporate+TÜV Envelope English.dot"/>

We have finally decided not to change any single file of our servers. Instead of doing such a lot of changes, we just have created an A record in our DNS Servers to point OLDSERVER to the IP address of any of the live servers. Having done that MS Words 2003 opens the files that poing to \\OLDSERVER\OfficeTemplates$\Word without the delay of 3 minutes. There is no need to create the share on the server pointed by OLDSERVER, it only needs to be there, alive and 'pingable'. It is not a solution, just a workaround. The real solution is MS Word being updated (maybe in the next SP or in Office 2007) so that it does not look for the path of the original template anymore.

Reporting Services & MS Word format (.doc) rendering.

Problem description: Feature request One of the most frequently asked questions in Reporting Services (RS) forums and newsgroups is: Why did not RS include the option to render documents into MS Word (.doc) format? According to BOL, Reporting Services 2005 has the ability to render/output documents into:

• CSV
• Acrobat (PDF) file
• Excel
• Web archive
• TIFF File
• XML

Why did I marked XML? Because RS is not only able to output the results to raw XML format but also can make transformations based on XSLT stylesheets! That means that you can render into ANY format that you want, provided you feed Reporting Services with the proper transformation file. This document is based (and extends) the idea from CarlosHM and his blog document: WordML in Reporting Services.

The solution In order to illustrate the process, we will render a report into MS Word format.

1. Create your template using Microsoft Word 2003. You can make it as complex as you need/want. When you reach the point where data fields are expected to be filled, just write the name of the fields surrounded by special characters (to be able to find them afterwards). For instance, if there will be a field called company that should be on bold, arial 16, left justified, etc... just write «Company» and format that piece of text as needed. Add every fixed text as usual (I suppose you know how to work with Word). If you want a new page for every record returned by Reporting Services, remember to add a hard break (CTRL+Enter) at the end of the format.

2. Save the document When you have your layout ready, Save document as... XML document. and name it TemplateDocument.xml. Just for your information, you can see that, despite the fact that its extension is .xml, its icon is not the standard for an XML file and it is still linked to Word. That is because in the inside of it there is a line:

<?mso-application progid="Word.Document"?>

3. Reformat the generated .xml file. MS Word generates XML files without indenting (smaller file size), thus making them hardly human readable. Since we need to manually modify this file, we will reformat it and indent it to avoid making mistakes. For this you might need tidy, a little command line program to reformat html/xml files or use your favourite xml editor.

tidy -utf8 -xml -i -wrap 999 -output TidyOutput1.xml TemplateDocument.xml

Note that if your input document has embedded images, using -i option will break them. There is no problem with this since they can be repaired later, calling tidy again without passing -i parameter.

4. Modify the formatted .xml in order to achieve .xsl Now we have a human readable .xml file. Let's use it in order to create the .xsl file we need to upload to RS. First, make a copy of it, renamed it to .xsl and open it with Notepad:

copy TidyOutput1.xml TidyOutput1.xsl
notepad TidyOutput1.xsl

We will need to do some search and replaces of some blocks of text. Search for the line that starts with <o:DocumentProperties> and remove everything from the top of the file up to that particular line (not included). In place of the removed text, insert this one:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:w10="urn:schemas-microsoft-com:office:word"
xmlns:sl="http://schemas.microsoft.com/schemaLibrary/2003/core"
xmlns:aml="http://schemas.microsoft.com/aml/2001/core"
xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:wsp="http://schemas.microsoft.com/office/word/2003/wordml/sp2"
xmlns:st1="urn:schemas-microsoft-com:office:smarttags">
  <xsl:output method="xml" media-type="text/xml" version="1.0"
  encoding="UTF-8" standalone="yes" indent="yes"
  omit-xml-declaration="no" />
  <xsl:template match="/">
    <xsl:processing-instruction name="mso-application">
    progid="Word.Document"</xsl:processing-instruction>
    <w:wordDocument xmlns:w="http://schemas.microsoft.com/office/word/2003/wordml"
xmlns:v="urn:schemas-microsoft-com:vml"
xmlns:w10="urn:schemas-microsoft-com:office:word"
xmlns:sl="http://schemas.microsoft.com/schemaLibrary/2003/core"
xmlns:aml="http://schemas.microsoft.com/aml/2001/core"
xmlns:wx="http://schemas.microsoft.com/office/word/2003/auxHint"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
xmlns:wsp="http://schemas.microsoft.com/office/word/2003/wordml/sp2"
xmlns:st1="urn:schemas-microsoft-com:office:smarttags"
w:macrosPresent="no" w:embeddedObjPresent="no" w:ocxPresent="no"
xml:space="preserve">
<xsl:apply-templates />
</w:wordDocument>
  </xsl:template>
  <xsl:template match="Report">
    <o:SmartTagType o:namespaceuri="urn:schemas-microsoft-com:office:smarttags"
    o:name="PersonName" />

Now go to the end of the file. You will find that the last tag is:

</w:wordDocument>

Delete it and insert these two lines:

  </xsl:template>
</xsl:stylesheet>

Now save your document. Let's check if it is well-formed yet:

tidy -utf8 -xml -i -wrap 999 -output TidyOutput2.xsl TidyOutput1.xsl

If you see No warnings or errors were found in the output, you can continue (using TidyOutput2.xsl). If any error appears, revise the previous steps to see where the error was. You need to have a valid (well-formed) XSL file in order to continue.

5. Modify .xsl file to include the database fields. In step 1, when we created the template using MS Word, we named the fields as «FieldName». Now we will use that names to search and replace every field that you entered with the correct xsl command to replace it with the value of the Reporting Services returned xml file. Open TidyOutput2.xsl with Notepad and do a search and replace:

Search: «FieldName»
Replace: <xsl:value-of select="@FieldName" />

Repeat the search & replace for every field. When you think you had finished, search for « to check that you did not leave any field behind. Save as TidyOutput3.xsl. As I told you before, if your original template had embedded images, reformatting using tidy with -i option broke them. Now we will run:

tidy -utf8 -xml -output TidyOutput4.xsl TidyOutput3.xsl

Without the -i option everything will be reformatted to start at column 0 and the images ( <w:binData> tags) will be repaired.

6. Upload .xsl file to RS. In order to do our test we will create a new simple report, using MS Visual Studio, with the underlying query:

SELECT 'Company 1' As Company, 'Address 1' As Address
UNION
SELECT 'Company 2' As Company, 'Address 2' As Address

This will return us 2 records, without involving any database. Of course, you will need to create your own report with your own underlying query so that you can retrieve all the information you need. Then design the report as simple as you can, since it will only be used to retrieve the data. The formatting will be applied using TidyOutput4.xsl we had prepared before. Both the generated report (Report1.rdl) and TidyOutput4.xsl must be deployed to Reporting Services.

7. Test it If you have reached this point, now it's time to check if RS can send us a Word document. Type this on your explorer navigation bar:

http://reportserver.yourdomain.com/ReportServer?/directory/Report1
 &rs:Format=XML
 &rs:Command=Render
 &rc:XSLT=TidyOutput4.xsl
 &rc:FileExtension=xml

If everything has gone nicely, you should be asked for opening a file called Report1.xml. Click open and see the results. MS Word should open and you should see something like your template, but without the data. The reason for this is that we forgot to add...

8. <xsl:for-each> Most of the reports have a master/details structure. You can think about an invoice, with master part with customer, invoice number, address etc, and a details part with a list of items. In our Report1.rdl we used a table (named table1) and we need to use <xsl:for-each> in order to iterate for every record in it. Open TidyOutput3.xsl (indented) and save it as TidyOutput5.xsl. Now search for the occurrence of your first field (@Company). Now move the cursor some lines up, until you reach the opening <w:p ...> (word paragraph) in which your field is placed. Right between the previous closing paragraph tag ( </w:p> ) and the opening tag you have found, insert the following:

<xsl:for-each select="table1/Detail_Collection/Detail">

We use table1 because that is the name of the table in Report1.rdl. Now we need to find the place for the closing tag:

</xsl:for-each>

In our example, we have placed it right before the <w:sectPr...> tag. The correct place for your case will depend on your particular layout. To be able to easily find the correct places for the starting and ending xsl:for-each tags, it might be usefull to use placeholders (special characters) as we did with the field names (during the layout preparation). Since TidyOutput5.xsl is an indented version (images are broken), we generate the non-indented version of it:

tidy -utf8 -xml -output TidyOutput6.xsl TidyOutput5.xsl

and upload TidyOutput6.xsl to Reporting Services.

9. Production Now the report is in place and the correct .xsl is also deployed to reporting services server. You can retrieve the document using:

http://reportserver.yourdomain.com/ReportServer?/directory/Report1
 &rs:Format=XML
 &rs:Command=Render
 &rc:XSLT=TidyOutput6.xsl
 &rc:FileExtension=xml

Note that the final document is not a Microsoft Word Document file (.doc), but a XML file that contains information to be interpreted by Microsoft Word 2003 and be displayed as if it were a DOC file. If you really need the file to be a Microsoft Word Document file you can then (once opened inside Word) Save as... and select the .doc format. This can be achieved also using VBScript:

Set oApp = CreateObject("Word.Application")
oApp.WindowState = 1  ' 0=wdWindowStateNormal; 1=wdWindowStateMaximize; 2=wdWindowStateMinimize
oApp.Application.Visible = False
oApp.Application.Documents.Open XMLFile ' Dim XMLFile As String
oApp.Application.ActiveDocument.SaveAs DOCFile, wdFormatDocument ' Dim DOCFile As String
oApp.Application.Visible = True ' Now the currently opened file is a DOC file

Download: You can download all the sample files together with the resulting report from here.

Keywords: reporting services, rs, render, export, generate, word, ms word, word document, word file, doc, xml, xsl, templates