Solved: Building document from Excel

[jwise]

I need to build multiple documents (one for each property) that basically have two areas in them. The first is what I will call a header. This header identifies the property and the year in review (provided via bookmarks). Then I will have multiples of the second area (and the number varies). I know that 4 of these will fit on one page. Each of these areas has 8 bookmarks (the numbers again come from the spreadsheet). The length of the document will vary based on the number of data rows for each property. I previously built documents from Excel using a template, but a fixed template does not handle this. Since I don't know how many pages are necessary, I think I need two templates (one for each "area").

Could someone give me some insight on how to start? I'm having trouble finding anything in the help files. I believe this is a relatively common task. TIA

[Oorang]

Sounds like you want to build template. Just make an excel file that looks how you want it to, then save it to the templates folder. Close it, and when you go to insert a new worksheet or start a new workbook it will be listed in your templates.

[gwkenny]

An adequate definition of the problem helps.

Pictures would be ideal.

"I need to build multiple documents (one for each property) that basically have two areas in them."

Okay, you need to build separate documents for each property.

"The first is what I will call a header. This header identifies the property and the year in review (provided via bookmarks)."

It is really confusing when you use Word terminology and define it as something else. I can only assume this is not a Word header, but maybe something akin to a "Table Header".

"Then I will have multiples of the second area (and the number varies). I know that 4 of these will fit on one page. Each of these areas has 8 bookmarks (the numbers again come from the spreadsheet)."

Okay, in this second area, you can have up to a multiple of four. Each area has 8 bookmarks (how??? This is coming from Excel no?), so you can have 32 more bookmarks.

"The length of the document will vary based on the number of data rows for each property. I previously built documents from Excel using a template, but a fixed template does not handle this. Since I don't know how many pages are necessary, I think I need two templates (one for each "area")"

Why would you need two templates? From what I understand (and that's not too much) you can put your "header" in the first row of a table and have it repeat across pages. You can have each separate "second area" be formatted "Keep with next" so you will never have a page break across an individual "second area". Then it doesn't matter if you have one "second area" or fifteen.

"Could someone give me some insight on how to start? I'm having trouble finding anything in the help files. I believe this is a relatively common task."

Don't think anyone can help you without a better description of the task. Though lately, I've been misreading posts and I sure could have misread yours

:)

[jwise]

Thanks for the replies. I apologize for the nebulous problem definition. I will attempt a more lucid description.

For each property, I will build a Word document. You may think of the document as being composed of two parts: a single header and multiple descriptions (this is not Word terminology). This header will have three bookmarks in it, and I will place the property identification data in it. The number of descriptions necessary is determined by the number of rows of data, and is variable. In a row are certain numbers that I will also pull out and place in this description. I can do this by bookmarks as well.

What I don't understand (and is different from previous Excel/Word project) is how you build a document when it seems that I need two templates: one for the header, and a second for each description. Since the number of rows vary, I can't build a single template that will match every case. I guess the real question is "Can I build a document that uses multiple templates?" Also, assuming I can do this, and since the multiple description templates will contain the same name bookmarks, will there be a "bookmark name" problem? Let's assume that this description has three bookmarks named mark1, mark2, and mark3. When I fill in the first description, everything is OK since there is only one mark1. When I "insert" or whatever I do to put the description template in the document again, now I have two of each of the bookmarks. If I reference mark1, will I get the second?

I apologize in advance if you can not understand my restated question.

[fumei]

Heck, I do not know what you mean by "property".

Are you talking about real estate?

There seems to be confusion between identification of location (ie. bookmarks) and data.

"when it seems that I need two templates: one for the header, and a second for each description. Since the number of rows vary, I can't build a single template that will match every case. "

My bolding.

I do not see why not. This seems to be a question of logic.

If you have X chunk of data, do THIS.

If you have Y chunk of data, do THAT.

" I guess the real question is "Can I build a document that uses multiple templates?" "

Technically, the answer to that is...sort of. Not REALLY, but you certainly can use multiple content.

However, I am not getting what the issue really is.

Yes, using bookmarks can be tricky if you start messing with names, but ONLY if you try doing copying. You can not copy bookmark names, nor can you change them.

Unless I am totally off to lunch (possible) I am not understanding what the problem is. Can you be very explicit and describe it? Exactly?

These "descriptions"...are they text in an Excel file? I do not understand where they are coming from.

[gwkenny]

OP:

First: Use correct terminology. Do not redefine "Header" and expect everyone to follow. It just gets more confusing. I can't go into a finance forum and redefine compounded annual interest as simple interest and expect people to follow me.

Just use a different term. It's not hard and will clear a lot of confusion. So now we have Property Definition area (Name of the property and year in review). And we have a Property Description area.

Each Property Definition can have several Property Descriptions.

I'm going to make some HUGE ASSuMEptions now. Please let me know if this is correct:

You use bookmarks in your templates as location markers where you post the data from Excel.

The problem comes in when you have a second description for a property. You've already used the bookmarks for description cause that's where you put the data for the first description.

