VBA to enable word 16 reference

cbutters

New Member
Joined
Aug 2, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,

I am developing an excel document that prints a table over to a memo on word. I currently have the whole process figured out except one thing... how to enable the word reference from script. I know that you can go into the tools and enable it, however this worksheet will be used across the whole business enterprise and some users will not know how to do that (nor should need to know how to access the VBA scripting). All of the computers run the same Office 365 and receive the same firmware/software patches through the IT infrastructure.
In an ideal scenario the worksheet would automatically enable the references similar to setting the environment in C++, however I would settle for being able to write it into the same VBA scripting as the print function. I currently have the worksheet set up with three buttons: (with associated macros) one to move certain items over to the new sheet, one to clear the whole sheet, and one to print to the memo template in word. I will paste all of the codes below (maybe someone can help me clean it up and make it better?) but the big question is how to set the reference for Word (Microsoft Word 16.0 Object Library). Thank you in advance for any help!!

Copy to Word Code:

VBA Code:
Sub CopyToWord_GO()

'need to add how to add the reference*********


    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Set wApp = CreateObject("Word.Application")
    wApp.Visible = True
    LastRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
   
    Set wDoc = wApp.Documents.Open("C:\Users\CB\Desktop\EXCEL TEST\Memo Test\Go memo Temp.dotx")

'copy what you want to paste from excel
    Range("A2:E" & LastRow).Copy
   
  
'select the word range you want to paste into
    wDoc.Bookmarks("Table1").Select

'and paste the clipboard contents
    wApp.Selection.Paste

End Sub

Copy to sheet:

VBA Code:
Sub copy18estay()
Dim r As Range, C As Range
With Worksheets("18E")
Set r = Range(.Range("E4"), .Range("E200").End(xlDown))
For Each C In r
If WorksheetFunction.IsNumber(C) Or WorksheetFunction.IsText(C) Then
Range(.Cells(C.Row, "A"), .Cells(C.Row, "E")).Copy
Else
GoTo nextc
End If
With Worksheets("STAY")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
nextc:
Next C
End With
Application.CutCopyMode = False

'adjusts column width
With Worksheets("STAY").Columns("E")
 .ColumnWidth = 21
End With

'auto fit the rows
With Worksheets("STAY")
Rows("3:300").EntireRow.AutoFit
End With

'set all alignment
With Worksheets("STAY").Columns("A:E")
.VerticalAlignment = xlCenter
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
       
End With
       
End Sub
 
Last edited by a moderator:

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Welcome to the MrExcel Message Board.

First of all, you are using late binding with the CreateObject method, which means you don't need to add the Word Object Library since the CreateObject will instantiate the required application object without the reference. As long as the application is installed on the computer, it will work. However, you need to change the object variable declaration as below:

VBA Code:
Dim wApp As Object
Dim wDoc As Object

You are not using any Word object library constant in the current code, but if you did, then you would have also changed those with the corresponding values (such as 20 instead of wdSelectionMode - just a sample). However, as I said, you are not using any wd constant, so it is fine for the current macro.

Second, when you save the workbook and distribute it, then all the object references will be loaded in all copies of the workbook. It means you don't have to do something specific unless the client computer has another version of the Word application.

And finally, is it possible with VBA? Yes, kind of but really complicated that you already don't need with the current macro. You might try to refer to the dll file directly, or there was a method by using VBProject.References.AddFromGuid, which I never used since it is really ugly to deal with the registry keys to get the GUID, and honestly, not necessary for this macro. Still, I found the following thread in the forum (although the question is about something else, the implementation of the method is correct):

Again. The CreateObject method is your friend here and you just need to change the declaration.
 
Upvote 0
Solution
Thank you for the quick response! I will continue to develop the project as it unfolds, but this start should help big time! Though I haven't done a ton of digging on it yet, is there an easy way to force the user to save the word document as a new document upon the creation? I would prefer to make a pop-up and force them to save it so that they don't overwrite the template as that could be painful!
 
Upvote 0
Instead of using the Open method and opening the template itself, you need to use Add method to create a new document by using the template's name that will be used to create the new document:
See the Documents.Add method for more information. Basically, if you use the Add method without providing the Template argument, then it uses the Normal template to create the new document. However, if you use the following code:

VBA Code:
Set wDoc = wApp.Documents.Add("C:\Users\CB\Desktop\EXCEL TEST\Memo Test\Go memo Temp.dotx")

Then it will create a brand new document based on the selected template, and the actual template will be untouched. You don't need an extra popup.
 
Upvote 0

Forum statistics

Threads
1,223,961
Messages
6,175,651
Members
452,664
Latest member
alpserbetli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top