VBA to enable word 16 reference

[cbutters]

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:

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:

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

 

[smozgur]

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:

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):

VBA Add GUID References

Hello, This code allows you to add reference to a specific library on the fly. Sub AddReference() 'Macro purpose: To add a reference to the project using the GUID for the 'reference library Dim strGUID As String, theRef As Variant, i As Long 'Update the GUID...

www.mrexcel.com

Again. The CreateObject method is your friend here and you just need to change the declaration.

 

[cbutters]

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!

 

[smozgur]

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:

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.

 

[cbutters]

Perfect! Thank you very much!

 

[smozgur]

Perfect! Thank you very much!

You're welcome.

Please mark the solution post by using the little tickmark icon on the right side of the answer post for this and your future questions to help future readers.