[Solved] VBA: Using Word from Excel

    Evening All,


    I'm using Excel to print out documents in Word. Each time, I create an object wordApp and an object wordDoc. wordApp is new each time and wordDoc is one of a number of templates. The wordDoc bit is not a problem as they are closed each time when their purpose has been served. If the program runs to completion there's no problem but . . . .


    I now want the ability to interrupt, edit the word doc and then continue. Getting tricky coz I end up with far too many instances of Word running (I don't want to bother users with having to close the app).


    So, instead of creating a new version of wordApp each time, is there a way of finding an existing instance running on the PC, hi-jack it, use it for my nefarious ends then release it back to whatever the user wants to do with it.


    Hope this makes sense, if not, ask and I'll write a novel about it.


    John


    PS: Apologies to GCHQ for wasting your time, only used the term hi-jack 'coz I couldn't think of another way of putting it.

    Hi John,


    If you are using CreateObject then try modifying your code to use GetObject.

    Quote

    Use CreateObject when there is no current instance of the object. If an instance of the object is already running, a new instance is started, and an object of the specified type is created. To use the current instance, or to start the application and have it load a file, use the GetObject function.


    Cheers
    Andy

    [h4]Cheers
    Andy    [/h4]

    Hi Andy,


    I’m currently using


    Set wordApp = GetObject("Word.Application")
    Set wordDoc = wordApp.Documents.Open(FileName:="~~~CancelMemo.dot")


    This works but as per my original post, it will leave instances of word lying around all over the place if I interrupt the process.


    If I change the CreateObject to GetObject, I get an error message “File name or class name not found during Automation operation”


    Any thoughts?


    From my point of view, is there a test to see if Word is already running, if I can get that to work, it might lead me a bit further.


    John

    Hi John,


    Play around with following sample-code:


    Option Explicit


    Sub test()
    Dim WordApp As Object


    On Error GoTo Error_Handling
    Set WordApp = GetObject("Word.Application")


    NextStep:
    'Your code here


    ExitHere:
    Set WordApp = Nothing
    Exit Sub


    Error_Handling:
    If Err.Number < 0 Then
    Set WordApp = CreateObject("Word.Application")
    Resume NextStep
    Else
    MsgBox Err.Number & " " & Err.Description, vbInformation
    Resume ExitHere
    End If
    End Sub



    Kind regards,
    Dennis

    Hi Dennis,


    Thanks for that . . . . . but


    I get the same error. Stepping through your code, it doesn't recognise the "Set WordApp = GetObject("Word.Application") " and throws the same error.


    Is there an object library involved?

    Confused of Yorkshire.

    Hi John,


    The GetObject takes two arguments, the first is the path and file name. The second is the class.
    So in order to use GetObject with a class the add a comma.


    Set WordApp = GetObject(, "Word.Application")


    Cheers
    Andy

    [h4]Cheers
    Andy    [/h4]

    Okay, I used ,"word.application" and I am now getting the error message
    439 ActiveX component can't create object. This is when Word is not running on my PC.


    When word is running, It works and does not create any excess versions.


    Now, using Dennis's code, it enters the Error handling routine so I'll fiddle there and let you know.


    John

    Ha haaaa,


    It works !


    Thanks to both of you for all your help.


    I've got another unconnected query but I'll surf a bit and see if I can solve it myself before starting another post.


    Again, huge thanks.


    John

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login