G
GrantS
A client using access 97 runtime has upgraded their office suite to
2003. Unfortunately with office 97 no longer installed they asked if
it was possible run the office 2003 spell checker from their access 97
app (for use on a form's textbox). I have implemented the below
solution which can be used successfully in visual basic but I'm having
problems in VBA with the screen not be re-painted when the
spellchecker is in use. i.e someone trys to use another application
while the spell checker is running and the screen appears to crash and
one must Alt Tab back to the spellchecker. The basic functionality is
load the content of the text box into word doc (which is not visible)
which is spell checked and then loaded back into the textbox. The
function is as below:
Private Sub cmdSpellCheck_Click()
On Error GoTo Err_cmdSpellCheck_Click
'Paul Barry Simpl 15/01/04 new function to utilise office 2003
spell and grammar checker
Dim objWord As Object
Dim objDoc As Object
Dim strResult As String
'Create a new instance of word Application
Set objWord = CreateObject("word.Application")
Select Case objWord.Version
'Office 2000
Case "9.0"
Set objDoc = objWord.Documents.Add(, , 1, True)
'Office XP
Case "10.0", "11.0"
Set objDoc = objWord.Documents.Add(, , 1, True)
'Office 97
Case Else ' Office 97
Set objDoc = objWord.Documents.Add
End Select
'Return focus to the control
Me.txtActionComment.SetFocus
'Give the text box content to Word
objDoc.Content = txtActionComment.Text
'Spell and Grammer check content
objDoc.CheckGrammar
'Check new content
strResult = left(objDoc.Content, Len(objDoc.Content) - 1)
'Replaces carriage returns with hard returns for access
strResult = ReplaceChars_TSB(strResult, Chr(13), Chr(13) &
Chr(10))
'Clean up
objDoc.Close False
Set objDoc = Nothing
objWord.Application.Quit True
Set objWord = Nothing
If txtActionComment.Text = strResult Then
' There were no spelling errors, so give the user a
' visual signal that something happened
MsgBox "The spelling and grammer check is complete.",
vbInformation + vbOKOnly
End If
' Replace the selected text with the corrected text. It's
important that
' this be done after the "Clean Up" because otherwise there are
problems
' with the screen not repainting
txtActionComment.Text = strResult
' Old spellchecker code when office 97 was installed
' DoCmd.RunCommand acCmdSpelling
Exit_cmdSpellCheck_Click:
Exit Sub
Err_cmdSpellCheck_Click:
If err = 91 Then 'No textbox has focus
MsgBox "Please click in the text box you wish to check!",
vbCritical, "Error"
Else
MsgBox Error & " " & err, vbCritical, "frmIssue"
End If
Resume Exit_cmdSpellCheck_Click
End Sub
To stop this happening when using a vb app the following line of code
is inserted before the word object is created.
App.OleRequestPendingTimeout = 999999
Unfortunately this is not recognised in access 97 - does anyone know
the equivilent or some type of work around?
2003. Unfortunately with office 97 no longer installed they asked if
it was possible run the office 2003 spell checker from their access 97
app (for use on a form's textbox). I have implemented the below
solution which can be used successfully in visual basic but I'm having
problems in VBA with the screen not be re-painted when the
spellchecker is in use. i.e someone trys to use another application
while the spell checker is running and the screen appears to crash and
one must Alt Tab back to the spellchecker. The basic functionality is
load the content of the text box into word doc (which is not visible)
which is spell checked and then loaded back into the textbox. The
function is as below:
Private Sub cmdSpellCheck_Click()
On Error GoTo Err_cmdSpellCheck_Click
'Paul Barry Simpl 15/01/04 new function to utilise office 2003
spell and grammar checker
Dim objWord As Object
Dim objDoc As Object
Dim strResult As String
'Create a new instance of word Application
Set objWord = CreateObject("word.Application")
Select Case objWord.Version
'Office 2000
Case "9.0"
Set objDoc = objWord.Documents.Add(, , 1, True)
'Office XP
Case "10.0", "11.0"
Set objDoc = objWord.Documents.Add(, , 1, True)
'Office 97
Case Else ' Office 97
Set objDoc = objWord.Documents.Add
End Select
'Return focus to the control
Me.txtActionComment.SetFocus
'Give the text box content to Word
objDoc.Content = txtActionComment.Text
'Spell and Grammer check content
objDoc.CheckGrammar
'Check new content
strResult = left(objDoc.Content, Len(objDoc.Content) - 1)
'Replaces carriage returns with hard returns for access
strResult = ReplaceChars_TSB(strResult, Chr(13), Chr(13) &
Chr(10))
'Clean up
objDoc.Close False
Set objDoc = Nothing
objWord.Application.Quit True
Set objWord = Nothing
If txtActionComment.Text = strResult Then
' There were no spelling errors, so give the user a
' visual signal that something happened
MsgBox "The spelling and grammer check is complete.",
vbInformation + vbOKOnly
End If
' Replace the selected text with the corrected text. It's
important that
' this be done after the "Clean Up" because otherwise there are
problems
' with the screen not repainting
txtActionComment.Text = strResult
' Old spellchecker code when office 97 was installed
' DoCmd.RunCommand acCmdSpelling
Exit_cmdSpellCheck_Click:
Exit Sub
Err_cmdSpellCheck_Click:
If err = 91 Then 'No textbox has focus
MsgBox "Please click in the text box you wish to check!",
vbCritical, "Error"
Else
MsgBox Error & " " & err, vbCritical, "frmIssue"
End If
Resume Exit_cmdSpellCheck_Click
End Sub
To stop this happening when using a vb app the following line of code
is inserted before the word object is created.
App.OleRequestPendingTimeout = 999999
Unfortunately this is not recognised in access 97 - does anyone know
the equivilent or some type of work around?