Is this making sense to you? Are my assumptions correct?

If so, you do NOT need 2 templates or more. You need one template but you need to organize your template in such a way that you do NOT use bookmarks as placeholders for where you place text. Or they may be STARTING points. Key is to format the data that if you have more than one description, you can just keep inserting them one after another and they will be formatted correctly.

In general, I'd use tables. Each row would contain one description. But you have not given us your format for your output so this may or may not work for you. You can also use and apply styles (which you should do if your using tables as well).

But however your reporting format is, it can be done. Just can't help you cause you have yet to clearly define the problem :(

I'll reiterate again: Pictures/samples will be ideal. Data in Excel and final output.

[jwise]

I apologize again for the confusion in the terminology. Your assumptions are correct.

I did solve my problem by finding some code that deleted all bookmarks in the document. Then I insert the template again, and add the next rows' data to it. My understanding was that when you inserted the text that the bookmark was "replaced", i.e. destroyed. This is wrong. Since I insert text for every bookmark in the template every time, I would not have had a problem if this had been the case.

I do not understand how to do this without bookmarks, and I certainly do not understand how to do it with just one template. I have seen "InsertText" in Word, but the problem is "How do you locate where to insert the text?" This is the purpose of a bookmark. A very complicating factor is that the number of rows can vary for each piece of real estate, thus the finished document will vary in size. It seems to me that a single template would work only in the case where the number of rows of data are the same for each piece of real estate.

The problem may get more complicated as I have been told that I may need to insert unique text for each row description. Thus I would have a template for each possible description. This would be a maintenance nightmare as these descriptions would probably change over time.

I also attempted to solve this problem by using a "Word" (as opposed to Excel) RANGE to limit the search for the bookmark. I got the "character count" before I inserted the template, and then again after I inserted it. Although I had some problems getting this to work, I received no errors but all upadtes still went against the very first "data" template I inserted. I have no idea why this doesn't work.

Thanks again for the assistance.

[fumei]

OK, can you clarify one thing?

Is this to create ONE document per action? Are you using the template to make a document for one Property? Or is the template used to make one document, with multiple properties?

Please answer the querstion regarding where the text is coming from?

I am really not following this, as on the surface it seems simple.

BookmarkA....put text

BookmarkB....put text

What the text is - one line, forty lines - it does not matter.

"The problem may get more complicated as I have been told that I may need to insert unique text for each row description. Thus I would have a template for each possible description. "

No, no, no. This is to be avoided at all costs. There is no need. Descriptions, and I do not care what they are, are just text. Text is text. If you need to, you have a separate file that holds that text. A separate file removes any issue with maintainence, as you simply maintain THAT file.

Description file:

Description_A
Description_B
Description_C
Description_D
.....etc. etc.

These are themselves bookmarked.

Say a new document needs Description_B, Description_F, Description_GZ.Dim ThisDoc As Document
Dim DescriptionDoc As Document
Set ThisDoc = ActiveDocument
Set DescriptionDoc = Documents.Open Filename:="yaddayadda\Descriptions.doc"
ThisDoc.Bookmarks("DumpHere").Range.InsertAfter _
Text:=DescriptionDoc.Bookmarks("Description_B").Range.Text _
& vbCrLf & _
DescriptionDoc.Bookmarks("Description_F").Range.Text _
& vbCrLf & _
DescriptionDoc.Bookmarks("Description_GZ").Range.Textwould get the bookmarked descriptions ("B", "F", "GZ") and insert their text at the bookmark (in the active document) "DumpHere"...each separated by a paragraph mark.

It would be exactly the same logic if you need one description text...or 1,000.

I am not seeing the problem, at least with making ONE document for each Property. One template can handle variable situations easily. A template can make a document with PropertyA with 3 descriptions...then turn around and make a document with 17 descriptions.

Now, if you are trying to create multiple Properties in one document...then please...say so. It is rather an important point. However, it seems that this is not the case, as you wrote:

"I need to build multiple documents (one for each property) "

My bolding.

[jwise]

The macro produces multiple Word documents, one for each piece of real estate (in fact apartment complexes). The descriptions are line item descriptions of expenditures and incomes, for example the money spent on utilities, office supplies, painting and decorating, etc. The number of these descriptions vary. The spreadsheet analyzes these costs and incomes, and then I produce a Word document that provides the relevant information plus comparative information (how this complex compared to other complexes) for each complex. This Word document will be sent to the management.

My "data" template does not describe the "description" field the same as the manually produced document from prior years because I believed that the "description" itself was informative enough. Management is now considering this.

Each data row contains this descriptive text. The "data" template has a bookmark for this description, and other bookmarks for the relevant data (this complex's expenditure, its rank among complexes, the average expenditure, etc.). By using a document template (".dot") as the inserted text in Word, I was able to put all the data into the document. I was not sure how many descriptions would be used as we actually have around 120 of them, but we are only using about 20 (today!). The management changes its mind about which descriptions are important as business conditions change, so this is not whimsical.

The document does not "read" like the former manually produced document. But I could make that happen because the same variable data is in both. Instead of one template for all descriptions, I would need a template for each description.

Perhaps your sample code will keep me from having to build all the separate templates. I have not seen this done (I've only written a couple of Word macros, but many Excel Macros) so it is educational in the least and gives me another way to handle this situation.

Thanks again for the input.

[gwkenny]

Gerry:

My understanding (and that ain't saying much lol) is that the OP is indeed generating separate Word documents for each property. He has a procedure for this.

His problem is that each property can have several descriptions, and he's using Bookmarks as placeholders for the descriptions. The problem is that a property can have multiple descriptions so when he loops through his descriptions for that property, the description he is pasting is replacing the description he pasted previously.

OP:

You are making this unnecessarily difficult. Bookmarks are just placeholders in the document. You can also use plain text in your template as a placeholder.

Your Logic should be as follows:

1) New Property? Yes, Create New document...

2) Insert Property Definition (Property Name, Year in Review, etc...) and format accordingly

3) Select the bookmark for the starting point of Inserting Property Descriptions.

4) Insert the first Property Description information. Format accordingly.

5) Is this the last Property Description? If Yes, go back to step 1 and rinse and repeat.

If this is NOT the last property description then...

6) Insert a paragraph or tab to create a new Table row (if you placed your description in a table).

