Solved: Using TXT file as array filler

[lynnnow]

Hi,

I've got a macro wherein I have hard coded words to spell check the document with. This macro has been posted on my colleagues' computers. Now everytime there needs to be an addition/deletion (more of additions than deletions) of a word to the macro, I've got to go back to the code editor and hard code these words in which it is quite cumbersome making the code very long and tiring to keep track of and then updating the pc's is still cumbersome. What I've thought of is whether a txt file can be use as an array filler and words only need to be added to this txt file which can be done with a simple copy and paste command while leaving the code intact.

I've tried using the array function within the code itself and it works fine, however, I'm trying to use the txt file as the array feeder. I'm not able to understand how use a txt file as the array feeder. There will be documents that will be opened and closed and everytime the variable will have to reload the array for the macro to work. This might also be a problem. Is this a feasible option or should I hard code the words in the macro array itself?

I recorded the macro at first and had limited words, however, the popularity of the macro has increased and so have the words. An addition is made nearly every week and it is cumbersome to go on editing the macro, considering it is now 2200+ lines now. This is part of the macro:


'I've used different colors for various types of words
'e.g. british/american, sound alikes, citations, etc.
Options.DefaultHighlightColorIndex = wdTurquoise
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Highlight = True
With Selection.Find
.Text = "principal"
.Replacement.Text = "principal"
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = True
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = True
End With
Selection.Find.Execute Replace:=wdReplaceAll


I've used array variables for the .Text and .Replacement.Text fields and it works just how I need it.

Any help/suggestions in solving this query is appreciated.

Lynnnow
:think: :dunno : pray2:

[mdmackillop]

Hi Lynnnow
Try the following to create an array from the text file. Please make a copy of the text file for testing, just in case of "accidents". The code assumes text in two columns with tab spacer, so may need some adjusting. Can you zip and post part of your txt file?


Option Explicit
Sub OpenTextFileTest()

Dim tmp
Dim CheckList As String, TextLine As String
Dim MyList(5000, 2)
Dim i As Long, j As Long

CheckList = "c:\aaa\test.txt"

Open CheckList For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Check for end of file.
i = i + 1
Line Input #1, TextLine ' Read line of data.
tmp = Split(TextLine, vbTab)
MyList(i, 1) = tmp(0)
MyList(i, 2) = tmp(1)
Loop
Close #1

CheckArray:
For j = 1 To i
Debug.Print MyList(j, 1) & " - " & MyList(j, 2)
Next
End Sub

[MOS MASTER]

Hi, :yes

IMO Textfiles are also rather cumbersome to maintain.

I always use a second worddocument with a table in it with two columns.
In the left column you type in the find word in the second one the replace word.

Now your code can open that document and proces the Word in that table for the find & Replace.

HTH :whistle:

[fumei]

Why are you hard coding words for spell checking????? This seems counter productive. If there are words that you have to distribute as part of a spelling check, use a custom .dic file.

Not quite getting the purpose of this.

[MOS MASTER]

Why are you hard coding words for spell checking????? This seems counter productive. If there are words that you have to distribute as part of a spelling check, use a custom .dic file.

Not quite getting the purpose of this.
Hi Gerry, :yes

Totally missed the part about the spelling..(English is still hard to read fast) :rofl:
I really thought we where doing a mass search & replace...

If it's about adding to the dic then you're absolutly right with adding a custom.dic as the better aproach!...:whistle:

[lynnnow]

Hi Gerry and Groetjes,

The issue is mass search and replace. So i've got to use the array function.

Mdmackillop:

The zip file is attached. as you can see the words are separated with tab spacers, so i'm counting on your code working. Thanks for the help.:whistle:

[lynnnow]

Hi MdMacKillop:

Your code shows me a Sub or Function not defined error at the Split() line. What do i do?

[mdmackillop]

Hi Lynnow,
The file was not attached. Check the size limit when you try to upload it. If it is a large file, we only need a sample to check out the code.

