E xtensible Development

Profession blog about Software Engineering, Web, *nix, Processes, Tools and more.

• H ome
• .this
• Architecture
• Business
• Development
• Social
• Software Engineering

Switcher

H ome > Software Engineering > Accessing WebDAV in Microsoft Word Visual Basic

Accessing WebDAV in Microsoft Word Visual Basic

April 28th, 2009

Goto comments Leave a comment

Introduction

In “Document Management System in Word using WebDAV ” I explained why I needed to connect Microsoft Word to a WebDAV repository and in “PHP based authentication for mod_dav ” I explained how to set up authorisation for mod_dav using a PHP script. What’s left is accessing the WebDAV repository using Word’s Visual Basic.

Requirements

• Open documents from the remote location.
• Save documents to the remote location.
• Save new documents to the remote location.
• Overwrite existing documents on the remote location.
• List documents available for specified MemberID’s.
• Add meta-data to stored documents. (Optional)

Solution

Starting from Office 2000, support for WebDAV is included natively. Although Windows XP and never also have their own support for WebDAV, it’s always better to use Office’s own methods. The only downside to this is that these methods are not available in Word’s Visual Basic.1

Word Visual Basic WebDAV interface

Accessing WebDAV resources in Word’s Visual Basic can be done by using an ADODB object. Starting from ADODB 2.5 support for WebDAV is available. ADODB might need to be enabled for Word first.

Activate ADODB in Word

Visual Basic Editor (Word 2003)

To activate (or check if it is available) ADODB in Word, you need to start the Visual Basic Editor. The VBE can be found in Tools\Macro (Word XP & 20032 )

Access to ADODB is determined by the project’s references and those are specific for each Word document, so make sure to start your WebDAV macro projekt first (Done by creating a new Macro from Word).

VBE Rreferences (Word 2003)

Open the References from Tools\References and make sure a “Microsoft ActiveX Data Objects 2.x Library” is selected. Versions 2.5 and newer support WebDAV, but make sure the production environment you will be delivering to has the same version.

As soon as the ADODB library is activated the Visual Basic Editor will recognise the ADODB references in the code.

Opening a document from WebDAV

To open a document from WebDAV the ADODB object is not needed, Word uses it’s internal WebDAV library instead.

Documents.Open "http://example.org/dav/document.doc", False, False

Sometime the opened document becomes read-only, this is can have several causes:

• WebDAV supports locks and the file has been locked. Just like normal local access Word puts a lock on the file if opened for writing. Unlike local access the lock on WebDAV can easily be removed by an administrator (or another Word Macro! See “PHP based authentication for mod_dav ” to know how even this can be moderated with a PHP script.)
• Word erronously identified the WebDAV as an Exhange or SharePoint resource. Both Microsoft Exchange and Microsoft SharePoint use WebDAV to share documents with Office. The problem is caused by the extra meta-data that is expected.

Save a new document to WebDAV

Documents opened from a WebDAV resource are automatically saved back to their original WebDAV location, unless they are opened as read-only. To save a newly created document to a WebDAV repository, just pass the URL to the normal SaveAs function.

ActiveDocument.SaveAs ("http://example.org/dav/test.doc")

Getting a directory listing from WebDAV

Other actions that interact with the WebDAV repository require the ADODB object. Using ADODB creates an interface for WebDAV that acts like a database, with recordsets and records.

Listing 1:

'

' Microsoft Office Visual Basic

' WebDAV Access example

'

' @author Jeffrey Ridout

' @date 2009-04-23

' @version 0.0.1

'

Sub AccessWebDAV()

Dim files() As String ' String array to hold friendly filenames.

Dim davDir As New ADODB.Record ' Record for the directory

Dim davFile As New ADODB.Record ' Record for the file

Dim davFiles As New ADODB.Recordset ' Recordset for the directory's filelist

Dim isDir As Boolean

Dim index As Integer

' For debugging purposes jump to the error display on any error

On Error GoTo showErr

' Open the root folder as a ADODB.Record to retrieve a list of it's contents.

' To open a folder, pass an empty string as the filename and the full URL as the path.

davDir.Open "", _

"URL=http://example.org/dav", _ ' URL= tells ADODB to use the WebDAV interface.

adModeReadWrite, _ ' This could be adModeReadOnly, it might matter in a multi-threaded environment

adFailIfNotExists, _

adDelayFetchStream, _ ' ADODB sometimes pre-fetches the content of files, this is not needed.

"username", _

"password"

Set davFiles = davDir.GetChildren() ' Get the list of files as a ADODB.Recordset

index = 0

