Creating Access2016 Modules with VBA

Viewed 27

I am new to VBA and am actually copying/modifying an old Access (Pre 2016version) module VBA procedure into a new Database (2016), modifying the links. I have checked the paths and think those are correct, but I am getting errors. The purpose of the procedure is to open a word document and autofill to bookmarks with information from forms. The first error is a compile error, user-defined type, not defined. This is occurring on the second line, Dim wApp As Word.Application. I have not taken it further but imagine I will see issues. What should I be doing different?

Option Compare Database

Option Explicit


Public Sub ExportKaiserReferralForm2022()
Dim wApp As Word.Application
Dim wDoc As Word.Document
Dim rs As DAO.Recordset

Set wApp = New Word.Application
Set wDoc = wApp.Documents.Open("U:\WC Files\Pre Employment\Access\New Database\Modules\KasierReferralForm2022")

If Not rs.EOF Then rs.MoveFirst


Do Until rs.EOF
wDoc.Bookmarks("Clinic").Range.Text = Nz(rs!Clinic, "")
wDoc.Bookmarks("Date_Input").Range.Text = Nz(rs!Date_Appt_Sent_To_Clinic, "")
wDoc.Bookmarks("Name").Range.Text = Nz(rs!Name, "")
wDoc.Bookmarks("Street_Address").Range.Text = Nz(rs!Street_Address, "")
wDoc.Bookmarks("City").Range.Text = Nz(rs!City, "")
wDoc.Bookmarks("Zip_Code").Range.Text = Nz(rs!Zip_Code, "")
wDoc.Bookmarks("Phone_Contact").Range.Text = Nz(rs!Phone_Contact, "")
wDoc.Bookmarks("DOB").Range.Text = Nz(rs!DOB, "")
wDoc.Bookmarks("Job_Title").Range.Text = Nz(rs!Job_Title, "")
wDoc.SaveAs2 "U:\WC Files\Pre Employment\Access\New Database\Modules\Test\Kaiser\" & rs!ID & rs!Name & "_KasierReferralForm2022"

wDoc.Bookmarks("Clinic").Range.Delete wdCharacter, Len(Nz(rs!Clinic, ""))
wDoc.Bookmarks("Date_Input").Range.Delete wdCharacter, Len(Nz(rs!Date_Appt_Sent_To_Clinic, ""))
wDoc.Bookmarks("Name").Range.Delete wdCharacter, Len(Nz(rs!Name, ""))
wDoc.Bookmarks("Street_Address").Range.Delete wdCharacter, Len(Nz(rs!Street_Address, ""))
wDoc.Bookmarks("City").Range.Delete wdCharacter, Len(Nz(rs!City, ""))
wDoc.Bookmarks("Zip_Code").Range.Delete wdCharacter, Len(Nz(rs!Zip_Code, ""))
wDoc.Bookmarks("Phone_Contact").Range.Delete wdCharacter, Len(Nz(rs!Phone_Contact, ""))
wDoc.Bookmarks("DOB").Range.Delete wdCharacter, Len(Nz(rs!DOB, ""))
wDoc.Bookmarks("Job_Title").Range.Delete wdCharacter, Len(Nz(rs!Job_Title, ""))

rs.MoveNext

Loop

wDoc.Close False
wApp.Quit

Set wDoc = Nothing
Set wApp = Nothing
Set rs = Nothing

End Sub
1 Answers

There are two ways to solve your current Compile error regarding Word.Application being a user-type undefined.

1. You can add a Reference to Microsoft Access so that it "knows" what a Word.Application is.

From the VBA Editor, Click on the [Tools] Menu and choose [References...].

enter image description here

Then, in the References dialog, scroll down and put a check next to the [Microsoft Word 16.0 Object Library] and click [OK] to close the dialog. (NOTE: Your version of the Microsoft Word Object Library may be different if you have an older or newer version of Word {i.e. 14.0, 15.0, 16.0})

enter image description here

This Reference allows Microsoft Access to "know" about the additional data types, objects and methods that are available in Microsoft Word. If you database will be distributed and run by other people, then this is not the best method as maintaining the correct Reference can be difficult if they have a different version of Word installed.

OR

2. You can create the Word.Application object in code. Your code would need modified to include error handling and change the data types to [Object].

Public Sub ExportKaiserReferralForm2022()
    On Error GoTo Err_PROC
        Dim wApp As Object
        Dim wDoc As Object
        Dim rs As DAO.Recordset
        
        Set wApp = GetObject(, "Word.Application")
    Continue_PROC:
        Set wDoc = wApp.Documents.Open("U:\WC Files\Pre Employment\Access\New Database\Modules\KasierReferralForm2022")

        If Not rs.EOF Then rs.MoveFirst

        Do Until rs.EOF
            wDoc.Bookmarks("Clinic").Range.Text = Nz(rs!Clinic, "")
    
        'The rest of your Bookmark code here

            rs.MoveNext
        Loop
        wDoc.Close False
        wApp.Quit    
        
    Exit_PROC:
        Set wDoc = Nothing
        Set wApp = Nothing
        Set rs = Nothing
        Exit Sub
    
    Err_PROC:
        Select Case Err.Number
            Case 429    'ActiveX Component Can't Create Object
                        'Word wasn't already running, start a new instance
                Set wApp = CreateObject("Word.Application")
                Resume Continue_PROC
            Case Else
                MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
                Resume Exit_PROC
        End Select
End Sub

This will dynamically instantiate a Word.Application object, regardless of the version of Microsoft Word on the target computer.

NOTE: Based on the code you posted, your RecordSet object hasn't been initialized, so currently, your code won't have any data to process. But, once you fill your RecordSet object and make the changes above, the Bookmarks in the Word Document should be filled in.

Related