Is there a work around for missing VBA Project References

[bisel]

Greetings,

I have a large application developed in Excel that integrates to a small extent with MS Word. I have a VBA macro that creates a Word object and then copies varies elements from the Excel workbook to the Word document.

For this VBA macro to function correctly, there must be reference to the Microsoft Word Object Library. In Office 2019, it is called "Microsoft Word 16.0 Object Library". In other versions of Office is may have a different numeric value.

If the Word Object Library is not selected, the macro results in an error ... "Compile Error: User defined type not defined".

Selecting the reference library is not a big deal and not a problem ... except, by default, it is not selected when one installs Office. I have been telling users that they have to select this option on their installed version of Excel. Ideally, I would to be able to either perform the selection automatically or have a work around so I would not have to have the users perform this manually. Does anyone know of how I might do this? Or, is it something that cannot be avoided?

Thanks,

Steve

 

[Marcelo Branco]

See if this helps
http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/

M.

 

[bisel]

See if this helps
http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/

M.

Thanks for your suggestion, Marcelo,

I had edited my VBA code for late binding ... at least I thought I did. Here is the VBA ....

Sub create_diy_report()


Dim wdApp As Object ' for late binding
Dim wdDoc As Object
Dim newWord As Object
Dim oleObj As Object
Dim pasteobject As Range
Dim diy_excel_range As String
Dim diy_word_bm As String
Dim diy_excel_content As String
Dim diy_word_content As String
Dim diy_vba_case As Integer
Dim diy_sheet_num As Worksheet
Dim ws As Worksheet
Dim detailnum As String
Dim shownum As String
Dim i As Integer
Dim bmrange As String
Dim mytable As Table
Dim headertext As String
Dim errorcount As Integer


Application.ScreenUpdating = False


On Error GoTo errorhandler
errorcount = 0 ' Set errorcount to zero


Application.DisplayAlerts = False


Set oleObj = Sheet5.OLEObjects("diy_template")
oleObj.Verb Verb:=xlPrimary
oleObj.Activate


Application.DisplayAlerts = True


Set wdApp = oleObj.Object.Application


With wdApp
    .Visible = True
    .Activate
    Set wdDoc = wdApp.Documents(1) ' wdDoc will be the embedded Word doc
    .Documents.Add ' add a new document
    Set newWord = wdApp.Documents(1) ' newWord will be the new blank document
    
End With


'Select and copy all content from DIY Template to the new Word document
    wdDoc.Content.Copy ' copy contents from the diy template
    newWord.Content.Paste ' paste contents into the new document


' Close the DIY Template to prevent changing it and then activate the new Word doc
    wdDoc.Close (wdDoNotSaveChanges)
    newWord.Activate