' The WebDAV directory Recordset uses a 'current' index and has no counter,

' so use EOF to check if there are more files.

Do While Not davFiles.EOF

davFile.Open davFiles, , adModeReadWrite

' File properties are Record.Fields, all standard DAV fields are called "Dav:<property>".

' ADODB adds it's own fields that supply the same information, called "RESOURCE_<property>".

isDir = davFile.Fields("RESOURCE_ISCOLLECTION").Value ' RESOURCE_ISCOLLECTION is a boolean specifiying if the file is a directory.

If Not isDir Then ' If the file is not a directory it's a normal file and we can add it to the list.

ReDim Preserve files(0 To 1, 0 To index) ' The number of files is unknown, so "ReDim Preserve" resizes the array without losing the data.

files(0, index) = davFile.Fields("RESOURCE_PARSENAME").Value ' RESOURCE_PARSENAME contains the filename.

files(1, index) = davFile.Fields("RESOURCE_ABSOLUTEPARSENAME").Value ' RESOURCE_ABSOLUTEPARSENAME contains the full URL.

index = index + 1

End If

davFile.Close ' Always remember to Close the file!

davFiles.MoveNext ' Move the index to the next Record.

Loop

Set davFiles = Nothing ' Always unset the object pointer!

davDir.Close ' Close the Record for the directory, not closing it can leak memory and could also lock the directory.

Set davDir = Nothing

' Do something with files here...

GoTo noErr

showErr:

Call MsgBox(Err.Number & ": " & Err.Description, vbOKOnly, "Error")

noErr:

' No errors

End Sub

Getting and setting meta-data

Exchange and SharePoint use custom properties to perform extra actions and change file attributes. Custom properties in WebDAV normally have their own namespace, but I haven’t figured out how to specify those with ADODB yet.3 Some examples contain the namespace as part of the property, so I’ve adopted this.

Adding properties for the document’s title, subject, author, etc. makes it possible to display those properties without having to open the document. Whenever the document is saved to WebDAV a Macro function should be triggered to update these properties.

Listing 2:

'...

' Define some constants containing property namespaces in the head of the Macro.

' Constants

Public Const OFFICE_URN As String = "urn:schemas-microsoft-com:office:office"

Public Const EXAMPLE_URN As String = "http://example.org/dav/props/"

' Inside the function, the custom properties can be retrieved and added quite easy.

'...

' Get custom properties.

prop1 = davFile.Fields(OFFICE_URN & "author").Value

prop2 = davFile.Fields(EXAMPLE_URN & "doc_title").Value

prop3 = davFile.Fields(EXAMPLE_URN & "doc_subject").Value

prop4 = davFile.Fields("non-existing-property").Value ' Non existing properties return an empty string.

' Set custom properties.

davFile.Fields(OFFICE_URN & "author") = "user"

davFile.Fields(EXAMPLE_URN & "doc_title") = "Document title"

davFile.Fields(EXAMPLE_URN & "doc_subject") = "Document subject"

davFile.Fields.Update ' Fields.Update commits the changes to Fields to WebDAV.

'...

Putting it all together

Here is the code where all previous techniques are put together. Normally opening, saving and listing would be done in different functions preferably triggered by events, put this will serve as an example.

Listing 3:

'

' Microsoft Office Visual Basic

' WebDAV Access example

'

' @author Jeffrey Ridout

' @date 2009-04-23

' @version 0.0.2

'

' Constants

Public Const OFFICE_URN As String = "urn:schemas-microsoft-com:office:office/"

Public Const SPK_URN As String = "http://schemas.spk.no/dav/props/"

' Module variables

Public isCancelled As Boolean ' Used by dlgOpenFileDAV

Public fileURL As String ' Used by dlgOpenFileDAV

Sub OpenWebDAVFile()

Dim files() As String ' Dynamic Multidimensional String array to hold file properties.

Dim davDir As New ADODB.Record ' Record for the directory

Dim davFile As New ADODB.Record ' Record for the file

Dim davFiles As New ADODB.Recordset ' Recordset for the directory's filelist

Dim isDir As Boolean

Dim index As Integer

Dim dlg As Object

' For debugging purposes jump to the error display on any error

On Error GoTo showErr

' Open the root folder as a ADODB.Record to retrieve a list of it's contents.

' To open a folder, pass an empty string as the filename and the full URL as the path.

davDir.Open "", _

"URL=http://example.org/dav", _ ' URL= tells ADODB to use the WebDAV interface.

adModeReadWrite, _ ' This could be adModeReadOnly, it might matter in a multi-threaded environment

adFailIfNotExists, _