7) Insert your NEXT description (do NOT repeat the search for the bookmark. Format accordingly. Go back to step 5 rinse and repeat.

OP: What you need to do is to look at the organization of your Description data so you can paste it to the Word document one after the other without worrying about how many descriptions you have.

There is no need for more than 1 template. I'm just saying the same thing Gerry has been saying, "insert ... text at the bookmark ...each separated by a paragraph mark."

I will reiterate AGAIN: PICTURES/SAMPLES WILL BE IDEAL. THE DATA IN EXCEL AND THE FINAL OUTPUT THAT YOU WANT IN WORD.

[fumei]

I give up. I have tried here, but am just feeling frustrated.

jwise, both gwkenny and I have asked for information but you do not answer, really. I don't know how I can say it more clearly.

It does not matter if there is one, or many, descriptions. They are text text text text text text text text. Word is a word-processor...which means it handles....text text text text text.... rather well.

"Instead of one template for all descriptions, I would need a template for each description. "

You have repeatedly stated this, and I have repeated stated, no you don't, as has gwkenny. I even gave a real world example of how you do not have to.

gwkenny has repeated asked for pictures/samples, and you do not even have the politeness to respond at all. Even saying - no, I can't do that (for whatever reason).


gwkenny - "My understanding (and that ain't saying much lol) is that the OP is indeed generating separate Word documents for each property. He has a procedure for this."

Maybe...maybe not.

1. the OP has shown not a single scrap of code, so...excuse me..how do you really know the OP has a procedure?

2. the OP wrote: "The macro produces multiple Word documents, " AND "The spreadsheet analyzes these costs and incomes, and then I produce a Word document"

The latter statement seems like one document, but the former is iffy. ONE macro producing multiple documents? Yes, that is possible. By using a template repeatedly, with EACH document freshly using a new clone from the template.

In any case, jwise, gwkenny seems willing to help you, and appears to know what is happening. So I will leave you with his assistance. I do not see that I can help any further.

[jwise]

I apologize again for my inability to get the problem across.

It is difficult to provide the data to illustrate this because the owners of this data might not approve it. That is part of the reason. GWKenny is correct in his restatement of my problem. Here is the loop that is processing the rows. This code works, but it does insert the same template for each row. After inserting the data using the bookmarks, I call a subroutine to delete all bookmarks. If there are additional rows, then I insert this same template again, and repeat.


After the major problem was solved by deleting the bookmarks after the data was placed in the template, I was informed that the "single" template was not considered as adequate as the former text. Previously, a Word document was manually edited and the data was typed into it. The base document was altered every time there was a "rows" change. The basic requirements are dictated by business considerations. Now I know I could include a separate template for each row (the template itself would contain different text but the same set of bookmarks). I just considered this as being a waste since the only real data in the text is what I have called the "description". I have not received management's decision yet.
For i = 3 To lastRow
count = count + 1

If count > 4 Then
wrdApp.Selection.InsertBreak Type:=wdPageBreak
Call RemoveAllBookmarks(wrdApp)
wrdApp.Selection.InsertFile ("C:\PUPA\HDR01.dot")
sNam = wsProp.Cells(1, thisCol)

With wrdApp.ActiveDocument.Range
.Bookmarks("sNam").Range.text = sNam
End With

count = 1
End If

Call RemoveAllBookmarks(wrdApp)

wrdApp.Selection.InsertFile ("C:\PUPA\DATA01.dot")

sCat = wsProp.Cells(i, 1)

If wsRank.Cells(i, thisCol) = "" Then
sRnk = "N/A"
Else
sRnk = Format(wsRank.Cells(i, thisCol), "##0")
End If

sCnt = Format(wsProp.Cells(i, lastCol), "##0")

If wsProp.Cells(i, thisCol) = "" Then
sExp = "N/A"
Else
sExp = Format(wsProp.Cells(i, thisCol), "$###,###,##0.00")
End If

sLbl = wsProp.Cells(i, lastCol - 4)
sMed = Format(wsProp.Cells(i, lastCol - 2), "###,###,##0.00")
sAvg = Format(wsProp.Cells(i, lastCol - 3), "###,###,##0.00")
sDev = Format(wsProp.Cells(i, lastCol - 1), "###,###,##0.00")

With wrdApp.ActiveDocument
.Bookmarks("sCat").Range.text = sCat
.Bookmarks("sRnk").Range.text = sRnk
.Bookmarks("sCnt").Range.text = sCnt
.Bookmarks("sLbl").Range.text = sLbl
.Bookmarks("sExp").Range.text = sExp
.Bookmarks("sMed").Range.text = sMed
.Bookmarks("sAvg").Range.text = sAvg
.Bookmarks("sDev").Range.text = sDev
End With

Next i

Previous attempts at this involved using RANGE (in Word) to limit the bookmark replacement to just the last inserted template. I could never get this to work. The "RemoveAllBookmarks" sub came from Allen Wyatt's website (with a slight mod to pass the appropriate document).

[fumei]

"I apologize again for my inability to get the problem across. "

NO! That is not the main issue. Try apologizing for not answering questions. I understand the problem - I think. Here is what I think your problem/objective is.

You want to create a Word document with multiple "main items" (the properties) which have multiple, and variable, amounts of other text "items".

Yes? If this is correct...and maybe I am wrong...then I will repeat myself. This is not hard, nor particularly difficult - it is not really a problem - and the solution is NOT to insert a template file, nor remove bookmarks.

Just to show what (I think) you are trying to achieve:

Property_A
data item
data item
data item

Property_B
data item
data item

Property_C
data item
data item
data item
data item
data item
data item


BTW: wrdApp.Selection.InsertFile ("C:\PUPA\DATA01.dot")

InsertFile... a dot file?????? This is an utter lack of understanding what a dot file is, and does.

[jwise]

You have the idea. The only thing I would add is that each piece of real estate is a separate document since we will send the documents to the responsible parties. The user only gets the information for real estate that he/she manages.

As to my not understanding bookmarks, I am not surprised. I have a lot of programming experience but very little OOP experience, and Windows is not my native platform. I am a displaced mainframer, and I have had major difficulties trying to find information. I understood bookmark to be analogous to the bookmarks I use to remember what page I'm on when I put down a book. It seemed pretty logical to use bmarks as placeholders for inserted text. To top it off, it worked. However, I have seen people do the strangest things because they lacked the proper information, so I'm not too surprised I've taken this wrong.

Here are a couple of problems you may know how to solve. On one particular line, there are three bookmarks for amounts which can vary hugely. What I'd like to do is have these three values displayed evenly, meaning as the numbers get larger, I have less space between them. This was a big problem on my other Word/Excel project (I only needed a single template for it so I never had these duplicate bmark problems). Another way to say this is that I'd like to have the first digit in each of these values in the same place every time. Now the second and third values move around depending on the length of the preceding values.

I'd also be very appreciative if you'd tell me what I should use instead of bookmarks to place these values in the document. Also consider that if I can keep this text outside the macro, then maintenance would be reduced in the future. A programmer would not be involved unless they changed something other than the associated text.

Thanks for the insight and your patience.

[gwkenny]

It is difficult to provide the data to illustrate this because the owners of this data might not approve it.

Please use some imagination and mock up some faux data. I doubt the structure of their reports are proprietary and/or confidential. We do not need to know the actual property name, rank, or the median, avg, or standard deviation statistics.

However we DO need to see how you've layed things out. I am unwilling to spend significants amounts of my time (unrewarded) on this cause I keep saying the same thing:

PICTURES/SAMPLES WILL BE IDEAL. THE DATA IN EXCEL AND THE FINAL OUTPUT THAT YOU WANT IN WORD.


I'd also be very appreciative if you'd tell me what I should use instead of bookmarks to place these values in the document.

Personally, I tend not to use Bookmarks unless the same info needs to be shown several places in the same document, then I'd use it for the REF field. As I said before, you can just use text. For example, "[sNam goes here]". Just search for it.


Another way to say this is that I'd like to have the first digit in each of these values in the same place every time. Now the second and third values move around depending on the length of the preceding values.

The two main ways (there are others) are left tab stops and tables.

This goes back to my sneaking feeling I've gotten from the beginning, which is this can all be done SIMPLY and EASILY with tables. Yet we are prevented from HELPING you because you have yet to adequately define the problem because we are getting the information piecemeal.

A huge part of the confusion would be clarified if we got pictures/samples. The data in Excel and the Final Output that you want in word.


I'd also be very appreciative if you'd tell me what I should use instead of bookmarks to place these values in the document. Also consider that if I can keep this text outside the macro, then maintenance would be reduced in the future.

What text outside the macro? What are you talking about? You mean the bookmark names?

You want to talk about maintenance expandability without additonal coding?

You have 2 Excel sheets. Each sheet is structured the same. 1st row is the Variable name with data in the subsequent rows. Sheet1 lists all your properties and all variable but DESCRIPTION.

Sheet 2 has the Property ID and Descriptions.

A) Your code goes to Sheet1. Reads in the first row of column 1. Starts a new Document based on your Property template. It then searches for that name in brackets in the Word document. For example, if A1 = SNAM, it will search for "[SNAM]" in the Word document. Then it will replace the found ("[SNAM]") text with the data in A2. If the text ("[SNAM]") is not found, the User will be informed and the macro stopped.

B) It will repeat that for all the columns for the first record. It will then go to Sheet2, and insert the descriptions one after another until the next record doesn't have the same Property ID.

Then you go back to A). Do it again, but for the data in the third row, etc...

Have a new field to add? Just go to the end of your data and add another column and put the name in Row 1 and the data underneath. Goto the Property template and enter the appropriate search expression where the data should go, "[VARIABLE NAME HERE]".

Thus the system can be maintained and even expanded without additional coding.

But yah know what? You can't do that with the way you have things now and I really don't know how to help you get to that point cause I really don't have an adequate description of the problem: Ergo the layout that the data is going into which I'm almost sure can be EASILY and SIMPLY done with tables but we don't have pictures/samples.

HINT HINT

[jwise]

I do appreciate the assistance.

My theory was that there was a lot of irrelevant stuff in the spreadsheet, and that made posting it rather pointless. It appears I'm wrong.

I am taking the 2MB workbook and downsizing it. I will post it later. Do be aware that my e-mail virus code (Norton) erroneously determines that the workbook has the "bloodhound" virus. This (according to their website) is a generic term for suspicious macros. I found this out by taking the code home, working on it, and e-mailing it back to myself. I must use a flash drive or USB drive to transport it to work. My AVG at home has no such problem. I do not know about other virus software.

[jwise]

The attached zip file contains the "ZZZ" folder. It was created with 7-Zip, but any compatible "zip" should work. You should unzip to a "ZZZ" folder on the "C" drive. If you elect to put it somewhere else, you will need to edit the macros ("WordRank" and "PrtCol") and look for file references, and update them appropriately. The folder contains two files (a detailed "readme" and the workbook(VBAX.xls), and two sub-folders. The "test" folder is empty; it is a place-holder for the Word documents. The "PUPA" folder contains the three Word template files.

Be aware that if you use Norton anti-virus software, it may report that the macros contain the "bloodhound" virus. This is a FALSE detection. Since this is source and there is no "event" code, you can look at the code BEFORE you attempt to run it.

The workbook contains may other macros. Some of those are failed attempts of this specific problem and other attempts at other objectives. Some are other code used at other stages of this process.

WordRank invokes "PrtCol" and a few other support subroutines, but they are the only routines involved in this problem. The objective is to replace the improper use of "templates" and "bookmarks" with something more appropriate.

Please see the "readme.txt" included for additional information. I appreciate your attention and patience.

[gwkenny]

This looks like a load of work cause of the extraneous stuff. I'll take a look at it when I get a chance over the next day or so.

[fumei]

Wow....

Almost unbelievable.

[jwise]

I have taken the workbook and deleted ALL non-necessary macros. If you do ALT-F8, the only file that will show up is WordRank. There are some other macros in the module, but they are subroutines to WordRank.

All previous information is still applicable. The only relevant procedures to this problem are WordRank and PrtCol. Both of these modules insert templates. I am assuming you are going to show me a better way to do this. The code does work as designed...

[fumei]

My very last post on this thread....

"Both of these modules insert templates. I am assuming you are going to show me a better way to do this. The code does work as designed..."

Maybe so, but inserting text by inserting templates is a very very bad use of Word. Templates are used for two things.

1. to hold code, as code containers. these are global templates.

2. to create a new document by cloning itself.

That is it. Templates should not be, are not designed to be, used for anything else.

In fact, your use of the word "design" is, IMO, way off kilter. Yes, your code may work, but I would fain to call it designed. Design means working out WHAT before HOW.

As in:

WHAT I need is that boilerplate readme text as the start of a document.

WHAT I need is a variable number of Category chunks inserted into the document.

WHAT I need is to be able to insert variable content into each category chunk independently.

I have mentioned, and gwkenny has mentioned, the way to do this. You are NOT LISTENING and I am just not going to bother with you anymore. I am going to offer some comments and advice, show some portions of code and walk away.

Let me make this very clear. I have just done this. I have rewritten your Excel code - with portions below - and made it work. You can take my comments and suggestions and do what you like. I don't care anymore.

Ready?

1. ALL of your Word document content can be worked from ONE template. This has been stated to you repeatedly. I did it. EVERYTHING needed to create documents with as many categories as you like can come from ONE template. A real template. In the working file I did, this is "GerryVBAX_Crap.dot"

2. You clearly do not understand the use of instances. Your code has:For i = 2 To lastCol - 6
Set wrdApp = New Word.Application
wrdApp.Visible = FalseDo you know what actually happens with this??? It creates a new instance of Word every iteration!!. This is not just poor programming, it is BAD programming. You only need ONE, repeat ONE, instance of Word.

3. Corollary to #2.wrdApp.ActiveDocument.Close
Set wrdDoc = Nothing
wrdApp.Quit SaveChanges:=False
Set wrdApp = Nothing
Set wrdDoc = NothingSetting something to Nothing twice? Very useful.

4. In fact, you use:Set wrdDoc = wrdApp.ActiveDocument...and then never USE wrdDoc for anything....except to make it = Nothing....twice. Very useful. In other words, why make wrdDoc = ActiveDocument, and then use:With wrdApp.ActiveDocument
Once Set, wrdDoc IS ActiveDocument


OK, enough of that. I will tell you (mostly) what I did.

a) your separate .dot files (do not DO this!!!) you use to insert content are AutoText entries.

b) as all your sample documents have the same readme type text at the beginning...why not just put the darn stuff there? So I did. It is an AutoText entry, and when GerryVBAX_Crap.dot is cloned, its Document_New event (which fires when a template is cloned) dumps it at the start of the new document, like this:Sub Document_New()
AttachedTemplate.AutoTextEntries("MyReadMe") _
.Insert Where:=Selection.Range, _
RichText:=True
End SubBoom...done, including the page break, as the page break IS part of the AutoText entry "MyReadMe". So, right off the bat, the new document has that readme type text.

No reading from another file, and certainly not inserting a template! You do not have to tell Word to do anything. Cloning the template itself makes it happen. Here is the starting code, and please notice I moved the creation of the New instance of Word outside the loop - see
#2 above.Set wrdApp = New Word.Application
wrdApp.Visible = False

For i = 2 To lastCol - 6
Set wrdDoc = wrdApp.Application.Documents _
.Add(Template:="c:\zzz\GerryVBAX_Crap.dot")
docName = ws.Cells(1, i)

sPro = Abbrev(Cells(1, i))
sYer = pupaYr

With wrdDoc
.Bookmarks("sPro").Range.text = sPro
.Bookmarks("sYer").Range.text = sYer
End WithThis makes a new document (.Add), and attaches the template GerryVBAX_Crap.dot to it. By attaching the template, that readme type text is automatically there, as it is in Document_New. The bookmarks are there, all ready to have text put in them.

badda-bing, badda-boom.

Next.