' Perform edits on the new Word document by copying and pasting content from RFA
With newWord


    For Each c In Sheet5.Range("diy_report_bmnum") 'All values from table in Data Validation sheet
        diy_excel_range = c.Offset(0, 1).Value
        diy_word_bm = c.Offset(0, 2).Value
        diy_excel_content = c.Offset(0, 4).Value
        diy_word_content = c.Offset(0, 5).Value
        diy_vba_case = c.Offset(0, 6).Value


        Select Case diy_vba_case


            Case 1 'Copy range and paste as text replace bookmark text with text from Excel range
                .Bookmarks(diy_word_bm).Range.Text = Range(diy_excel_range).Value 'Copies single cell value to replace word bookmark text


            Case 2 'Copy range or table as picture and paste as picture
                Range(diy_excel_range).CopyPicture
                .Bookmarks(diy_word_bm).Range.Characters.Last.Paste


            Case 3 'Copy chart from Sheet10 and paste as picture
                Sheet10.ChartObjects(diy_excel_range).Activate
                Sheet10.ChartObjects(diy_excel_range).Copy 'Copies chart from Excel and pastes as picture in Word
                .Bookmarks(diy_word_bm).Range.PasteSpecial Link:=False, DataType:=15, Placement:=wdInLine, DisplayAsIcon:=False


            Case 4 'Copy chart from Sheet7 and paste as picture
                Sheet7.ChartObjects(diy_excel_range).Activate
                Sheet7.ChartObjects(diy_excel_range).Copy 'Copies chart from Excel and pastes as picture in Word
                .Bookmarks(diy_word_bm).Range.PasteSpecial Link:=False, DataType:=15, Placement:=wdInLine, DisplayAsIcon:=False


            Case 5 'Copy chart from Sheet4 and paste as picture
                Sheet4.ChartObjects(diy_excel_range).Activate
                Sheet4.ChartObjects(diy_excel_range).Copy 'Copies chart from Excel and pastes as picture in Word
                .Bookmarks(diy_word_bm).Range.PasteSpecial Link:=False, DataType:=15, Placement:=wdInLine, DisplayAsIcon:=False


            Case 6 'Copy range or table and paste as table
                Range(diy_excel_range).Copy ' copy range of cells as table from Excel
                .Bookmarks(diy_word_bm).Range.Characters.Last.Paste ' paste to Word
                .Tables(.Tables.Count).Rows.AllowBreakAcrossPages = False
                .Tables(.Tables.Count).Shading.BackgroundPatternColor = wdColorAutomatic
                .Tables(.Tables.Count).Borders.InsideLineStyle = wdLineStyleNone
                .Tables(.Tables.Count).Borders.OutsideLineStyle = wdLineStyleNone


            Case Else
        End Select


       Next c
            
    ' copy all component details as picture and paste into word
        For i = 250 To 1 Step -1 ' For each component item from 1 to 250, do in reverse order
        
            Application.CutCopyMode = False 'clear clipboard
            detailnum = "detail" & i
            shownum = "show" & i
            
            ' Check if the item is included in the component list, if not skip
            If Range(shownum).Value = 1 Then
                Range(detailnum).CopyPicture ' copy range as picture from Excel
                .Bookmarks("comp_details_bookmark").Range.Characters.Last.Paste
                Application.CutCopyMode = False 'clear clipboard
            Else
            End If
        
        Next i
      
End With 'End with newWord doc


' Update all fields in the document
    newWord.TablesOfContents(1).Update
    newWord.TablesOfFigures(1).Update
    newWord.TablesOfFigures(2).Update




' Format tables except the Terms and Def table
    For Each mytable In newWord.Tables
        If mytable.Title <> "terms" Then
            mytable.Range.Font.Size = 9
            mytable.Rows(1).HeadingFormat = True 'Set first row as table header
            mytable.Rows(2).HeadingFormat = True 'Set second row as table header
            mytable.Rows.AllowBreakAcrossPages = False ' prevent rows breaking across pages
        Else
        End If
    Next
        
skipformat:


'Change the header text in the DIY Report
    For i = 2 To newWord.Sections.Count
        With newWord.Sections(i) 'Start with Section 2 of the document to supress header on title page
        .Headers(wdHeaderFooterPrimary).Range.Text = Sheet5.Range("diy_header").Value
        .Headers(wdHeaderFooterPrimary).Range.ParagraphFormat.Alignment = wdAlignParagraphRight
        End With
    Next




Set wdApp = Nothing
Set wdDoc = Nothing


Sheet16.Select


'Display messge box with either Success or Error messaage
    Select Case errorcount
        Case Is = 0
            MsgBox "Reserve Study Report Complete", vbInformation + vbOKOnly, "Reserve Study Report"
            
        Case Is > 0 'display message about an error was found
            MsgBox "Reserve Study Report Created.  A total of " & errorcount & " error(s) encountered during report creation.  " & _
            "Review the report for potential omissions of data such as tables and charts.  " & _
            "You may have to perform manual edits and changes.", vbCritical + vbOKOnly, "Reserve Study Report Errors"
    End Select


Exit Sub


errorhandler:
    errorcount = errorcount + 1
    Resume Next


End Sub

Do you see anything I am missing?

Thanks,

Steve

 

[Marcelo Branco]

Hi Steve,

It's a complicated code
I do not have much experience in automating WORD via EXCEL, but, as recommended in the link, i think you should insert at the beginning of the code
Set wdApp = CreateObject ("Word.Application")

M.

 

[Marcelo Branco]

More information
https://www.reddit.com/r/vba/comments/7hzuzn/rewriting_excel_vba_that_references_word_to_late/

M.

 

[RoryA]

You need to declare mytable as object too. You also need to declare all those constants starting with "wd" as they're from the Word library and have no value if the reference isn't set.