how to copy Excel Text Box into Word

[loud_scream]

Hi, i'm trying to automate my report generation which has to be in a Word Document. Because all work is done in Excel my idea is build a report based in a word template. In order to everything be in correct form in the word document i am trying to copy text from a text box in Excel into a text box in word. I came out with the following code but i cannot understand why is not working:

Please Login or Register  to view this content.

When i run this code i allways get an error like this: "Invalid Access to memory location". Can anyone help?

[loud_scream]

Anyone can help me?

[snb]

You can reduce the macro to:

Please Login or Register  to view this content.

[loud_scream]

Thanks for helping. I haven't tried your code yet but doesn't it basically create a text box in a Word document? In my case the word template already has all text box needed. I just want fill them automatically with information from an excel file.

[snb]

Do not use Textboxes in Word.
To insert text in a Worddocument from outside Word use documentvariables (and surely no bookmarks !).

[Kenneth Hobson]

I would use a bookmark or formfield myself. If you post two very short example files, it would be easier to help as there are several different kinds of textboxes in each application.

[loud_scream]

Thanks for all the help so far!

I cannot send to you the original files because they have enterprise information. Therefore, i created a word file and excel file with exactly the same kind of text boxes i'm using.

Has i told before my objective is to be able to transpose the text from excel (which will always be inserted in a text box) to a Word document in a specific location. In this example, i want it to appear under the title conclusions.

My initial idea was of using text boxes for this purpose, but you mentioned the using of bookmarks which i haven't thought before. Do they allow me to set a specific location in the document to paste the text? taking into account the code i sent to you before (it is in the excel example) how could i use it?

Thanks in advance

[loud_scream]

Sorry forgot the files

[snb]

Do not use bookmarks, but documentvariables. (Bookmarks are the Userinterface replacements for the more sophisticated documentvariables, that can't be changed in the userinterface).
You can insert them where you like, as often as you like and format as you like.
You can insert them: Menubar/Insert/fields/docvariable.

Please Login or Register  to view this content.

[Colin Legg]

According to Microsoft, when you're automating Office applications, CreateObject should only ever be called to create an instance of the Application and not (in this case) the Word document. I recall reading a fairly thorough, formal piece of documentation on this, but I can't find it at the moment. I did a quick google and the most relevant article I could find was here:
http://support.microsoft.com/kb/209892/j

The CreateObject function should only create the server object, not the document.

Whilst the symptom may not be a concern on this thread, the generic advice might be.


Have you ever had any problems with Createobject("Word.document"), snb?

[Kenneth Hobson]

See the thread link below for links that should help you. The thread even has a recent example that I did using bookmarks. Ideally, one might name bookmarks with a prefix and a range name with the same prefix and iterate to fill.

http://www.vbaexpress.com/forum/showthread.php?t=38552

[snb]

@Colin

I wouldn't call the invisibility of the Word document a problem, rather beneficial. It speeds up the macro considerably and leaves open the choice to make the document visible after all the automatic tasks have been fulfilled. I explicitly use VBA to let things being done without the user noticing (in Word by using 'application.visible=false). To add '.application.visible=true' if necessary doesn't seem to be a burden to me.

[Colin Legg]

Hi snb,

I agree with you that the visibility itself is not an issue here - but, as I said in my post, whilst the symptom on that article is not a concern, the advice is. If I can find that other article/documentation I mentioned, I'll put a link on here.

The question I'm raising is that MS has categorically stated that CreateObject should not be used in this way and, since it looks like you do, I was wondering if you had experienced any negative side effects such as bugs/errors/strange behaviour? I've always played it safe and avoided it, so I'm interested if you've ever noticed any problems...

That said, I can't find the darn article anywhere, so maybe I'm making it up.

[loud_scream]

Hi Colin Legg,

i've been using macros in excel to create word files with no problems at all. So i think that they are pretty safe and useful. Nonetheless i'm no expert at all. In fact this project i am doing has a part of documenting all the work in Lotus notes and, from what i saw from other user in the forum it is possible to. (Just think inefficiency when you actually do all the work but your output is a Word file and a lotus notes database. It takes you 50% of your time doing a routine work that is just passing information from one file to the other and organizing it).

snb thanks for the tip, i will try your suggestion and use a Word document variables. But i am new at this things, so, how do i call the document variable in the excel macro. Take into account that my macro already copies text from a text box into the clipboard, but how do i say to excel to paste that values into a specific word variable? do you have any example of the code? (if you could use my code would be grate)

Once again thanks for all the help

[Colin Legg]

I've been using macros in excel to create word files with no problems at all. So i think that they are pretty safe and useful.

Sorry, you misunderstand me - that's not the question. VBA can certainly be used to create word files - we're talking more specifically about the method employed, ie.

Please Login or Register  to view this content.

As opposed to:

Please Login or Register  to view this content.

I hope you don't mind - it's your thread, and this is just a parallel plot so don't let it distract....

[Kenneth Hobson]

Basically, do it this way:

Please Login or Register  to view this content.

I left the MSWord file open after I updated the textbox. Here is how I set the WD document object to test using your example files:

Please Login or Register  to view this content.

[Colin Legg]

Here's another link on the topic which confirms what I was saying earlier:

It is best create an instance by using the Application ProgID, and then open or create new objects from there. Other ProgIDs, such as Excel.Sheet and Word.Document, and so forth, are intended for use in OLE (Object linking and Embedding) and may give inconsistent results when used with CreateObject. By using the Application ProgID, you avoid potential issues by explicitly starting the server for Automation (not Embedding).

My advice is to play it safe and follow Microsoft's advice...

[snb]

To illustrate the methods I would use: see the attachments.
Save the attachments on your computer in the same folder.
Open the Excelfile, click the commandbutton.
After having done this you will find in that same folder a new Worddocument __example_001.doc, in which the values from the Textboxes in Excel will be found in different places, with different formats.
The file __example.doc will be unaltered, so it can serve as a template (without any need to make it a .dot file).

@Colin

Thanks for pointing that out to me. I never came across that, nor to the need to investigate, because the method never failed. I find MS's 'warning' very a-specific (when, why, what). It looks rather peculiar to warn for a method they introduced in their program themselves and which they could have deleted (as they did with so many other useful things) in a newer version.

[Colin Legg]

Hi snb,

I never came across that, nor to the need to investigate, because the method never failed.

That's interesting to know, especially since I'm under the impression that you've used it a fair amount. Thanks for the information.

I find MS's 'warning' very a-specific (when, why, what)....

It would certainly be better for us if MS gave some specific examples, rather than generalising with terms such as "inconsistent results", because then we could better understand the function and the true implications of using it either way.