I am a newbie at VBA programming and this is my first post on a forum so I would like to apologise beforehand in case i make any mistakes.
I am automating an Excel workbook which opens and works with a new Word file from a template. I am using 'WithEvents' to track application events in Word. I am also using code to remove word object library references when closing the workbook and then adding them again at 'Workbook_Open' to make sure this workbook will work on other machines with different versions of word.
Everything works as expected except for a 'Compile Error: User-defined type not defined' error every time I open the workbook but consequent compilations work fine without a hitch. I know what is causing it - there is no reference to word object library during the first compile trial hence the compiler does not know what 'Word.Application' is but from the second instance onwards it does and hence produces no errors.
I just cannot ge my head around how to fix this. I have looked into 'LateBinding' but from research I found out 'WithEvents' is not compatible with LateBinding. Any help will be greatly appreciated.
Thank you in advance for your time.
Option Explicit
Private Sub Workbook_Open()
ThisWorkbook.VBProject.References.AddFromGuid GUID:="{00020905-0000-0000-C000-000000000046}", Major:=0, Minor:=0
ThisWorkbook.VBProject.References.AddFromGuid GUID:="{00062FFF-0000-0000-C000-000000000046}", Major:=0, Minor:=0
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If IsEmpty(ThisWorkbook.VBProject.References.Item("Word")) = False Then
ThisWorkbook.VBProject.References.Remove ThisWorkbook.VBProject.References.Item("Word")
End If
If IsEmpty(ThisWorkbook.VBProject.References.Item("Outlook")) = False Then
ThisWorkbook.VBProject.References.Remove ThisWorkbook.VBProject.References.Item("Outlook")
End If
ActiveWorkbook.Save
Set wdAppClass = Nothing
Set wdAppClass.wdApp = Nothing
'Set wdApp = Nothing
Set wdDoc = Nothing
Set button = Nothing
End Sub
Option Explicit
Public WithEvents wdApp As Word.Application
Private Sub wdApp_DocumentBeforeClose(ByVal Doc As Document, Cancel As Boolean)
Dim datecheck As Boolean
ThisWorkbook.ActiveSheet.Range("F" & row & "").Value = wdDoc.Shapes(1).TextFrame.TextRange.Text
ThisWorkbook.ActiveSheet.Range("K" & row & "").Value = wdDoc.Shapes(2).TextFrame.TextRange.Text
datecheck = IsDate(wdDoc.Shapes(3).TextFrame.TextRange.Text)
If datecheck = True Then
ThisWorkbook.ActiveSheet.Range("M" & row & "").Value = wdDoc.Shapes(3).TextFrame.TextRange.Text
Set_Reminder
End If
wdAppClass.wdApp.Quit
wdApp.Quit
wdDoc.Close
Set wdAppClass = Nothing
Set wdAppClass.wdApp = Nothing
Set wdApp = Nothing
Set wdDoc = Nothing
Set button = Nothing
End Sub