Connect to MS-Word docs in SharePoint and copy contents to local MS-Word doc using Stream

Question

I have MS-Word Document templates stored in SharePoint.

I want to copy the contents of the word document(s) in SharePoint, and save to a local Word document using Excel VBA.

I have the following Sub (which is a template sub that I call within other subs).

I get an error

Object doesn't support this property or method

on the line stream.Write xml.responseBody.

I realise that "responseBody" is not the correct term for a Word document but I can't figure out what to put here.

Sub Letter_Template(url As String)

    Dim xml As Object
    Dim localFilePath As String
    Dim WordApplication As Word.Application
    Dim WordDocument As Word.Document

' This section lets Word to grab files from the provided url and place it into a temporary location.

    localFilePath = Environ("TEMP") & "\LetterTemplate.docx"

    Set xml = CreateObject("MSXML2.XMLHTTP")
    xml.Open "GET", url, False
    xml.Send

    If xml.Status = 200 Then

        Dim stream As Object
        Set stream = CreateObject("ADODB.Stream")
        stream.Open
        stream.Type = 1
        stream.Write xml.responseBody
        stream.SaveToFile localFilePath, 2
        stream.Close

    Else

        MsgBox "Failed to download the file. Status: " & xml.Status
        Exit Sub

    End If

    ' Open the downloaded file in Word

    Set WordApplication = New Word.Application
    Set WordDocument = WordApplication.Documents.Open(localFilePath)

    With WordDocument
        .Display
 
    End With

End Sub

I did model this off a similar sub I use to locate and copy Outlook templates in SharePoint so I may be missing parts needed for a Word document.

I set a breakpoint at the steam.Write line and took a screenshot of the Locals Window (cropped out url part at the top as it is sensitive).

Instead of using Dim stream as Object you can do Dim stream As ADODB.Stream and then you'll get IDE IntelliSense features working. — Dai
– Dai, Commented Dec 2, 2025 at 20:21
Please run your Letter_Template sub in the debugger but set a breakpoint on the stream.Write line, then edit your post to show us what you see in the Autos/Locals windows. — Dai
– Dai, Commented Dec 2, 2025 at 20:23
You could use VBA to automate Word to open the document directly from the URL and save a local copy... — Tim Williams
– Tim Williams, Commented Dec 3, 2025 at 18:02

Dai · Accepted Answer · 2025-12-03 01:24:35Z

0

This works for me:

Add VBA Project References (Tools > References) to:
- Microsoft XML, v3.0 (for MSXML2.XMLHTTP)
- Microsoft XML, v6.0 (for MSXML2.XMLHTTP60) ' See https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms763701(v=vs.85)
- Microsoft ActiveX Data Objects 6.1 Library

Sub Letter_Template(url As String, saveToFile As String)

    Dim xml As New MSXML2.XMLHTTP60 ' or MSXML2.XMLHTTP
    xml.Open "GET", url, False
    xml.Send

    Dim responseContentType As String
    Let responseContentType = xml.GetResponseHeader("Content-Type")

    If xml.Status = 200 And responseContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document" Then

        Dim s As New ADODB.Stream
        s.Type = adTypeBinary ' = 1
        s.Open
        s.Write xml.responseBody
        s.Flush
        s.SaveToFile saveToFile, adSaveCreateOverWrite ' or adSaveCreateNotExists
        s.Close

        MsgBox "Saved to " & saveToFile

    Else
        ' TODO: Better error handling
        MsgBox "Unexpected response."

    End If
End Sub

Sub Test()

    Dim url As String
    Let url = "https://officeprotocoldocs-f5hpbjgea6b8gneq.b02.azurefd.net/files/MS-DOCX/%5bMS-DOCX%5d-251113.docx"

    Dim saveTo As String
    Let saveTo = Environ$("USERPROFILE") & "\Downloads\" & "File.docx"

    Call Letter_Template url, saveTo

End Sub

answered Dec 3, 2025 at 1:24

Dai

159k31 gold badges315 silver badges441 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Melissa Rose Dec 4, 2025 at 18:02

Thanks for the suggestions Dai. After adding in the references you suggested, my original code now seems to run file until I need to open the temp file with .Display - then suddenly it errors and says 'Run-time error '3004': Write to file failed.' I did play around with your code but I need to be able to open a word doc so that the user can decide where and what to save the file as, rather than just download it.

Melissa Rose Dec 4, 2025 at 18:18

I can see the file is in my temp folder but if I go to manually open it, I can only open a read only copy as it says it's locked by me? All the content it within the file so I guess it just has something to do with permissions when trying to open the file.

Dai Dec 4, 2025 at 23:06

I don't know, sorry; not without being able to debug it in-person.

jonsson Dec 6, 2025 at 12:30

Probably because WordDocument doesn't have a .Display member! What seems to work better here is to use WordApplication.Visible = True immediately after creating the WOrd object, and (maybe optional ) using .Activate instead of .Display. But I am not sure that completely deals with the situation where your LetterTemplate.docx is left locked, or the situation where Word thinks it needs to be recovered.

Melissa Rose Dec 7, 2025 at 18:20

Thanks for the reply! Yeah I figured out that .Display was causing it to error. Messed around a bit with the temp files and coding and finally got it to work :)

Melissa Rose · Accepted Answer · 2025-12-07 18:23:41Z

Finally got it to work after adding Microsoft XML, v3.0 and Microsoft ActiveX Data Objects 6.1 to the reference list (thanks Dai!)

Then had to tweak the code to the following:

Sub Letter_Template(url As String)

Dim xml As Object
Dim localFilePath As String
Dim WordApplication As Word.Application
Dim WordDocument As Word.Document

' This section lets Word to grab files from the provided url and place it into a temporary location.

localFilePath = Environ$("TEMP") & "\LetterTemplate.docx"
Set xml = CreateObject("MSXML2.XMLHTTP")
xml.Open "GET", url, False
xml.Send
If xml.Status = 200 Then
Dim stream As Object
Set stream = CreateObject("ADODB.Stream")
stream.Open
stream.Type = 1
stream.Write xml.responseBody
stream.saveToFile localFilePath, 2
stream.Close
Else
MsgBox "Failed to download the file. Status: " & xml.Status
Exit Sub
End If


' Open the downloaded file in Word

Set WordApplication = New Word.Application
WordApplication.Visible = True

Set WordDocument = WordApplication.Documents.Open(localFilePath)
WordDocument.Activate

End Sub

Alternately since you're opening Word anyway: Set WordDocument = WordApplication.Documents.Open(url As String): WordDocument.SaveAs localFilePath

Collectives™ on Stack Overflow

Connect to MS-Word docs in SharePoint and copy contents to local MS-Word doc using Stream

2 Answers 2

5 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Related