Problem with Reference Libraries between different Office Versions

[BigShango]

hi,

Hopefully you guys can help me here. I have an Excel workbook that is used by a few different people, conveniently we all have different versions of Office. I don't have rights to install or upgrade/downgrade anything on the other computers.

The problem is the Microsoft Word Reference Library. I've made the workbook with the Word 14.0 Reference Library, if anyone opens it then saves it on a later version of Office it automatically changes this to Word 15.0 Reference Library, then nothing will work for anyone on the older versions as Word 15.0 comes up missing.

Is there any way around this? Either a way to add/remove reference libraries through VBA code that I could have run when the workbook opens, or a way to make my code not need the reference library?

Or something else entirely, I'm up for anything that would save me having people come to me every other day with this not working. I know it's a one minute fix to sort the reference libraries every time but the other users need a fully working version.

(The code itself does a lot of opening Word documents, replacing text based on data held on the workbook and then emailing those documents out, but I'd assume that's irrelevant)

Thanks in advance
D

 

[Fluff]

You can use something like this

    Dim WrdApp As Object

    On Error GoTo OpenWrd
    Set WrdApp = GetObject(, "word.application")
    On Error GoTo 0

Exit Sub

OpenWrd:
    Set WrdApp = CreateObject("Word.Application")
    Resume Next

Which means you don't need to set the reference

 

[BigShango]

Thanks for the swift reply.

So I'd replace any piece of code that opens a word document with this bit of code and put the rest at the end? I'm not really following what that does tbh. It looks like it'll just replace that code with the code in the error handler, so is there a reason I shouldn't just replace the Word opening code with that code from the error handler? ie, why would I want it to only use that code on error and not always use it?

And is it only opening the application that needs the reference library, will things like find & replace still work?

On Error GoTo OpenWrd
Set WrdApp = GetObject(, "word.application")
On Error GoTo 0

 

[BigShango]

Turns out I'm already using CreateObject("Word.Application").

If I untick the reference library completely I get no errors and everything seems to work.

However nothing has been changed on the Word documents now.

This is an example of the code I'm using to find and replace text within a Word doc, which doesn't appear to work at all without the reference library in place, although gives no errors. (edit - Everything else works, naming the word doc, saving it etc)

Dim objword As Object

Set objword = CreateObject("Word.Application")
Set objdoc = objword.Documents.Open(NomFormTemplate)
objdoc.Activate
objword.Visible = True
Set objWdRange = objdoc.Content


With objWdRange.Find
    .ClearFormatting
    .Replacement.ClearFormatting
    .Text = "Xaward"
    .Replacement.Text = Award2
    .Execute Replace:=wdReplaceAll
End With

Edit again - perhaps what I'm really looking for is a way to find and replace text on a word doc, from Excel, without the need for a reference library?

 

[Fluff]

From what I can see there is no need to set references with that code.
However I've only connected to Word from Xl to do Mailmerges, so could be wrong.

 

[BigShango]

With the reference set it will replace any instance of "Xaward" in the Word doc with the text held in the variable Award2, without the reference it doesn't.

I have to admit I don't really have a clue here, I learn this stuff as I go along.

Is it at all possible to set a reference library through code? If so I could perhaps have it set the reference library on open and then unset it before saving? As long as I set it to the oldest possible references the newer versions of Excel should update it automatically?

 

[Fluff]

I believe that it's possible to set references in code, but it's not something I've ever done.

 

[BigShango]

I've figured it out. I'm not quite sure why this is the case, but without the reference library it has no idea what wdReplaceAll means. It does however understand what I mean if I replace that simply with a 2.


In case anyone else comes across this with the same issue, I've used the Object Browser on the Word library to find something called the "Constant value" for wdReplaceAll, which is 2. I'd assume that any other thing that won't work without the reference library will if I replace it with this Constant Value.

Cheers for your help mate, I think I'm there now. Just need to go through my spaghetti code.

 

[Fluff]

Glad you figured it out & thanks for the feedback.

One way that might help you find any other constants that need to be replace is to put

Option Explicit

at the very top of the module (before any code).
This will force you to declare all variables & you will get a compile error for everything that VBA thinks is a variable.