Same Error Cropping Up During Transfer To Word

  • I've been tasked with writing a "screen designer" tool in Excel to help alleviate the tedium of filling in form after form by our clients.


    All was going swimmingly until I started on the great idea to have it transfer all the data from the client's design changes (approx 20 worksheets involved) into a large report in Word. This report would then be given to the design team to help generate the website a lot quicker.


    The code seems to work in that variables stored on a worksheet called 'Results' are transferred to the Word document via Word bookmarks. Ignore the one copying a range of cells (unless you can fix that one) as it's a recent addition to the code and I know it doesn't work as of yet (Errors #13 or #1004).


    The same error keeps on cropping up:


    "Error 462" - it'll work the first time but will fail every single time thereafter. I've looked up various solutions in Google and they all say that I should be making sure I close the Word Object once I've finished with it. This I've made sure I'm doing.


    My code is taken from various sources (the three books on VBA I've bought in the last month plus this site and a few others found by Googling):



    As you can see the error handler at the end gives me a heads-up if any error occurs but regardless of which outcome (error or not), the program still works on the first run and crashes every other time after. I'm "closing down" Word correctly - but guess Word & Excel don't realise that.


    Bound to be something simple I've overlooked but what is it?


    Many thanks


    Mike

    I used to use GFA BASIC a lot on my old Atari ST (and still do!) and thought VBA would be just as easy..... guess I was wrong :yikes:

  • Re: Same Error Cropping Up During Transfer To Word


    Mike


    So where is this error occurring?


    By the way I wouldn't really recommend this sort of syntax.

    Code
    Range("Results!B5").Value

    Boo!:yikes:

  • Re: Same Error Cropping Up During Transfer To Word


    Dunno why it's been moved to the Word area when it's an Excel query too but never mind, won't rock the boat :)


    The error only occurs on the second run of the code. First time it works fine and sends the data through to the bookmarks in the Word template. But, as I am testing this routine each time I add a new part to it, it will crash time and time again with the same error #462.


    I read somewhere after a lengthy Google search that everything I "switch on" when sending stuff to Word, I must switch off. Which is what I am doing in the last few lines of code - the exit code is the same but one just gives me the error code.


    It's being developed on Excel 2000 with Word 2000 as the destination - if that matters?[hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from norie

    Mike


    By the way I wouldn't really recommend this sort of syntax.

    Code
    Range("Results!B5").Value


    Oh .. just copied and pasted it from some website :)

    I used to use GFA BASIC a lot on my old Atari ST (and still do!) and thought VBA would be just as easy..... guess I was wrong :yikes:

  • Re: Same Error Cropping Up During Transfer To Word


    You've still not told us where the error is.


    Kind of hard to help without that.


    It might also help if you told us the error message and the application eg Word/Excel that is raising it.:)

    Boo!:yikes:

  • Re: Same Error Cropping Up During Transfer To Word


    Quote from muguk

    Dunno why it's been moved to the Word area when it's an Excel query ...


    The Excel forum is for issues related solely to Excel, rather than integration with other apps -- Word, PowerPoint, ... . Now that yunno, please post accordingly.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Same Error Cropping Up During Transfer To Word


    Right .. back after a nice long weekend due to a Bank Holiday and I'm sat staring at the code.


    I've REMmed out the On Error Goto Finally so that I can find out where exactly it crashes when you run it for the 2nd time (as mentioned above - it works perfectly well on the first run).


    The error I get is #462 (server not responding) and it occurs on the first occurence of the "Set Doc = " line.


    Code
    ' Ask Word to create a new document based on the template
        objWord.Documents.Add template 
         
        Set doc = ActiveDocument


    Now as I'm setting this to Nothing when I leave the routine, I assumed I was doing it by the book or website - as it's based on some code from one or the other.


    Looking under Task Manager and sometimes it shows WinWord.EXE as a process and sometimes it doesn't. So I guess I'm not switching it off properly but I seem to be doing the same 'switch off's as other example code out there.


    Also found that if I remove the "Set Doc = Nothing" lines from my error trapping, Word appears then disappears and nothing comes up prompting me to save the newly created file.


    So I'm stuck ..

    I used to use GFA BASIC a lot on my old Atari ST (and still do!) and thought VBA would be just as easy..... guess I was wrong :yikes:

  • Re: Same Error Cropping Up During Transfer To Word


    Last time I looked there was no such object as ActiveDocument in Excel VBA.:)


    That is where the code is running isn't it?

    Code
    Set doc = objWord.Documents.Add(template)

    Boo!:yikes:

  • Re: Same Error Cropping Up During Transfer To Word


    Oh right .. I'll go and find the book/website/forum I got the code from and paste it here (once I've double-checked I've not made any typos!).


    GFA wasn't anywhere near this hard to pick up :)[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Not found the website yet but have found a similar query on another forum which, as part of it's solution, is doing the same thing I am - click here


    I seem to be doing the same stuff as this other person. Everything I switch on is then switched off via the Set xyz = Nothing command. The example code on the linked page also uses the 'ActiveDocument' command.


    I've asked one of the "proper" developers in work to see what they can find out is wrong with me code - but I'm left each time with the "it works once but never again after that" condundrum.

    I used to use GFA BASIC a lot on my old Atari ST (and still do!) and thought VBA would be just as easy..... guess I was wrong :yikes:

  • Re: Same Error Cropping Up During Transfer To Word


    With help from one of the busy developers in work I managed to get this working by use of the Activate command which I wasn't using in the code above.


    The template is now opened, the data is sent through to the various bookmarks and, when it's finished, it saves off the template as a normal .DOC file using a filename based on the client name, their website and today's date & time.


    If I'm allowed to post up the full code, as it's an almost "official" project in work, then I will do so.


    Cheers for the pointers received and it's still using "bad code" for setting those bookmarks :)

    I used to use GFA BASIC a lot on my old Atari ST (and still do!) and thought VBA would be just as easy..... guess I was wrong :yikes:

Participate now!

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