Edit
I've revised my code above to declare all the variables. I don't think this was the problem, but give a another try. Sorry, no more time now.

[MOS MASTER]

Hi Gerry and Groetjes,

The issue is mass search and replace. So i've got to use the array function.


Hi, :yes

Just call me Joost thank you.

I don't know if you have to use the array function. For me its still not clear what you want to do exactly with that textfile.

If it is an extension of the Dictionary I'd still try gerry's method first.
If not and you just want to replace a whole bunch of words with other words then I wouldn't use a textfile but a worddoc with a table in it for easy maintenance.

Well will find out soon enough if the txtfile approach works as expected so Good luck! :whistle:

[mdmackillop]

Here's my output from printing MyList to the immediate window, so it works here.
Did you try rerunning the code?

*wdDarkRed -
trail - trail
tow - tow
compliant - compliant
defense indemnification - defense indemnification
fro - fro
*wdTurquoise -
causal - causal
casual - casual
severally - severally
severely - severely
surmised - surmised
summarized - summarized
scraped - scraped
scraping - scraping
scrapped - scrapped
scrapping - scrapping
cite - cite
site - site
per jury - per jury
ontact - contact
contract - contract
alleviate - alleviate
elevate - elevate
illicit - illicit
elicit - elicit
warrantee - warranty
preceding - preceding
proceeding - proceeding
awarded - awarded
ordered - ordered
residents - residents
residence - residence
formally - formally
formerly - formerly
restroom - restroom
restaurant - restaurant
dependent - dependent
brake - brake
break - break
extend - extend
extent - extent
defer - defer
differ - differ
this position - this position
disposition - disposition
insure - insure
ensure - ensure
incidents - incidents
incidence - incidence
except - except
accept - accept
eminent - eminent
imminent - imminent
access - access
excess - excess
aisle - aisle
isle - isle
personal - personal
personnel - personnel
weather - weather
whether - whether
dual - dual
duel - duel
affect - affect
affect - affect
career - career
carrier - carrier
compile - compile
complaint - complaint
confirm - confirm
conform - conform
constrictive - constrictive
constructive - constructive
dairy - dairy
discovery inspection - discovery inspection
effect - effect
for closure - for closure
form - form
higher - higher
hire - hire
hole - hole
similarly - similarly
spilt - spilt
stationary - stationary
stationery - stationery
summarily - summarily
therefor - therefor
therefore - therefore
trial - trial
whole - whole
*wdRed -
cancell - cancel
cheque - check
cloths - clothes
continous - continuous
counter claim - counterclaim
counter offer - counteroffer
despatch - dispatch
focuss - focus
forth coming - forthcoming
judgement - judgment
labell - label
mould - mold
neighbour - neighbor
plough - plow
totall - total
travell - travel
work place - workplace
*wdBrightGreen -
A. 2d - A.2d
A.D. 2d - A.D.2d
Cal.2d - Cal. 2d
Cal.3d - Cal. 3d
Cal.Rptr. - Cal. Rptr.
F. 2d - F.2d
F. 3d - F.3d
F.Supp. - F. Supp.
Ill.App. - Ill. App.
N.Y. 2d - N.Y.2d
N.Y. 3d - N.Y.3d
N.Y.S. 2d - N.Y.S.2d
N.Y.S. 2d - N.Y.S.2d
S. E. 2d - S.E.2d
S. E. 3d - S.E.3d
S. W. 2d - S.W.2d
S. W. 3d - S.W.3d
S.E. 2d - S.E.2d
S.E. 3d - S.E.3d
S.W. 2d - S.W.2d
S.W. 3d - S.W.3d
SO. 2d - So. 2d
So.2d - So. 2d

[fumei]

Could you please post precisely the situation, the requirements that are determining that the solution is a loading of hardcoded items into an array that is to be used for a search and replace. I still would like to know why a custom.dic file is not a betterway. You may of course be correct, but for the enlightenment of all of us, it may be nice to see clearly why there is a need for this route.

