Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automation only works once (Access 2000)

    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,
    Regards,
    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automation only works once (Access 2000)

    You're destroying the object variable but you're leaving the application process itself open. Take a look at <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=vb&Number=94889&page=&view= &sb=&o=&vc=1>this thread</A> and see if that information helps.
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation only works once (Access 2000)

    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
    Regards,
    Peter

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automation only works once (Access 2000)

    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.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automation only works once (Access 2000)

    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?
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation only works once (Access 2000)

    Thanks for the further suggestions. I tried
    Regards,
    Peter

  7. #7
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation only works once (Access 2000)

    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
    Regards,
    Peter

  8. #8
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation only works once (Access 2000)

    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

  9. #9
    5 Star Lounger
    Join Date
    May 2001
    Location
    Patterson Lakes, Melbourne, Victoria, Australia
    Posts
    636
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation only works once (Access 2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •