Setting a variable for use in a macro. 3

[gbent]

Hi,

I am new to VBA language. I have a collection of .rtf files and am working on a word macro with VBA to process the files. I am attempting set up a variable so that it is the first line of the .rtf file which contains the county name in each file. I would then like to populate a column with the county name for each record in the file. If anyone can steer me in the right direction it would be greatly appreciated.

Thank-you

 

[TonyJollans]

You don't have access to the RTF stuff. Word reads it all and converts it to document format and presents that to you. Or are you simply reading them as text files?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[gbent]

I am allowing word to read it and convert it to a document format that is letting me view it. It is the first line of the word document that I would like to be the variable so when the file New Haven is being viewed the variable is New Haven and then when the Litchfield county file is viewed the variable is Litchfield. Ultimately I would like to be able to export my result to excel and populate a column next to the records.

Thanks

 

[fumei]

If the file has the first "line" terminated by a paragraph mark - and of course it may not -

myVariable = ActiveDocument.Paragraph(1)

If the first "line" is terminated by a line break (not a paragraph mark), then it is a little trickier.

faq219-2884

Gerry
My paintings and sculpture

 

[gbent]

The first line of the data reads Litchfield ^p. So would it then be myVariable = ActiveDocument.Litchfield?

Thank you

 

[TonyJollans]

No, no, no!

You need to take the text from the paragraph ...

ActiveDocument.Paragraphs(1).Range

But then you will also need to strip off the paragraph mark. Perhaps the easiest is:

[blue]Split{ActiveDocument.Paragraphs(1).Range,vbcr)(0)[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[fumei]

Interesting syntax on the Split.

gbent, I am just hassling Tony for fun.

There is a typo. Split should be followed by a "(", not a "{". A curly bracket is not valid.

Also, as you are new to VBA, make sure you are using Option Explicit. It will save you problems in the long run. If you do not know what that is, in the VBE (Visual Basic Editor) go Tools > Options > Editor tab, and check "Require Variable Declaration".

So, to get the text of the first paragraph without the paragraph mark into a variable (named myVariable), the syntax is:

' declare the variable
Dim myVariable As String

' give the variable a value
myVariable = Split(ActiveDocument.Paragraphs(1).Range,vbCr)(0)

faq219-2884

Gerry
My paintings and sculpture

 

[gbent]

Thanks everyone,

I'm going to give it a try over the weekend and will let you know how it works out. Thank-you again for your help it is truly appreciated.

 

[TonyJollans]

What's the odd curl between friends?

Thanks for picking that up, Gerry.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[gbent]

That worked great thanks guys. My next question is how to I insert the variable in this situation. My format is as follows, The text I have goes ^lNO. I would like to replace that with |"Variable"^lNO. I think it should look something like the following but don't know how to script it so it will insert the variable value.

Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^lNO."
.Replacement.Text = "|"Variable^lNO. "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.HomeKey Unit:=wdStory

Any help you can offer would be greatly appreciated. Thank-you

 

[PHV]

Like this ?
.Replacement.Text = "|" & Variable & "^lNO. "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gbent]

That doesn't seem to insert the value of my variable,

My variable is this : myVariable = Split(ActiveDocument.Paragraphs(1).Range, vbCr)(0)

I am not sure how to insert the value of variable here.


Thanks,

 

[PHV]

So, replacing this:
.Replacement.Text = "|"Variable^lNO. "
with this:
.Replacement.Text = "|" & myVariable & "^lNO. "

doesn't work ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gbent]

That works,

Thank you very much for your help.

 

[fumei]

I am not sure how to insert the value of variable here."

gbent, the whole point of using a variable (all variables, not just a document variable), is to use it. he value of the variable is what ever you give it (depending on legitimate syntax).

Once a variable has been given a value, then you use the variable by name, as a replacement for that value. The variable IS (by name) the value.

myVariable = Split(ActiveDocument.Paragraphs(1).Range,vbCr)(0)

The variable myVariable - NOT a very good name BTW - is equal to the text of the first paragraph, no matter what that text is.

faq219-2884

Gerry
My paintings and sculpture