Assuming that in fact you DO need a separate function for a mass search and replace (and again, I would like to see the requirements for this), I agree with Joost. I would go with a Word document rather than a textfile.

[mdmackillop]

I would as well, to be honest, but just trying to provide what was asked for!
Lynnow, I've no problem going with an alternative solution, when it is the better way to go.

[MOS MASTER]

I would as well, to be honest, but just trying to provide what was asked for!

Hi Malcom, :yes

Of course you did.. I think we all try to get the best sollutions by adding to the discussion.

You're code was excellent of course..


Hi MdMacKillop:

Your code shows me a Sub or Function not defined error at the Split() line. What do i do?

Lynnow, are you on Word '97?
I'm asking because that's the error you would receive in Word '97 because the Split Function was not in '97! (It's available in Office 2000 or greater)

You could add this function Click here (http://groups-beta.google.com/group/microsoft.public.word.vba.beginners/msg/e6b56d53ebeec50f?hl=en&lr=&ie=UTF-8&safe=off)

This is a custom Split function that will do the same for yah. :whistle:

[lynnnow]

Yes, I'm using Word 97, however, the link and code you provided throws a subscript out of range error now at the MyList(i,2) line.

The code as it stands now is this:

Option Explicit
Sub OpenTextFileTest()

Dim tmp
Dim CheckList As String, TextLine As String
Dim MyList(5000, 2)
Dim i As Long, j As Long

CheckList = "c:\lincoln\projects\words source.txt"

Open CheckList For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Check for end of file.
i = i + 1
Line Input #1, TextLine ' Read line of data.
tmp = Split(TextLine, vbTab)
MyList(i, 1) = tmp(0)
MyList(i, 2) = tmp(1)
Loop
Close #1

CheckArray:
For j = 1 To i
Debug.Print MyList(j, 1) & " - " & MyList(j, 2)
Next
End Sub
Public Function Split(Expression As String, Optional ByVal Delimiter As _
String = " ", Optional ByVal Count As Long = -1, Optional ByVal Compare As Integer = 0) As Variant

Dim lPos1 As Long
Dim lPos2 As Long
Dim lIdx As Long
Dim lCnt As Long
Dim arResult() As String

'Initialize the variables
lCnt = 0
lPos1 = 1
ReDim arResult(99)

'Search for the delimiter.
lPos2 = InStr(1, Expression, Delimiter, Compare)
Do While lPos2 > 0 And ((lCnt <= Count) Or (Count = -1))
'Delimiter found, extract the substring between the delimiters.
arResult(lCnt) = Mid$(Expression, lPos1, lPos2 - lPos1)
lCnt = lCnt + 1
If (lCnt Mod 100) = 0 Then
'Increase array size if needed.
ReDim Preserve arResult(UBound(arResult) + 100)
End If
'Move to end of last delimiter found.
lPos1 = lPos2 + Len(Delimiter)
'Search for the next delimiter.
lPos2 = InStr(lPos1, Expression, Delimiter, Compare)
Loop

If lPos1 < Len(Expression) Then
'Extract last substring.
arResult(lCnt) = Mid$(Expression, lPos1)
lCnt = lCnt + 1
End If

'Resize the array to correct size.
If lCnt > 0 Then
ReDim Preserve arResult(lCnt - 1)
Else
ReDim arResult(-1 To -1)
End If

'Return the array.
Split = arResult

End Function

This is a bit advanced for me. Additionally, the reason I'm doing a mass search and replace is to make the user pay more attention to the highlighted words while proofing the document, once he/she has transcribed it. There are instances where similar sounding or similar appearing words can be mistakenly keyed in. A custom.dic file has all the spellings (right and inappropriate at times) in it and will not show an spell error. Thus this extra check. Is there a need for tweaking the above code, since i'm not uncertain of what the split function is doing, but i'm trying to understand it and what i understand from the code is that the split function is using a single dimension array while the code provided to input the txt file is using a 2 dimension array. I tried putting a double array in the first redim of the split function but it then threw a subscript out of range error. where exactly do make the change is something i'm wondering. and since i've not got the entire day to mull over the problem, i'm leaving this bit in your capable hands.

regards,

[fumei]

Hi lynnow,

I am still unclear about this.

You have an existing document...transcribed or other wise - I am very unclear what the relevance, or even the meaning of "transcribed".

Ok, so there is a document, with text in it.

This procedure opens a text file and, using that, builds an array of words.

Ok, so far, so good. Then what? You are going to check every word in the document to the words in the array? And in doing so, you are going to highlight the words that match? That do not match?

I am rather curious, so if you would not mind, could you please walk through it? Step by step. I see no code that actually makes a selection (or a range) and makes a highlight. You say you want to make more attention, but precisely how are you doing that?

[fumei]

Hi lynnow,

I am still unclear about this.

You have an existing document...transcribed or other wise - I am very unclear what the relevance, or even the meaning of "transcribed".

Ok, so there is a document, with text in it.

This procedure opens a text file and, using that, builds an array of words.

Ok, so far, so good. Then what? You are going to check every word in the document to the words in the array? And in doing so, you are going to highlight the words that match? That do not match?

I am rather curious, so if you would not mind, could you please walk through it? Step by step. I see no code that actually makes a selection (or a range) and makes a highlight. You say you want to make more attention, but precisely how are you doing that?

[lynnnow]

this is how it goes:

If you are privy to the concept of medical transcription, then understanding this bit is a cinch. A voice file is received by us which needs to be transcribed, then proofed, and uploaded to the sender. The words in the array will highlight the words in the document before the file is proofed (wherein the user uses a shortcut key to fire the macro to highlight the words). The hard coded version has the words as provided in the attached txt file, but now the length is increasing and so is the hard coding effort. It just is a matter of seconds where the words are highlighted with the appropriate replacements (in the hard coded version, though i'm not sure how long this will take with the txt file in play). When the user proofs the document, he checks for the relevancy of the highlighted word in the context. It is just as a double checking method for any word errors in a document.

I created the macro for my use only initially, however, it was liked by some others and hence it has been distributed on the floor.

I've not included the highlighting part in this part of the code, but it is similar to the part i've posted earlier in this thread. it just uses the array variables instead of the hard coded data, thus reducing the code length tremendously.

this is how the next part of the code looks:

Sub trial()
Dim OriginalWord(2, 2) As String
OriginalWord(0, 0) = "diary" ' same word to be used for replacing
OriginalWord(0, 1) = "diary"
OriginalWord(1, 0) = "trial" ' look alike word used for replacing
OriginalWord(1, 1) = "trail"
Options.DefaultHighlightColorIndex = wdTurquoise
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Highlight = True
i = 0
Do While i <= 2
With Selection.Find
.Text = OriginalWord(i, 0)
.Replacement.Text = OriginalWord(i, 1)
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = True
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
i = i + 1
Loop
End Sub

it's just a trial part and it works successfully, so i just have to edit the variables when the loading of the txt code is complete.

[fumei]

I understand the process of transcribing.

However, this seem very strange to me. I see in your code that you have replacement words that are the same as the original. diary / diary. This is non-intuitive to me.

Since you did not respond with my request for a step by step walk through.....let's see if I can do it for you.

1. A voice file is received, and the document is created based on what the typist hears.

2. As sounds can be similar among various words, there is likely errors in transciption.

3. A procedure is loaded with commonly misheard words, hard coded.

4. The procedure finds EVERY instance of each word and REPLACES it with another, and then makes the replacement word highlighted.

Is this correct? This is what I meant by step by step.

If this is correct, I must ask...what if the original word is correct? What if trial is correct? You are replacing it with trail, regardless. The code finds trial and replaces it. Sure it highlights it, but how is the proofer to know what the original transcribed word was? Is it relevant? I don't know. That is why I asked for a step by step. Actually, the code part is the least of it. The design part is much more interesting.

You said it worked sucessfully. Please define what sucess means.
words in the array will highlight the words in the document
This is incorrect. The words in the array do not highlight anything. Your code highlights words, after replacing them - in some cases apparently with the same word.

Could you please explain:

1. why the words in the array are significant.
2. why is the word automatically replaced.

I am trying to understand why, in your example, ALL "trial" will be replaced with "trail"...but if there are any "trail" it is not touched. You have: .Text = OriginalWord(i, 0)
.Replacement.Text = OriginalWord(i, 1)
but never: .Text = OriginalWord(0, i)
.Replacement.Text = OriginalWord(1, i)
Could not the reverse of the array be true? Please let me know.

Again, this is why I am asking these questions. Sorry if I am being a bit annoying, but design issues are a major interest for me. I am hoping to learn something here.

[fumei]

Just as an additional thought...

From a design point of view I could see doing a search for a word, then inserting a possible correction word after it The potential correction word could be its own character style. This would make it very easy for a proofreader to see. A proofing procedure would loop through all instances of this style displaying each one in an message box, with a choice to keep the original, or replace it with the inserted "correct" word. If the original is accepted, the inserted word is removed. If the inserted word is accepted, the original word is removed.

It just seems strange to me that a trigger word is replaced regardless of logic. Logic in the sense that there is no determination of whether a replacement is required, or not. It just is replaced.

Perhaps if you helped me with that I could also understand a process that searches for a word, and replaces it with the same word.

[MOS MASTER]

Yes, I'm using Word 97, however, the link and code you provided throws a subscript out of range error now at the MyList(i,2) line.


Hi, :yes

Well this must be something in code or in the txtfile.
Could you provide the txtfile so we can test on it?

I've attached a sample of the above code and all works well over here. :whistle:

[lynnnow]

Hi Gerry:

I understand that there are some words that are being replaced with the same word and it is intentional. This is so that the original word is not overwritten with the inappropriate word, it will just get highlighted. Since I needed to use the same word for the replacement but highlight it, I used this technique. In the example that I provided I did use the "Trial" and "Trail" option only for code check purposes. It is not used in runtime. However, there are some spellings that are replaced with the correct spellings, for eg. judgement vs. judgment (since judgement is a british spelling of the word and our firm is in the US). These words are highlighted with red to make it stand out, since it is a fatal error if british spellings are found by the client.

Joost:

That tweak was marvellous. With your help, I've now made the following edit to the code, which still needs a bit of a nudge. I'm having a difficulty in setting the highlight color from the variable.

the code looks like this now:


For j = 1 To i
If MyList(j, 0) = "*" Then
ColorUse = MyList(j, 1)
j = j + 1
End If
Options.DefaultHighlightColorIndex = ColorUse ' This is where I need help
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Highlight = True
With Selection.Find
.Text = MyList(j, 0)
.Replacement.Text = MyList(j, 1)
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = True
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = True
End With
Selection.Find.Execute Replace:=wdReplaceAll
Next


It shows me a type mismatch error. I know that wdDarkRed and the other color codes are VBA standard, however, can't it be passed thru a variable? If it can be, is there any other method of passing these codes to that line?

Thanks for all the efforts guys...:friends: :bow: :beerchug:

[MOS MASTER]

It shows me a type mismatch error. I know that wdDarkRed and the other color codes are VBA standard, however, can't it be passed thru a variable? If it can be, is there any other method of passing these codes to that line?

Thanks for all the efforts guys...:friends: :bow: :beerchug:

You're most welcome! :yes

Wel you've only pasted part of you code so we pretty much know nothing. (No offence) If you ask a question please post the whole code so we can see important things like Dim statements for dimensioning the variabels.

You use: ColorUse as a variable.
You don't use the Naming convention (like strColorUse or sColorUse) so that I could guess it was a String (I think it is)

First of al if you wan't to replace a Constant from a Enum you have to pass it in the right Variable dimension and use it's numeric index in the Enum to pass it.

So wdDarkRed = 13 in wdColorIndex (Enum)
To retrieve 13 go to the VBE en press F2. In the search box paste wdDarkRed and press enter. To your lower left you'll see something like:
Const wdDarkRed = 13

So you're code needs to pass 13 in this line:
Options.DefaultHighlightColorIndex = ColorUse

Now you need to no first which type DefaultHighlightColorIndex expects so highlight is and press F1.
The help states that the Value is Long so your Variable "ColorUse" should be dimmed to Long

Like: Dim ColorUse As Long

So the only line you have to change in your current code is the Dim statement. (I presume cause I didn't seen all)

HTH, :whistle:

[lynnnow]

Thanks again Joost

This is the entire code now as I use it. It works perfectly. I've edited the Words Source.txt file to have the wdColorIndex Enum instead of the value. This is the complete code as it now stands:

Sub SpellChecker_Modified()

Dim tmp
Dim CheckList As String, TextLine As String
Dim ColorUse As Long
Dim MyList(5000, 2)
Dim i As Long, j As Long

CheckList = "C:\Lincoln\Projects\words source.txt"

Open CheckList For Input As #1 ' Open file for input.
Do While Not EOF(1) ' Check for end of file.
i = i + 1
Line Input #1, TextLine ' Read line of data.
tmp = Split(TextLine, vbTab)
MyList(i, 0) = tmp(0)
MyList(i, 1) = tmp(1)
Loop
Close #1

'CheckArray:
For j = 1 To i
If MyList(j, 0) = "*" Then
ColorUse = Val(MyList(j, 1))
j = j + 1
End If
Options.DefaultHighlightColorIndex = ColorUse
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Highlight = True
With Selection.Find
.Text = MyList(j, 0)
.Replacement.Text = MyList(j, 1)
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = True
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Next
Options.DefaultHighlightColorIndex = wdYellow
End Sub
Public Function Split(Expression As String, Optional ByVal Delimiter As _
String = " ", Optional ByVal Count As Long = -1, _
Optional ByVal Compare As Integer = 0) As Variant

Dim lPos1 As Long
Dim lPos2 As Long
Dim lIdx As Long
Dim lCnt As Long
Dim arResult() As String

'Initialize the variables
lCnt = 0
lPos1 = 1
ReDim arResult(99)

'Search for the delimiter.
lPos2 = InStr(1, Expression, Delimiter, Compare)
Do While lPos2 > 0 And ((lCnt <= Count) Or (Count = -1))
'Delimiter found, extract the substring between the delimiters.
arResult(lCnt) = Mid$(Expression, lPos1, lPos2 - lPos1)
lCnt = lCnt + 1
If (lCnt Mod 100) = 0 Then
'Increase array size if needed.
ReDim Preserve arResult(UBound(arResult) + 100)
End If
'Move to end of last delimiter found.
lPos1 = lPos2 + Len(Delimiter)
'Search for the next delimiter.
lPos2 = InStr(lPos1, Expression, Delimiter, Compare)
Loop

If lPos1 < Len(Expression) Then
'Extract last substring.
arResult(lCnt) = Mid$(Expression, lPos1)
lCnt = lCnt + 1
End If

'Resize the array to correct size.
If lCnt > 0 Then
ReDim Preserve arResult(lCnt - 1)
Else
ReDim arResult(-1 To -1)
End If

'Return the array.
Split = arResult

End Function


Now all I have to do this amend the default location for the txt file and then the system is a go. Thanks again:beerchug: :friends: :bow:

[MOS MASTER]

Hi, :yes
Glad we could help you and good luck on your project! :beerchug:
ps..don't forget to mark your thread solved.