How to handle Error Number 424 Object Required in MS Access VBA

When you run a VBA function in a Microsoft Access database, you receive the following error message Error number 424: Object Required. This issue occurs if coding is not done properly while automating an office application. One of the advantages of a suite such as Microsoft Office is that all the programs should work well together, with each program doing what it does best. This process is called automation. In practice it means that there is no need for the user to open Word and run a mail merge from Word, linking it to an Access table or Excel spreadsheet. Using VBA, the developer can achieve the same effect, opening Word programmatically (i.e. using VBA code), creating, saving and (if appropriate) printing documents and closing Word without the user having to leave Access.

In order to understand this error user first needs to create a form named frmCreatingWordDoc in MS Access database

How to handle Error Number 424 Object Required in MS Access VBA Fig 1.1

Fig 1.1

As shown in Fig 1.1 in next step we need to create a button with caption Create Word Document. Now the purpose of this form is to open a word file, adding some text to it and subsequently closing it. In order to accomplish it we will automate this task with some VBA code. VBA code has been shown in last two pages of this document. You need to include that code into On Click event of button used on this form (Shown in Fig 1.1)

Now if we execute above code in sequence we will get error as shown in Fig 1.2

How to handle Error Number 424 Object Required in MS Access VBA Fig 1.2

Fig 1.2

The error occurs because the ActiveDocument object has not been fully qualified by reference to the Office object. The line of code that saves the document must be modified to include a specific reference to the instance of Word, as highlighted below. We need to replace ActiveDocument.Save with objApplication.ActiveDocument.Save to avoid this error(Code has been highlighted below)

Once VBA code is corrected you will receive massage on your computer screen as shown in figure Fig 1.3

How to handle Error Number 424 Object Required in MS Access VBA Fig 1.3

Fig 1.3

VBA Code set to On Click event of button:-

Private Sub cmdCreateWordFile_Click()
On Error GoTo errhandlr
Dim strDoc As String
MsgBox "Select Word Document File only !"
strDoc = filedialog_box
Set objApplication = CreateObject("Word.Application")
objApplication.Visible = True
objApplication.Documents.Open strDoc
With objApplication.Selection
.TypeText "Some words for MS Word Application"
End With
ActiveDocument.Save 'error occurs because the ActiveDocument object has not been fully qualified by reference to the Office object
objApplication.ActiveDocument.Close
objApplication.Quit
Set objApplication = Nothing
MsgBox "Record has been added to word file"
Exit Sub
errhandlr:
objApplication.ActiveDocument.Close
objApplication.Quit
Set objApplication = Nothing
MsgBox Err.Number & " " & Err.Description
End Sub

Shown in above code we will also use function named filedialog_box which in turn popups a file dialog box to select intended file.

VBA Code for function:-

Function filedialog_box() As String
Dim intChoice As Integer
Dim strPath As String
Dim fileExtension As String, temp As Variant
'only allow the user to select one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
'get the file path selected by the user
strPath = Application.FileDialog( _
msoFileDialogOpen).SelectedItems(1)
'print the file path to sheet 1
fileExtension = Right(strPath, 4)
If fileExtension = "docx" Then
filedialog_box = strPath
Else
MsgBox "Select Word File only"
End If
End If
End Function


DISCLAIMER

It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. Access Guru will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.