Setting range in Word with VBA in Excel

Question

How do I set a range in Word while opening that file with VBA in Excel?

Dim wordApp As Word.Application
Dim wordObject As Word.Document
Dim wordRange As Word.Range

Dim filePath As String
Dim fileName As String

filePath = "C:\Users\"
fileName = "somename.docx"

Set wordApp = CreateObject("Word.Application")

With wordApp
    .Visible = True
    .Activate
    .WindowState = wdWindowStateNormal
End With

Set wordObject = wordApp.Documents.Open(filePath & fileName)

Set wordRange = Documents(fileName).Sections(1).Range

With wordRange
    'code
End With

The line causing trouble:

Set wordRange = Documents(fileName).Sections(1).Range

Regardless of the string I put in this returns

4160 runtime error "Bad File Name"

If I use ActiveDocument instead of Documents(), I get

4248 runtime error: "This command is not available because no document is open".

The error persists even after opening multiple unsaved and saved Word docs whilst running the code, only to have the same error message show up.

chris neilsen · Accepted Answer · 2020-02-09 00:58:27Z

2

Set wordRange = Documents(fileName).Sections(1).Range errors because Excel doesn't know what Documents is (or it resolves it to something other than Word.Documents)

To fix that, you'd use (just as you did in the previous line)

Set wordRange = wordApp.Documents(fileName).Sections(1).Range

That said, you've already Set the Document(filepath & filename) to wordObject, so use it:

Set wordRange = wordObject.Sections(1).Range

Also, Excel doesn't know wdWindowStateNormal, so a new Variant variable is created (unless you have Option Explicit, which you should, always) and assigned the default value 0. Which just happens to be the value of Word.wdWindowStateNormal so no harm done, but the code is misleading.

To fix, use

.WindowState = 0 'wdWindowStateNormal

I'm curious about the way you've created the object. Using early binding but instead of creating New Word.Application you use CreateObject

Was this an intentional decision?
What is the benefit?

edited Feb 9, 2020 at 0:58

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

answered Feb 8, 2020 at 21:20

ProfoundlyOblivious

1,4751 gold badge8 silver badges12 bronze badges

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

3 Comments

Cindy Meister Over a year ago

@chrisneilsen I'd guess the OP uses CreateObject because so many code samples here and elsewhere use it instead of New. Almost every question I see that manipulates another application from within Excel (and Access) use CreateObject. True, most also use late-binding, but not all... The answer here might benefit from an explanation of what early- and late-binding are and how to use them correctly since the OP almost certainly doesn't understand the difference.

chris neilsen Over a year ago

@cindy yep, I agree. That comment was the last of a back and forth that lead to the current version of the answer (don't know if you saw those) that's now been cleaned up. Doesn't make much sense to leave it on it's own, I'll delete it shortly. I don't think adding a discussion of early and late binding to this Q+A would make sense, but do you know a good canonical that could be referenced?

Cindy Meister Over a year ago

@chrisneilsen I don't have a link, right off-hand, although I'm sure there must be one on the site. And yes, such a link would probably be better than a full explanation. FWIW I flagged the rest of the comments for clean-up, which is what prompted my comment. I'd also remove those questions at the end of the answer as it appears the OP is not responding...

Collectives™ on Stack Overflow

Setting range in Word with VBA in Excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related