VBA error: 'Compile Error. Cannot find Project or library' in 2003

[srikanth_s]

Hi All,

I'm very new to writing code and do a lot of copy/paste from the recorder. With that out of the way, I've written what seems like a fairly simple routine for creating a word doc out of content in an excel file. Both versions 2007. When my boss runs this in Excel/Word 2003 he gets the error cited in the subject line above 'Compile Error. Cannot find Project or Library.' I assume this is because I wrote it in 2007, and I assume I need to write it in a backwards-compatible way. My problem is I just don't know how to get started with figuring out which methods/arguments that I'm using are unavailable in 2003. My code is below; I'd love some guidance not only on what is awry with this code in particular, but more generally how to approach writing backwards-compatible code.

Private Sub cmdSendToWord_Click()
    Dim i
        Dim msWord As New Word.Application
        msWord.Visible = True
        msWord.Documents.Add
    With msWord
        .Selection.TypeParagraph
        .Selection.InsertDateTime DateTimeFormat:="M/d/yyyy", InsertAsField:=False, _
            DateLanguage:=wdEnglishUS, CalendarType:=wdCalendarWestern, _
            InsertAsFullWidth:=False
        .Selection.TypeParagraph
        .Selection.TypeParagraph
        .Selection.TypeText Text:="Dear "
        .Selection.FormFields.Add Range:=.Selection.Range, Type:=wdFieldFormTextInput
        .Selection.TypeText Text:=":"
        .Selection.TypeParagraph
        .Selection.Font.Size = 1
        .Selection.TypeParagraph
        .Selection.Font.Size = 11
        .ActiveDocument.FormFields("Text1").Result = "RECIPIENT"
        .Selection.FormFields.Add Range:=.Selection.Range, Type:=wdFieldFormTextInput
        .ActiveDocument.FormFields("Text2").Result = "INTRODUCTORY TEXT"
    End With
    i = 4
    Do
        If Range("B" & i).Text = "" Then
            Exit Do
        End If
        
        If Range("e" & i).Text = True Then
            Range("B" & i, "D" & i).Copy
'            msWord.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:=wdInLine, DisplayAsIcon:=False
            msWord.Selection.PasteExcelTable False, True, False
'            msWord.Selection.TypeParagraph
            msWord.ActiveDocument.PrintPreview
        End If
        i = i + 1
    Loop
    msWord.Selection.TypeParagraph
    msWord.Selection.FormFields.Add Range:=msWord.Selection.Range, Type:=wdFieldFormTextInput
    msWord.ActiveDocument.FormFields("Text3").Result = "CONCLUDING TEXT"
    Application.CutCopyMode = False
    ActiveSheet.Shapes("Picture 125").Copy
    msWord.ActiveDocument.PageSetup.DifferentFirstPageHeaderFooter = True
    msWord.ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
    msWord.ActiveWindow.Selection.Paste
    msWord.ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
    msWord.ActiveDocument.FormFields.Shaded = Not msWord.ActiveDocument.FormFields.Shaded
    msWord.ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End Sub

Thanks in advance

 

[rconverse]

You need to make sure both of your references are the same. Sounds like you two are out of sync with your references more than a 2003/2007 issue.

In VBE if you go to Tools | References, see what you have checked. Confirm your boss has the same ones checked.

In fact, as I type this, it's probably both. His references for Word will be MS word 11.0. Yours are MS word 12.0. He'll need to change his references.

HTH,
Roger

 

[srikanth_s]

Thanks for your response rconverse.

This raises a larger concern -- I'm looking to deploy this file as a tool to a team of about 15 people of varying technical abilities / comfort levels. Is there a way to avoid having to force pretty excel-illiterate people to check their references by writing the code with them in mind?

Also, on making sure both sets of references are the same -- as you said, if I write it in 2007 I'll have the office 12.0 library. How would he have access to that in 2003?

Thanks again ...

 

[RoryA]

You can use late binding instead, which means you don't need to set any references. Declare all your word objects simply As Object and use CreateObject to initialise the word application. You will however need to either use literal values for any word constants (such as wdFieldFormTextInput) or declare them yourself at the top of your code.

 

[srikanth_s]

Hmm ... that sounds promising.

Is anyone aware of a good tutorial/article on late- vs. early-binding? As I said I'm very, very new to this.

Also, I recall attempting to use literal values for constants in this project at one time -- specifically with wdFieldFormTextInput. I think quick info cited the literal value as "70 (&H46)." How do I read that / which portion would I use?

Thanks so much again for your help.

 

[RoryA]

There's a reasonable basic run-through here.

 

[prawer]

Thanks rorya. I'm converting to late-bound now, and am getting the error, "run-time error 4605: method or property is not available because the clipboard is empty or not available."

The concept of this document is to create a word doc that includes some form fields for user input as well selected rows from a table in the excel sheet.

The debugger is pooping out at the line

msWord.Selection.PasteExcelTable False, True, False

.

Does this have something to do with error handling / managing multiple instances of word?

My attempt at conversion to late-bound:

Private Sub cmdSendToWord_Click()
    Dim i
        Dim msWord As Object
        Set msWord = CreateObject("Word.Application")
        msWord.Visible = True
        msWord.Documents.Add
    With msWord
        .Selection.TypeParagraph
        .Selection.InsertDateTime DateTimeFormat:="M/d/yyyy", InsertAsField:=False, _
            DateLanguage:=1033, CalendarType:=0, _
            InsertAsFullWidth:=False
        .Selection.TypeParagraph
        .Selection.TypeParagraph
        .Selection.TypeText Text:="Dear "
        .Selection.FormFields.Add Range:=.Selection.Range, Type:=70
        .Selection.TypeText Text:=":"
        .Selection.TypeParagraph
        .Selection.Font.Size = 1
        .Selection.TypeParagraph
        .Selection.Font.Size = 11
        .ActiveDocument.FormFields("Text1").Result = "RECIPIENT"
        .Selection.FormFields.Add Range:=.Selection.Range, Type:=70
        .ActiveDocument.FormFields("Text2").Result = "INTRODUCTORY TEXT"
    End With
    i = 4
    Do
        If Range("B" & i).Text = "" Then
            Exit Do
        End If
        
        If Range("e" & i).Text = True Then
            Range("B" & i, "D" & i).Copy
'            msWord.Selection.PasteSpecial Link:=False, DataType:=wdPasteText, Placement:=wdInLine, DisplayAsIcon:=False
             msWord.Selection.PasteExcelTable False, True, False
'            msWord.Selection.TypeParagraph
            msWord.ActiveDocument.PrintPreview
        End If
        i = i + 1
    Loop
    msWord.Selection.TypeParagraph
    msWord.Selection.FormFields.Add Range:=msWord.Selection.Range, Type:=70
    msWord.ActiveDocument.FormFields("Text3").Result = "CONCLUDING TEXT"
    Application.CutCopyMode = False
    ActiveSheet.Shapes("Picture 125").Copy
    msWord.ActiveDocument.PageSetup.DifferentFirstPageHeaderFooter = True
    msWord.ActiveWindow.ActivePane.View.SeekView = 9
    msWord.ActiveWindow.Selection.Paste
    msWord.ActiveWindow.ActivePane.View.SeekView = 0
    msWord.ActiveDocument.FormFields.Shaded = Not msWord.ActiveDocument.FormFields.Shaded
    msWord.ActiveDocument.Protect Type:=2, NoReset:=True
End Sub

Thanks again, in advance.