Your "Category" chunk is an AutoText and includes the bookmarks. Removing the bookmarks is still an OK idea, and is in fact required. So, here is part of your Sub PrtColSub PrtCol(wsProp As Worksheet, doc As Word.Document…yadda yadda

For i = 3 To lastRow
count = count + 1
Call RemoveAllBookmarks(wrdApp)
Select Case count
Case Is > 4
count = 1

‘ go to end, add page break
wrdApp.Selection.EndKey Unit:=wdStory
wrdApp.Selection.InsertBreak Type:=wdPageBreak

‘add silly “Continued” and fill bookmark
doc.AttachedTemplate.AutoTextEntries("MyContinue") _
.Insert Where:=Selection.Range, _
RichText:=True
wrdDoc.Bookmarks("sNam").Range.text = sNam

‘ go to end, add new Category
wrdApp.Selection.EndKey Unit:=wdStory
doc.AttachedTemplate.AutoTextEntries("Category") _
.Insert Where:=Selection.Range, _
RichText:=True

‘ add your data

Case Else ' count is < = 4
‘ go to end, add new Category
wrdApp.Selection.EndKey Unit:=wdStory
doc.AttachedTemplate.AutoTextEntries("Category") _
.Insert Where:=Selection.Range, _
RichText:=True

‘ add your data
End Select
Next i


I understand that you do not have an expert level of knowledge of Word. That is OK. I am not being critical of that. I am being critical of the fact that you seem to be not listening, AND you seem to be making no attempt to work with the basic structure of design logic. In fact, it seems to me that you want to persist in poor logic.

Hopefully you can use the above. Ta-ta.

[gwkenny]

OP: I'm going to quote your readme.txt and maybe some of your posts.


Since I am not a Word person, this is entirely possible, and thus I have submitted this code in the hope that those who are Word knowledgable would inform me.

Your understanding of Word seriously inhibits your abilities but you also keep stating things have to be "this way" when 2 folks who are pretty damn knowledgable about Word, its objects, and how to use them in conjunction with VBA keep saying different.

Gerry and I have been saying the same thing. You only need one template. Now having seen what you are working with, I can definitively say that all you need is one Word template. Gerry gave you one approach with Autotext. I probably would throw in tables too. But the fact remains you do not need separate templates. One will do everything you need.


There are over 120 possible line items ... This is why a single
template is a bad idea: every year (or cycle) this template
would need updating depending on business factors.

This does not negate the fact that one template will do everything you need. EACH of the 120 possible line items has the exact same format in the report. Thus one Template will suffice. Even if they did have different formats, I'm sure it can be done, but the fact they all have the same reporting format means this is easy.

Your create a new document based on your template. You insert the Property Definition information as required.

You get to the Line Item Description area. The additional information besides the variables are so small, you can even hard encode it (though I don't recommend it). But let's say you hard encode it, since that's very simple, you just keep adding the Line Item Descriptions to the document until you run out of Descriptions and go to the next property. Use the property FORMAT/PARAGRAPH/LINE AND PAGE BREAKS/ and Keep with Next property to make sure your pages don't break in the middle of your Line Item Descriptions.

Did you know your Project doesn't compile?

There are 8 undefined variables
1 called subroutine with the wrong number of parameters being passed
29 undefined functions (though all calls were for the same function)

I can spend a little time and write the code, but I've just spent an hour of my time already. If I have some free time maybe I'll revisit this issue.

I really recommend taking the time to understand Word. Specifically Styles and Tables. Also Tab stops, but that's not necessary for what you need to do.

Once you have learned about those items, take the time to learn about Word VBA. How to move around a document using VBA, insert text in different areas using VBA, and how to manipulate styles and tables in VBA. Also learning to search with VBA is very helpful too.

Then approach your project from an open mind given the things you have just learned. See how the layout for each Property Line Item is the same. Think about the different ways the information can be formatted (think about using styles and the paragraph property of "Keep with Next").

I'm very hesitant to re-code all your procedures because I truly doubt you will learn anything, and I'm surely not gaining anything. It is obvious to me that you don't have a great grasp of VBA and the applications Word and Excel. As your system is setup, we only needed 2 sheets from your Excel file and your supporting Word documents along with 2 subroutines amongst all your code. Yet, I believe you provided all the code in the workbook and all the sheets of data (even if the data has been changed to fake names and numbers). Additionally the format of your Word documents are primitive. Using underscores to make lines isn't necessary. Paragraph borders are much better, maybe in conjunction with a table!

Again, the best thing you can do for yourself is to learn how to use Word and Excel. Once you understand the features of Word and Excel, learning to manipulate the objects in VB isn't that hard. That's what I would do.

Meanwhile, if your bosses need a better solution, tell them to get off their cheap butts, spend a few hundred dollars (at max) and get someone who is an expert in Word, Excel, and VBA to revamp the whole project.

[jwise]

I was able to meet my deadline. I actually had a couple of days to spare until the boss said he wanted each description with custom text. I had to hastily get that out... Now that the pressure is off, I'm trying to fix this mess.

Most people are zealous about their editors, and I really miss my platform of choice. I find VBE has some extreme useability issues if you are a code pirate. Thus the "Your project doesn't compile" statement is because the "project" was never compiled. I searched the Internet and found several code snippets which were included in the first zip file. It never crossed my mind that someone would compile the project. Perhaps my paradigm will one day shift to VBE.

The code (procedures) in question did compile and run...just not very well. The double "Set to nothing" probably came from many attempts and rearrangements on trying to get this to work. My apologies again as I should have cleaned everything up before I zipped it. Hopefully the second zip which had only the necessary procedures in the project did not have this problem.

The code provided by Fumei was very helpful. When the comment about invoking Word on each pass through the loop was made, I understood for the first time about this "design issue". Totally my ignorance... hopefully I learned. I have another application where I did the same thing, so I'll change it too. I could tell a big difference in speed of execution. Thanks.

For whatever reason, I did not understand at all about "AutoText". With the code example, I searched the interent until I found some explanation on its useage. Thank you for making me aware of this technique. Now I understand your approach much better. I still do not understand why templates are so bad, but I have no reason to doubt Fumei/GWKenny.

I have tried to get the other suggestions working, but the "AutoText Insert" is not working. VBA says I have an object error. I will continue to work on this now that I have a little breathing room.

Thanks again for the assistance and suggestions.

[fumei]

"I could tell a big difference in speed of execution."

Indeed....you should. Reiterating the creation of an instance sucks up memory resources big time.

" I still do not understand why templates are so bad,"

No one suggested templates are bad. Templates are GREAT! They are a very very powerful part of Word. You simply have to understand what they are, and what they are used for. I have stated what that is. What is "bad" is that you were using them for something they are not designed to be used for.

You are getting (most likely) errors for the AutoText because the AutoText are not in the template you are using...which you do not state. Did you make one?

[jwise]

I made Word visible and played around with it after the error. I discovered that my AutoText was not in the lone template that I was using. I fixed that only to encounter error "405" at the same "Insert" method.

I jokingly tell people that success in life is now dependent on selecting the correct search words for Google, and a lot of my problems arise from not being able to use the help files very well. Perhaps the help files were designed to be solely reference material and not educational material. For example, Fumei used the "Where" parameter in the "Insert" method, and I was suspicious that this parameter was causing my error. I searched for over an hour before I found out enough information to tell me that my theory was bad. I own several Excel/VBA books and one Outlook/VBA book, perhaps I should buy a Word/VBA book as well.

Thanks again for the suggestion because it was "on the money"! I am continuing to work on this because I think this is an interesting problem and the techniques can be applied to a broad spectrum of problems.

[jwise]

I have tried everything I can find to no avail. Here is the statement:
wrdDoc.AttachedTemplate.AutoTextEntries("CATEGORY_Auto") _
.Insert Where:=Selection.Range, _
RichText:=True

When I receive the error, I go to the Word document and it looks as expected. The cursor is at the correct location (end of document), the AutoText entries are loaded in the template (only two them, spelling was checked). I found several references on MSDN to this statement, and it is as described. The "Selection Range" was set by the preceding statement, and since the cursor is at the end of the document, I am assuming the "Where" clause is OK. Please note that this is an Excel macro accessing Word.

Any ideas?

[fumei]

Sorry, my fault. You need to use fully qualified objects ALL the time.

Change:

Where:=Selection.Range


To:

Where:=wrdApp.Selection.Range

Which proves the concept that even when you know what you are doing....you probably don't. Or that one is always capable of being an idiot. In this case, it was me.

It should (hopefully) work now.

[jwise]

Thanks for the assistance. I plowed up an additional bug once I got past the error "450" and it took a while to run it down. I have another bug but it is only cosmetic.

My theory is that the "fully qualified" requirement is the result of using Word objects in Excel. Is this correct?

I would never have figured this one out. I'm really curious as to how you figured it out. I could see nothing in the error message that pointed to anything in particular.

[fumei]

"My theory is that the "fully qualified" requirement is the result of using Word objects in Excel. Is this correct?"

Yes.

When you make an instance of an application from another application, you must use fully qualified identifiers. This applies not just for Word objects in Excel, but Excel objects in Word, or PowerPoint objects in Word, or PowerPoint objects in Excel......

One of the common screw-ups involving Excel and Word is the object..."Range".

A Word.Range is very different than an Excel.Range

So say you have the procedure starting in Excel, and you make a Word instance, and you have a declaration:

Dim Rng As Range

then try and use that in your Word instance. It will fail, as the procedure is IN Excel, "Range" means an Excel range.

You would have to declare it as:

Dim Rng As Word.Range

and THAT would only work if you were using early-binding.

The reason I missed it was that dreaded copy and paste thing. One should never copy and paste code, or at least avoid it as much as possible.

In other words, I stuck the wrdApp at the beginning of things, but forgot the Selection for the Where. So of course, running from Excel...it has no idea what Selection means. What "Selection"????? But wrdApp.Selection...ah, another thing entirely.

[jwise]

Thanks again for the assistance. Hopefully, I learned something. Now that I have this app in shape, I want to add a progress bar (I found code in a JWalk book for this) and fix a similar design problem(multiple invocations of Word) in my other Excel/Word app.

Thanks also for the theory information... hopefully I won't make these mistakes again.

[fumei]

Ummm, well go ahead, but if I may gently suggest something....

get a better, more solidly grounded knowledge of the applications themselves, and of VBA.

You have a lot of basic structural flaws in your logic and your code. If I may, I will reiterate something from gekenny's post:

"There are 8 undefined variables
1 called subroutine with the wrong number of parameters being passed
29 undefined functions (though all calls were for the same function)"

These are simply not acceptable in anything trying to approach a professional level. Sorry, but I am going to repeat that: these are simply not acceptable in anything trying to approach a professional level.


Sure you can go ahead and throw in elaborate bells and whistles if you want. They may even work. However, you will be better off by far - in the long run - if you really really really got your procedures lean and trim and functioning with impeccable logic.

[jwise]

Thanks again for the advice.

I uploaded the entire module when I should have cleaned it first. I think my code would not have had so many errors if I had done my housekeeping beforehand. My guess is the second version which had all the extra stuff stripped from it would compile.

[fumei]

You just don't get it.

It was not that the code was not clean - "extra stuff stripped from it" - it was that it was poor code.

Misuse of objects, declared objects never used, multiple instances...and on and on.

These are NOT housekeeping errors! These are serious conceptual errors.

"I think my code would not have had so many errors if I had done my housekeeping beforehand. "

That is utter nonsense, and you still do not get it, and still - apparently - refuse to look in a mirror.

"I own several Excel/VBA books and one Outlook/VBA book, perhaps I should buy a Word/VBA book as well."

Perhaps, I suppose it would not hurt. However, as you clearly did not pick up basic SERIOUS concepts from the VBA books you have...it may be a waste of money to get one.

Putting the multiple created instances of Word is not a housekeeping error, it is not even a mis-conception of using instances (although it is...), it is a lack of understanding of what a loop is, and does.

That, my friend, is a basic, and vital concept to understand.

I do not care how fancy a code anyone writes, if they clearly do not understand the underpinning of HOW things work -

Oh well.

I will leave this with your last quote:

"My guess is the second version which had all the extra stuff stripped from it would compile."

THAT is exactly the problem, and the issue.

"my guess"

1. guesses are not professional









2. your guess is wrong.