adDelayFetchStream, _ ' ADODB sometimes pre-fetches the content of files, this is not needed.

"username", _

"password"

Set davFiles = davDir.GetChildren() ' Get the list of files as a ADODB.Recordset

index = 0

' The WebDAV directory Recordset uses a 'current' index and has no counter,

' so use EOF to check if there are more files.

Do While Not davFiles.EOF

davFile.Open davFiles, , adModeReadWrite

' File properties are Record.Fields, all standard DAV fields are called "Dav:<property>".

' ADODB adds it's own fields that supply the same information, called "RESOURCE_<property>".

isDir = davFile.Fields("RESOURCE_ISCOLLECTION").Value ' RESOURCE_ISCOLLECTION is a boolean specifiying if the file is a directory.

If Not isDir Then ' If the file is not a directory it's a normal file and we can add it to the list.

ReDim Preserve files(0 To 1, 0 To index) ' The number of files is unknown, so "ReDim Preserve" resizes the array without losing the data.

files(0, index) = davFile.Fields("RESOURCE_PARSENAME").Value ' RESOURCE_PARSENAME contains the filename.

files(1, index) = davFile.Fields("RESOURCE_ABSOLUTEPARSENAME").Value ' RESOURCE_ABSOLUTEPARSENAME contains the full URL.

files(2, index) = davFile.Fields(SPK_URN & "doc-title").Value ' Custom property: title

files(3, index) = davFile.Fields(SPK_URN & "doc-subject").Value ' Custom property: subject

files(4, index) = davFile.Fields(OFFICE_URN & "author").Value ' Custom property: author

index = index + 1

End If

davFile.Close ' Always remember to Close the file!

davFiles.MoveNext ' Move the index to the next Record.

Loop

Set davFiles = Nothing ' Always unset the object pointer!

davDir.Close ' Close the Record for the directory, not closing it can leak memory and could also lock the directory.

Set davDir = Nothing

' See Image 3 and Listing 4 for the OpenFileDAV dialog.

dlgOpenFileDAV.listDocuments.Clear ' Clear the ListBox since dlgOpenFileDAV is reusable.

For I = 0 To index - 1

With dlgOpenFileDAV.listDocuments

.AddItem files(0, I), I ' Add a new row to the ListBox.

.List(I, 1) = files(1, I) ' The ListBox has 4 columns, 2nd column is hidden and contains the full URL.

.List(I, 2) = files(2, I) ' 3d column contains the title.

.List(I, 3) = files(3, I) ' 4th column contains the subject.

.List(I, 4) = files(4, I) ' 5th column contains the author.

End With

Next I

isCancelled = True

fileURL = ""

dlgOpenFileDAV.Show

If (isCancelled = False) Then

If (fileURL <> "") Then

Documents.Open fileURL, False, False ' To open the file from the WebDAV repository, simple pass the URL.

End If

End If

GoTo noErr

showErr:

Call MsgBox(Err.Number & ": " & Err.Description, vbOKOnly, "Error")

noErr:

' No errors

End Sub

Image 3 shows the UserForm used as the custom “Open File” dialog. Image 4 shows the ListBox properties. The ListBox currently only should support selecting 1 file, has 5 columns. Note that the 2nd column has a width of “0 pt” in order to hide it. BoundColunm is set to 2, which results in the Value of the ListBox being the value of the hidden column of the selected row (The file’s full URL).

Image 2: Open WebDAV File dialog

Image 5: ListBox properties

Listing 4:

Private Sub btnCancel_Click()

davTest.isCancelled = True

Hide

End Sub

Private Sub btnOk_Click()

davTest.isCancelled = False

davTest.fileURL = listDocuments.Value

Hide

End Sub

Private Sub listDocuments_Change()

If listDocuments.Value >= 0 Then

btnOk.Enabled = True

End If

End Sub

Private Sub UserForm_Initialize()

If (listDocuments.ListCount > 0) Then

listDocuments.Clear

End If

btnOk.Enabled = False

davTest.isCancelled = True

End Sub

Conclusion

Creating your own WebDAV based repository access in Microsoft Word is possible and after some research and experimenting quite easy. Combine this with a PHP CMS and mod_dav with PHP authentication and authorisation and you’ve got a fully functional Document Management System.

All code in this post is licensed under Creative Commons Attribution Share Alike.

1. Office’s own WebDAV support might be available in VBA, but I have not found any documentation on it. [↩ ]
2. Office 2007 might have hidden it better but ALT+F11 still works. [↩ ]
3. If you find out, let me know! [↩ ]

Software Engineering

office , vba , visualbasic , WebDAV , word