Creating, Populating, and Using a 2-dimensional array

[Bill]

I used to write in VB quite a bit (back in the 90s) but haven't had occasion
to write anything in a while. Now I am trying to get my head back in the
game. After surmounting the loss of Application.FileSearch with six hours of
frustration until I found the Dir() command I thought I would ask a basic
question prior to jumping into the frustration again.

I have a set of files (143) that I have created using VB and three
templates. The files are all named cryptically, for example:

A26Checklist.doc
A27Studyoutline.doc
B01checklist.doc
B02checklist.doc
B03studyoutline.doc

What I would like to do is cycle through the 143 files in the folder, open
each one, take the first three characters of the document name, fill a
variable with a text string based on the first three characters, do a little
searching and replacing with the text string, and then close the document in
question.

I know how to cycle through the files using Dir(pathname & "*.doc). I know
how to use the Mid() command to get my text strings out of the doc.name. I
know how to do the searching and replacing once I have the text string in
variable. But for some reason my aging brain isn't getting the right code
for setting up and populating the array.

I tried to get around the question by just creating a giant "Select Case"
statement, but unfortunately VB won't let me do that inside of the
"For...Next" loop that I wanted to do to go through the Dir()'s.

The 3-character names and text strings all are unique:

A26 = "EmergencyResponse"
A27 = "Board Operation"
B01 = "Unit Overview"
B02 = "Unit Utilities"
B03 = "Tank Gauging"

This seems to be a natural two-dimensional array. The question is how do I
define it and populate it? Then how do I get VB to give me, for example,
"Tank Gauging" for the B03 code?

I did a lot of searching in the internet before I thought to come look into
Microsoft's online community. So perhaps I am just frustrated and should
read all the strings in here.

I would appreciate any help.
Bill Dannenmaier

 

[Jonathan West]

I used to write in VB quite a bit (back in the 90s) but haven't had
occasion
to write anything in a while. Now I am trying to get my head back in the
game. After surmounting the loss of Application.FileSearch with six hours
of
frustration until I found the Dir() command I thought I would ask a basic
question prior to jumping into the frustration again.

I have a set of files (143) that I have created using VB and three
templates. The files are all named cryptically, for example:

A26Checklist.doc
A27Studyoutline.doc
B01checklist.doc
B02checklist.doc
B03studyoutline.doc

What I would like to do is cycle through the 143 files in the folder, open
each one, take the first three characters of the document name, fill a
variable with a text string based on the first three characters, do a
little
searching and replacing with the text string, and then close the document
in
question.

I know how to cycle through the files using Dir(pathname & "*.doc). I
know
how to use the Mid() command to get my text strings out of the doc.name.
I
know how to do the searching and replacing once I have the text string in
variable. But for some reason my aging brain isn't getting the right code
for setting up and populating the array.

I tried to get around the question by just creating a giant "Select Case"
statement, but unfortunately VB won't let me do that inside of the
"For...Next" loop that I wanted to do to go through the Dir()'s.

I can't imagine why not. I've put Select case statements inside For Next
loops many times.

The 3-character names and text strings all are unique:

A26 = "EmergencyResponse"
A27 = "Board Operation"
B01 = "Unit Overview"
B02 = "Unit Utilities"
B03 = "Tank Gauging"

This seems to be a natural two-dimensional array. The question is how do
I
define it and populate it? Then how do I get VB to give me, for example,
"Tank Gauging" for the B03 code?

This sounds like a job for a Collection object. In the early part of your
code, do something like this

Dim CharCodes As Collection
Set CharCodes = New Collection
CharCodes.Add "EmergencyResponse", "A26"
CharCodes.Add "Board Operation", "A27"
CharCodes.Add "Unit Overview", "B01"
CharCodes.Add "Unit Utilities", "B02"
CharCodes.Add "Tank Gauging", "B03"

Include all your character codes and strings in this way.

Then, when going round the loop, if the variable sChars has been loaded with
the first three characters of the current filename, you can get the matching
string from CharCodes(sChars)

 

[Jay Freedman]

Hi Bill,

You can certainly do this, something like this:

Sub demo1()
Dim First3Chars As String, longName As String
Dim prodIdx As Long
Dim Products(4, 1) As String
Products(0, 0) = "A26"
Products(0, 1) = "EmergencyResponse"
Products(1, 0) = "A27"
Products(1, 1) = "Board Operation"
Products(2, 0) = "B01"
Products(2, 1) = "Unit Overview"
Products(3, 0) = "B02"
Products(3, 1) = "Unit Utilities"
Products(4, 0) = "B03"
Products(4, 1) = "Tank Gauging"

'within the Dir() loop...
First3Chars = "B03" ' you would extract this
' from the filename

For prodIdx = 0 To UBound(Products, 1)
If First3Chars = Products(prodIdx, 0) Then
longName = Products(prodIdx, 1)
Exit For
End If
Next prodIdx

' now do the replacement of
' First3Chars with longName

End Sub

However, I don't understand your statement that "VB won't let me do that
inside of the "For...Next" loop that I wanted to do to go through the
Dir()'s". There's absolutely no reason a Select Case can't be placed inside
a For...Next loop. Try this:

Sub demo2()
Dim First3Chars As String, longName As String

'within the Dir() loop...
First3Chars = "B03" ' you would extract this
' from the filename
Select Case First3Chars
Case "A26":
longName = "EmergencyResponse"
Case "A27":
longName = "Board Operation"
Case "B01":
longName = "Unit Overview"
Case "B02":
longName = "Unit Utilities"
Case "B03":
longName = "Tank Gauging"
End Select

' now do the replacement of
' First3Chars with longName

End Sub

If you're still having trouble, post the complete code you're working with
and quote exactly any error message it produces. Also, are you working in
VBA within Word, or with VB from outside of Word?

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Bill]

Thank you Jay I will try this out.

As for posting the code, I was running small scripts in a test area and
didn't leave a trail of bread crumbs back to the offending message. I will
take your word on it.

Really, what I need to do is run out to Fry's Electronics and buy a book. My
last VB book is a properly aged and beloved Office 97 VB book. It was good
in its time, though.

Bill

 

[Bill]

Jonathan:

Thanks for helping out. I try out this solution. I had seen some talk of
collections in the help screens for VBA (I am working in VB behind Word, not
as an independent app) that refer to collections and thought it would be a
path to take next.

Bill Dannenmaier
CEO
BlackBox Partners, LLC
a service-disabled veteran owned business
(e-mail address removed)