Simple loop to read and store words

[AyeSee]

What I am trying to do is very simple, and i'm surprised that I cannot find what I am looking for. Probably because I am not searching with the right technical terms. I am sorry if this post has ever been asked often, but I cannot seem to find.

In programming class, the first thing we saw in java was to create an array and store a bunch of data as a first step before doing several sorts.

In my case, I am trying to do the same thing, but in VBA. At the beggining of my word file, i have a list of fields which I need to store in order to find and replace these fields to their corresponding area. I have found code to do the replacement field, so I am good for that, but I need to fill an array (or string). This is the output I am trying to achieve:

2001
85%


In YEAR (to be replaced by 2001), the average will be AVERAGE (to be replaced by 85%.


So I have found code to replace, but how would I create a loop that reads the first lines and stores as an array (or whatever is best in VBA)?

I was thinking of this as my algorithm:

1. For n from 0 to 2
2. add line 1 to array
3. delete line 1
4. increment n

(and then i would use that array for my search and replace)

It sounds very simple but I am not very familiar with VBA yet

Thanks for any help... or if you know of a thread or website where this has been covered, I would be very greatful

[Tinbendr]

At the beginning of my word file, i have a list of fields which ....We need a sample of the list and of the body.


I have found code to do the replacement field...
Post that too. (Use the VBA brackets around the code.

[AyeSee]

Ok.

My word version is 2003. This is a sample of before, these numbers and fields can change, as they are case by case and are inserted by a fusion... the Paragraphs inside my text are also a fusion, but I decided not to put IFs inside the fusion because users might have to change the content of the paragraphs later on, and it is less complicated to ask them to follow a "codification" for attributes than having them to do {IF {MERGEFIELD...etc} all over again... So here is my example. The first fields are all variables and I have to store them to initiate a replacement:

2001
2007
January
March
August
July
85%
eighty five percent

- And one of my paragraphs. This paragraph does not use all of these variables, but my whole document will. -

Notwithstanding the calculated adjustment based on the agreed formula, the adjustment increase over the previous year for year (n) shall not exceed one and CAP_IN_WORDS (CAP_IN_NUMBERS%).

- the code should 1. store the variables, delete them, and replace those uppercase fields. The variables will always be the same and will always be present, so we can assume that (x, y, z, etc.) x will be always assotiated to the same uppercase field. - This is what should be present in my text after (the variables ideally should be gone as the code reads them):

Notwithstanding the calculated adjustment based on the agreed formula, the adjustment increase over the previous year for year (n) shall not exceed one and 85% (eighty five percent).


The code I have found was on another thread responded by fumei June 26th that looks like this:




Sub ReplaceColours()
Dim r As Range
Dim colours() As String
Dim var
colours = Split("blue,green,red", ",")
For var = 0 To UBound(colours())
Set r = ActiveDocument.Range
With r.Find
Do While .Execute(Findtext:="x" & colours(var) & "x", _
Forward:=True) = True
r.Text = colours(var) & " " & colours(var)
r.HighlightColorIndex = wdYellow
r.Collapse 0
Loop
End With
Next var
End Sub

[fumei]

"and are inserted by a fusion"

What the heck is a "fusion"????

Also, i fail to how: "Notwithstanding the calculated adjustment based on the agreed formula, the adjustment increase over the previous year for year (n) shall not exceed one and 85% (eighty five percent)."

is relevant whatsoever to what you are asking about.

You need to clarify: "So I have found code to replace, but how would I create a loop that reads the first lines and stores as an array (or whatever is best in VBA)?"

Are these "linea" - i.e text - or are they fields? Originally, they sounds like fields...

" i have a list of fields which I need to store in order to find and replace these fields "

If they are fields, refers to them as fields (not "lines").

I have no idea at all what you mean by: "1. store the variables, delete them, and replace those uppercase fields. The variables will always be the same and will always be present"

Fields are not variables. They DO however have values, which can be stored in variables. You do not "delete" variables. They CAN however be given a value of 0, or "" - depending on the variable data type.

What do you mean by replace those uppercase fields. replace them with....what? I do not even know what you mean by uppercase fields.

[AyeSee]

fusion is mailmerge sorry... I'm in a builingual environment (french and english) and my word is french


Before "Notwithstanding the calculated adjustment..." I clearly stated that it was one of the paragraphs in my document... Forget the whole mailmerge, that has been taken care of.... I decided not to insert "intellegent" (with if clauses) mailmerge because users will have to change the text in the future. I am fusing an Access database which contains all the different options possible for each clause.

In order to avoid using the if clause, im putting all the variables at the top of the document and then I'm reading them one by one and replacing them by their respective acronyms which I have established.

You are right, I am sorry, they are fields, but I will treat them as lines as a way to differenciate them.

I realised the uppercase fields do not matter, but if you observe the "nothwithstanding" clause, I have put them uppercase so that the user knows they are replacement fields.

My loop may be wrong, it was just my idea for creating a string with all the fields I want to insert in my text. ex: turn
2001
Test
Test 2

into:

array(2001, Test, Test 2)

For the moment, I have modified your code to make it work for my needs. It is not the most efficient, but it works, I guess.


'this first part of the code will place the cursor at the begginning of the text
Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="1"
Selection.GoTo What:=wdGoToLine, Name:="1"
'this next part will select the whole first line and remove the final key which is "enter"
Selection.HomeKey Unit:=wdLine
Selection.EndKey Unit:=wdLine, Extend:=wdExtend
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
' This is the loop to replace the text by the corresponding fields.
Dim r As Range
Set r = ActiveDocument.Range
With r.Find
Do While .Execute(Findtext:="BASE_YEAR", _
Forward:=True) = True
r.Text = Selection.Text
r.Collapse 0
Loop
End With
' This is to delete the field and pass on to the next
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.Delete Unit:=wdCharacter, Count:=1

[fumei]

I am still not following. I do not understand the "delete fields". Do you mean change the value, or actually remove the field.

Your code above has no fields actioned at all. It looks for text


Findtext:="BASE_YEAR"
and replaces the found text with selection.text. Not fields.

In terms of your original question, you can build arrays using Split, or you can use a redim preserve action. The attached document has five (real) fields at the start of the document: Author, FileSize, FileName, NumPages, SectionPages. I have toggle (Alt-F9) so the document should show the field codes.

The VBA builds an array of the results (values) of all fields in the document, then outputs those results as strings. Click "BuildMyArray" on the top toolbar.


Option Explicit

Sub BuildMyArray()
Dim aField As Field
Dim myArray() As String
Dim j As Long
Dim var
For Each aField In ActiveDocument.Fields
ReDim Preserve myArray(j)
myArray(j) = aField.Result
j = j + 1
Next
' go to the end of the doc
Selection.EndKey Unit:=wdStory
' write out each array item plus paragraph mark
For var = 0 To UBound(myArray())
Selection.Text = myArray(var) & vbCrLf
Selection.Collapse 0
Next
End Sub
I wish I understood what it is you are trying to do better. I hope this helps a bit.

[AyeSee]

Thank you for the response Gerry. Sorry if my explanations are not clear. What you have given me seems to do what I am looking to do.

I will try to explain plainly without telling you at all how I think I can do it. I built a tool that generates a contract in an Access database. The user awnsers a few questions, and based on them, the access database takes the related entries and puts them into a word document through mail merge. The only problem is that in the textual fields inserted there are years, percentages, and others.

If you read up on mail merge, you will find that you can make it so that the merge follows conditions that would achieve what I need to do. I want to avoid doing that because if I do that and someone changes the wording, the conditions will have to change as well. Instead, I will develop a coding for my users to follow when rewriting the wording. example percentage = PERCENTAGE.

Consequently, when a user creates a contract, it will
1. merge the fields in the database as text at the begining of word document
2. he will click a button
3. the macro will read all the merged fields at the begining of the document and replace the "codes" that I prescribed for my users

So the whole point of this thread was just to read and store all of the textual fields in the array to then do the processing. The deletion was basically to remove the fields that were temporarily inserted by the mailmerge for my program.

[fumei]

How bizarre. If I understand you...

You have a merge document, with merge fields.

You perform a merge.

You convert the resultant merged fields to text yet somehow the "macro will read all the merged fields"

Let me show that.

1. merge the fields in the database as text
2. who cares
3. read all the merged fields

Except....there ARE no merged fields. How can they be read if you have put their values in as text???

I still do not understand the uppercase issue: percentage = PERCENTAGE

I have no idea what is going on with that.

I am still not sure if there are fields...or not.

I also do not understand: "I want to avoid doing that because if I do that and someone changes the wording, the conditions will have to change as well. "

What wording? Why does that change the conditions? Maybe I am missing something, but it seems you are adding extraordinary work disassembling a merge.

[AyeSee]

ok lol
There is no use of uppercase... it could be lower case, it is just so that the users know that those are the replacement "words" (not fields). I excuse my unproper use of terminology, this experience is showing me that I truly merit my noob title.

The macro will read the text. Everything in word is text indeed. It is just that there are two types of text that I have merged.

1. Paragraphs in the form of memos which contain "holes" which i have named with uppercase words
2. replacements for those "holes", which I have inserted at the beginning of the document

The paragraphs are part of a legal document and the users the final product to be changeable. ex:

Old wording:
The cap will go no higher than 95%, with a hypercap of 3%.

New wording:
With a hypercap of 3% following the baseyear of 2010, a cap of 95% is immient.

If I used conditional mergefields, It complicates changing this wording... That is why I use my unconventinal system..

[fumei]

"If I used conditional mergefields, It complicates changing this wording... That is why I use my unconventinal system.."

OK, that is making a bit mroe sense. However......

Old wording:
The cap will go no higher than 95%, with a hypercap of 3%.

New wording:
With a hypercap of 3% following the baseyear of 2010, a cap of 95% is immient.


OK, fine. But what determines what is old and what is new, and more importantly what logic determines if "old" is supposed to be "new"...THIS TIME?

And as for: "which contain "holes" which i have named with uppercase words"

SAY WHAT???????????????????????????

"holes"?????

Worse yet...named holes????

What the heck is a hole, and how do you name it??!!!????

[AyeSee]

The user determines it. Whatever is in the database is necessarily the most recent version. Once I am done this project, the database will be updated by a super user. No old wordings are stored. Only the latest.

I called it a hole because It was a metaphore I thought appropriate to the situation. The final version of the contract actually has the wording as such:

"With a hypercap of 3% following the baseyear of 2010, a cap of 95% is immient."

But since the 3%, the 2010 and the 95% are all variable, I have to find a way to insert them into the paragraphs. That being said, I replaced them by words (uppercase only to make them obvious for the user):

"With a hypercap of HYPERCAP following the baseyear of BASEYEAR, a cap of CAP is imminent."

With this system, the user can change the wording in a similar way as he would in Word. I could have cut the text up into smaller pieces and have it automatically put together like such:
<txtParagPart1><numHypercap><txtParagPart2><numBaseYear><txtParagPart3><numHyperCap><txtParagPart4>

But that would significantly complicate changing the wording for an untrained user. That is why I want the fields to be written as text and have the macro search and find where they should be replaced.

Would you have a better way to suggest?

[fumei]

Wow. I really really really want to understand what the heck is going on here.

The new (final for now) wording is:


"With a hypercap of 3% following the baseyear of 2010, a cap of 95% is immient."

But since the 3%, the 2010 and the 95% are all variable,But if the OLD wording is:

Old wording:
The cap will go no higher than 95%, with a hypercap of 3%.




ummmmmmmmmmmmmm, WHERE/WHAT is the variable for baseyear of 2010? It is NOT in the old wording....and...these are not (as previous stated) actually variables. It is text.

I am still not seeing this. i have a better appreciation of what you seem to trying to do, but i am still foggy on what exacrly you are doing.

Interesting though.

[AyeSee]

Yeah, I know. It is because there are a few parameters which come into play in the contract when it is negotiated. The formula, the base year, and etc. there is about a list of 10 possible variables (yes in this case they are truly mathimatical variables... but some of them are just years which can vary case to case).

Sometimes, depending on how the formula is negotiated, there are different variables which must be included in the wording, and years which can be different depending on the contract. That is to say the output is not standard. So there is no real way to predict where the "variables", as I call them (text in word, but in the database they can vary), will be inserted in the document.

That is why the search and replace seems like the easyest way out, but I needed a way to read those variables to feed it to the search and replace function.... With a bit of code from everywhere I have finally found a way to do all of this

[fumei]

OK, but they are NOT variables, right? So please, stop calling them that.

So there is no real way to predict where the "variables", as I call them (text in word, but in the database they can vary), will be inserted in the document.
If you can not predict where...WHAT does determine where???

[AyeSee]

The Where is determined by if the user finds it is necessary in a paragraph. It can be in any of the paragraphs.

The paragraphs exist inside the text only if the contract responds to certain criteria. To make it simple, every contract is different.

Therefore, there are pre-determined words which will be searched by the VBA code to be replaced by the correct text placed at the begginning of the document.

I put the textual fields in the form of text at the beggining of the document in a specific order so that the VBA code replaces by the right word at the right time.

[fumei]

Sorry, there is language/terms issues here. I asked:

WHAT does determine where???

Your response does nothing to answer that.

The Where is determined by if the user finds it is necessary in a paragraph.

There is no logic in that. That answers (perhaps) WHY - the user finds it necessary. But it most certainly does NOT answer WHERE.

"I put the textual fields in the form of text at the beggining of the document"

Oh no you do not. You are not putting "textual fields". There are no such things that are "in the form of text". Text is text. That is ALL it is.

" in a specific order so that the VBA code replaces by the right word at the right time."

I am quite certain that this is not needed.

[AyeSee]

I'm gonna cry lol. God damnit, I did not think two canadians could have this much trouble communicating!!! Unless this is a troll. In any case I am discouraged haha

Nothing is certain in terms of where, because that depends on the why (which is not predictable). Technically, if the user finds it is not necessary to have any of the parameters anywhere, they can be nowhere. So if there is no why, there is no where. That being said, I still will export all of the existing parameters even if they are null, they just wont go anywhere (other than at the beggining of the word document).

So say the user decides there is a why, he can then determine the where by he putting a keyword which I predetermined depending on what he wants:
- If he wants the deadband in the text, he writes DEAD_BAND in the middle of the paragraph
- Hypercap= HYPER_CAP
- etc. for everything.

[fumei]

OK, now we are getting somewhere. The logic (what I have been trying to get from you) is:

The user types in pre-determined words, and it is THOSE words that determine what/where.

OK. That is fine. I did not know this. It sounds liked YOU were putting in text. For example....

"I put the textual fields in the form of text "

I - not "user". When you use "I" I assume you mean "I".

So, no "I" do NOT put the "textual" fields (which are not fields, not textual) in the document.

The user types in "DEAD_BAND" someplace and then -afterwards - you (the coder) search for these text chunks and action things accordingly.

I do not like it. I do not like it at all. The possibility of error seems huge to me.

What if the user types in "DEAd_BAND"?
What if the user types in "DEaD_BAND"?
What if the user types in "DEAD _BAND"?

All of those are NOT "DEAD_BAND", therefore any logic operations looking for "DEAD_BAND" will fail to find it.

What if they type in "DEAD_BAND" correctly...but at the wrong location??? What if they typed it two characters to the left of where they really want it? Does that means they have to go back and manually adjust things?

Seems very sloppy to me.

[AyeSee]

I agree that the risk is great. That being said, very little people will have access to changing the wording, and I will document appropriately in the update GUI. The explanations will state it clearly, therefore the user will be warned to follow the correct wording.

Generally only one person will be allowed to maintain the database.

The database will not be updated often.

My initial testing has told me that uppercase/lowercase does not affect the search terms...

[AyeSee]

I am taking that risk in order to make the update by an untrained person somewhat possible.

On another note, would you know how to compare a selection in this manor?


Dim y As String
y.text = selection
IF y = "Price" THEN
Selection.text = "true"
ELSE
Selection.text = "false"
END IF

Currently it always returns as false, do you know why?

[fumei]

Dim y As String
y.text = selection


GONG!!!! This will fail. It is incorrect syntax. Y, declared as a String, does not have ANY object properties using Dot syntax.

Are you using Option Explicit? If you are not...start using RIGHT NOW.

Your code above should not even run, never mind return "false". Actually, even without Option Explicit y.Text should fail. Are telling me it does not?

Not .Text
Not .Value
Not .Start
Not .Length
Not .Anything