Automation only works once (Access 2000)

Topic

New Reply

From Access, I use automation to insert and format data into a Word doc. I use early binding and Set appWD = CreateObject(“Word.Application”). I set appWd to Nothing when finished, but leave the resulting doc open. This works just fine,

Reply | Quote

Replies

You’re destroying the object variable but you’re leaving the application process itself open. Take a look at this thread and see if that information helps.

Reply | Quote

Charlotte,
Thanks for the suggestions. I am now closing the document then setting appWd to nothing. I also am using the task-closing subroutine that you pointed to. I then open the doc using FollowHyperLink. After manually closing the doc, I still get the same error when I run the routine a second time!
Have a happy new year (I probably won

Reply | Quote

I’m afraid we’re just guessing, since you didn’t post your code. Why don’t you do that and maybe someone can spot the problem.

Reply | Quote

As an afterthought, are you doing an appWd.Quit? You might try that in your routine and see whether it makes a difference. If you manually close the doc, are you also closing Word at the same time?

Reply | Quote

Thanks for the further suggestions. I tried

Reply | Quote

Found the solution. Boy is it obtuse!

Using the line below causes another hidden instance of the Word object to start.
appWd.ActiveDocument.PageSetup TopMargin = CentimetersToPoints(2)
This hidden instance is not closed by
appWd.Quit
Set appWd = nothing
The solution:-
appWd.ActiveDocument.PageSetup TopMargin = appWd.CentimetersToPoints(2)

Thanks all and a happy new year

Reply | Quote

Peter,
I have spent some time solving your problem, and now find that you have managed your self. Congratulatins!
I decided to post this anyway, since some of it might have your interest.

Regards,
Claus Parkhoi

Here it is:
I have done some changes to your code. The use of CentimetersToPoints was the
cause of your trouble. I have changed all refs to: appWd.CentimetersToPoints(x).
Furthermore I have made changes to attempt to use a running Wordapp by using
Getobject instead of Createobject. If Word is not running, Err_Tryword will
create the object for you and resume. This makes your function IsWordRunning
redundant.
I have tested it. It works. Happy New Year!
PS. Why don’t you put appWD declaration in the sub – I would have.

Public appWD As Word.Application ‘In the real code this is actually declared elsewhere

Sub TryWord()
Dim StrToInsert As String, FileName As String, Msg As String
Dim blnWordWasRunning As Boolean
Dim FlDt As Date
On Error GoTo Err_TryWord
‘ ** remove blnWordWasRunning = IsWordRunning
‘ ** remove Set appWD = CreateObject(“Word.Application”)
‘ ** remove blnWordWasRunning = IsWordRunning
‘ insert next line
Set appWD = GetObject(, “Word.Application”) ‘ Use existing app, if any
FileName = “C:AAATemp.rtf”
With appWD
.Application.Visible = False
.Documents.Add DocumentType:=wdNewBlankDocument
With .Selection
.Font.Name = “Arial”
.Font.Size = 10
StrToInsert = “Hope This works”
.InsertAfter StrToInsert
End With ‘.Selection
With .ActiveDocument.PageSetup
.Orientation = wdOrientLandscape
.TopMargin = appWD.CentimetersToPoints(2)
.BottomMargin = appWD.CentimetersToPoints(1.5)
.LeftMargin = appWD.CentimetersToPoints(2)
End With ‘ActiveDocument.PageSetup
.Selection.ParagraphFormat.TabStops.ClearAll
.ActiveDocument.DefaultTabStop = appWD.CentimetersToPoints(1.27)
.Selection.ParagraphFormat.TabStops.Add Position:=appWD.CentimetersToPoints(2), _
Alignment:=wdAlignTabLeft, Leader:=wdTabLeaderSpaces
On Error Resume Next
FlDt = FileDateTime(FileName)
If Err.Number = 0 Then
On Error GoTo Err_TryWord
Msg = FileName & vbCrLf & “already exists.” & vbCrLf & _
“Overwrite – [Yes] or save as Temp.rtf – [No}”
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
FileName = “C:AAATemp2.rtf”
End If
End If
On Error GoTo Err_TryWord
.ActiveDocument.SaveAs FileName:=FileName, FileFormat:=wdFormatRTF
.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
‘ ** you might remove the next line, since calling again
.Quit
End With ‘appWD
Set appWD = Nothing
FollowHyperlink FileName
Exit_TryWord:
‘ ** remove the following lines
‘If blnWordWasRunning = False Then ‘| If word wasn’t already running
‘Do ‘| I

Reply | Quote

Thanks Claus, I really appreciate your input.
Have a great year. Regards Peter

Reply | Quote