VBA Access to Word error when creating numbered list

Question

I have an Access database from which I create Word documents. I'm using Office 2016. When I run the code, I either get

Error 462 "Remote server machine does not exist or is unavailable"

or

Error -2147023170 "Error: Automation error, The remote procedure call failed"

and Word closes.

On Error GoTo Err_CMD_Test

'Open Word document
    Set GBL_objWord = CreateObject("Word.Application")
    GBL_objWord.Visible = True
    GBL_objWord.Activate
    Set GBL_objDoc = GBL_objWord.Documents.Add
    GBL_objDoc.Activate

'Traitement
    GBL_objWord.Selection.TypeText Text:="List of something :"
    GBL_objWord.Selection.TypeParagraph

    GBL_objWord.Selection.TypeText Text:="Number one"
    GBL_objWord.Selection.Range.ListFormat.ApplyListTemplate ListTemplate:=ListGalleries(2).ListTemplates(1), ContinuePreviousList:=True, ApplyTo:=0, DefaultListBehavior:=2
    GBL_objWord.Selection.TypeParagraph
    GBL_objWord.Selection.TypeText Text:="Number two"
    GBL_objWord.Selection.Range.ListFormat.ApplyListTemplateWithLevel ListTemplate:=ListGalleries(wdNumberGallery).ListTemplates(1), ContinuePreviousList:=False, ApplyTo:=wdListApplyToWholeList, DefaultListBehavior:=wdWord10ListBehavior


Exit_CMD_Test:
    Exit Sub

Err_CMD_Test:
    Select Case Err.Number
    Case Else
        MsgBox "Erreur : " & Err.Description & vbCrLf & _
               "Numéro : " & Err.Number & vbCrLf & _
               "Procédure : CMD_Test", vbCritical, ""
        Resume Next
    End Select

The code breaks on

GBL_objWord.Selection.Range.ListFormat.ApplyListTemplate _
  ListTemplate:=ListGalleries(2).ListTemplates(1), _
  ContinuePreviousList:=True, ApplyTo:=0, DefaultListBehavior:=2

and on

GBL_objWord.Selection.Range.ListFormat.ApplyListTemplateWithLevel _
  ListTemplate:=ListGalleries(wdNumberGallery).ListTemplates(1), _
  ContinuePreviousList:=False, ApplyTo:=wdListApplyToWholeList, _
  DefaultListBehavior:=wdWord10ListBehavior**"

I use the methods ApplyListTemplate and ApplyListTemplateWithLevel just to try both and the result is the same: an error. On the first one I changed he variables wdNumberGallery, wdListApplyToWholeList, wdWord10ListBehavior with their enumeration values available on MSDN website in attempt to pinpoint the error.

Unfortunately I was unable to achieve this task. What I'm looking for is to have a Word document with the following text :

List of something :

Number one
Number two

...

Thank you all for your help

Errors like this can easily be diagnosed by using `Option Explicit` at the top of the module, and declaring variables explicitly. — Erik A, Aug 28 '18 at 09:26
Thank you for your answer. Option Explicit is already at the top of the module. Sorry for omitting it in my first post. — Sébastien, Aug 28 '18 at 22:38

score 2 · Accepted Answer · answered Aug 28 '18 at 04:49

The problem comes from Access VBA not recognizing ListGalleries when late-binding is used. If that is fully qualified with the Word.Application object, the code works for me:

Set GBL_objWord = CreateObject("Word.Application")
GBL_objWord.Visible = True
GBL_objWord.Activate
Set GBL_objDoc = GBL_objWord.Documents.Add
GBL_objDoc.Activate

'Traitement
GBL_objWord.Selection.TypeText Text:="List of something :"
GBL_objWord.Selection.TypeParagraph

GBL_objWord.Selection.TypeText Text:="Number one"
GBL_objWord.Selection.Range.ListFormat.ApplyListTemplate _
  ListTemplate:=GBL_objWord.ListGalleries(2).ListTemplates(1), _
  ContinuePreviousList:=True, ApplyTo:=0, DefaultListBehavior:=2
GBL_objWord.Selection.TypeParagraph
GBL_objWord.Selection.TypeText Text:="Number two"

I'm not sure to understand what is "late-binding". If it's not a good thing to use it, what else can be done? — Sébastien, Aug 28 '18 at 22:40
@Sébastien Late-binding is neither good nor bad - it's just one way of doing things. The reason you find a lot of code that uses late binding (Dim xxx as Object) is because a reference (Tools/References) is not required. These references can be a problem when people distribute code to others or use code in multiple versions of Office. — Cindy Meister, Aug 29 '18 at 12:42

score 0 · Answer 2 · answered Aug 29 '18 at 01:22

I've done some researches and here is what I found.

First, I forgot to put the variable declaration in my first post. Here is what I missed :

Option explicit
Public GBL_objWord As Object
Public GBL_objDoc As Object

Also, I did not mentionned that the Microsoft Word 16.0 Object Library, among others, was selected in Tools/References.

Next, I've learned the difference between Early-binding and Late-binding. My variables should have been :

Public GBL_objWord As Word.Application
Public GBL_objDoc As Word.Document

This change has not corrected the error but when I run very long procedures in which the result is a 7 pages Word Document, the process seems to have speeded-up quite a bit.

Now that I have access to all the Word functions, I tried

GBL_objWord.Selection.Range.ListFormat.ApplyNumberDefault

And it works perfectly!!!

Even if none of your answer was the good one, I would like to thank you both for your help and your guidance as these answers have sent me on the right track.

Thank you,

Sincerely

Hmmm, I believe the code I posted did work correctly. But perhaps you did not notice how it differed from that in the Question? I added `ListTemplate:=**GBL_objWord.**ListGalleries(2).ListTemplates(1)` so that it would work with late binding. — Cindy Meister, Aug 29 '18 at 12:45
And you need to be careful with `GBL_objWord.Selection.Range.ListFormat.ApplyNumberDefault` as the number default may not always be what it was when you wrote and tested this code. But FWIW `ListGalleries` are also not 100% reliable depending on what happens in that Ribbon button. Those images can be deleted or even changed. To be 100% certain you'd need to record a macro while setting up a custom List. This just for your information! — Cindy Meister, Aug 29 '18 at 12:46

score 0 · Answer 3 · answered Jan 16 '23 at 03:59

I was having some errors too and here's how I fixed it:

You need to first construct the object after you've constructed your word application like this

Set GBL_objWord = CreateObject("Word.Application")
GBL_objWord.Visible = True
GBL_objWord.Activate
Set GBL_objDoc = GBL_objWord.Documents.Add
GBL_objDoc.Activate

Dim formatter As Object
Set formatter = GBL_objWord.ListGalleries(2).ListTemplates(1)

Then replace the list template with the object and this section should no longer return an error:

GBL_objWord.Selection.Range.ListFormat.ApplyListTemplate _
ListTemplate:=formatter, _
ContinuePreviousList:=True, ApplyTo:=0, DefaultListBehavior:=2

VBA Access to Word error when creating numbered list

3 Answers3