Need help with an Auto parsing Macro from Excel to Word

[jackdaniels]

Ok, all here is a real challenge for you. (Or maybe not, I am a layman).

Some background first. I am a business manager in Illinois and have 5 teams underneath me. Each team is required to send a daily excel spreadsheet of work completed for their clients to me (lots of random data) and also, from that excel sheet, send a more detailed (i.e. "prettier") version of the data in a word document. I then have to take all of this pieces of information and manually copy and paste the stuff I need into my company's word template. I have tried to get the company to move away from this level of paperwork, to no avail. Now I am seeing if a little programming can go a long way.

I hope that made sense. Ok, so I have written some extremely basic VB code to pull some of the excel data I need, convert it into the format the company likes to see, then send it to word. Therein I copy that data and paste it into my final word template. Thats the most I am able to accomplish at this level of my experience.

I was wondering if there was a way to "auto parse" data from excel and word and "auto fill" it into predetermined sections of my final word template. Basically I am looking for guidance on building macros I can add into both a blank excel and blank .docx file, then once I get my teams reports, copy those files into my "macroed" blank files, and hit run. The macros would search for specific strings of information, pull them out and put them into areas of the final template that they should go.

If this were actually possible I would be extremely grateful. A case of beer would immediately gifted.

[Don]

Hi and welcome to the forum!

It looks like you've been able to get the data from Excel using a macro but are stuck on trying to get the macro to automatically insert it into Word, is that correct?

If so, here are some tips.

First, in order to manipulate items in Word from a macro in Excel, you need to enable the object library for Word. Follow these steps to enable it:

1. Alt + F11 to go to the VBA window.
2. Go to the Tools menu > click References
3. Scroll to where it says “Microsoft Word XX.0 Object Library” (XX will be replaced with the version number for your computer – i.e. 10, 11, 12, etc.) and select this option.
4. Hit the OK button.

This will allow the Excel macro to 'speak' with Word. Now you just need to have somewhere to input the data into Word.

For this, you need to insert fields into the Word document. Then, you can add data to those fields using the macro from Excel like this:

Code: Select all

sub Test()

Dim appWord As Word.Application
Dim doc As Word.Document

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

Set doc = appWord.Documents.Open("C:\your file.docx", , True)

doc.FormFields("Field Name Here").Result = VariableName

end sub


The VariableName will contain whatever data from Excel you want to input into Word and the "Field Name Here" should be the name of the field where you want to input the data.



If you need an entire system built for you from the ground up, you can always use the professional Excel consulting services here: http://www.teachexcel.com/consulting/

However, this should get you started, and actually most of the way there, in regards to using a macro from Excel to input data into Word.

hope this helps

[jackdaniels]

Don,

This is outstanding, thank you very much. I just ran a test with a couple quick made up excel and word documents and it seems to be the exact thing I was looking for! Much, much, much appreciated!

[Sisyphus]

Hi,

I hate to pour cold water on the euphoria but like to point at my bleak experience with Word's FormFields. To cut it very short, they aren't suitable for reports. Use them to fill forms: Name, DoB, Position_Applied_For - that sort of thing. The trouble comes from locking and unlocking the form (in many ways it's no longer a "document"). I have given up using FormFields and now prefer Tables or Bookmarks.

Bookmarks work wonderfully if you have a fresh form (template) for every report.

Code: Select all

ActiveDocument.Bookmarks("AnyName").Range.Text = VariableName


Since you are starting to program you are, however, more likely to use the Selection rather than the Range object. The Selection object is easier and shorter to manage: Goto Bookmark / Selection.Text = VariableName. But the Bookmark gets lost in the process. So, you can't find it again if you need it. Choose: Longer code (Range object) or lost Bookmark (Selection.Object). But you can name your Excel cells to match the Bookmarks, meaning you can easily loop the transfer of data, regardless of how you address your Bookmarks.

A table's cells never get lost. You address them as Tables(1).Row(1).Column(1) or Tables(1).Cell(1,1) and that works like a dream until you merge cells in the table. Building loops involves Enums (the best way of naming row and column numbers). Take the trouble to learn about them. They are extremely helpful. You can hide the grid lines. So, the reader never knows that there is a table arranging the data in yur report. The advantage of a table vis-a-vis a bookmark is that you can easily add rows after your macro has done its job and you want to add some things that haven't been automated yet. Doing that on a Form with FormFields can be a nightmare. If you didn't know that all FormFields are reset when you un- and re-lock the form you will find out at this point.

What it all burns down to is template design. Design your Excel and Word templates in tandem.
Good luck!