By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Challenge: Match a string in a file path

P: n/a
Hi All,

I have quite a unique issue - I would be very grateful if someone
could help out with this challenge....
I have two columns, Column A contains and acronym, and Column B
contains a corresponding file path. Like below:
Column A
FSSC
GPM SBU
AL ACT
.....
Column B
/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/ITO/GPM/FSSC/
ADM-E-R001-C001.txt
/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/ITO/GPM/GPM
SBU/
ADM-I-R003-R001.txt
/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/II/AL/AL ACT/
ALV-A-R001-C001.txt
....
The 9th folder in the file path matches the acronym in column A, but
only 90% of the time. I have around 3000 rows and I need to highlight
which ones don't match the acronym in column A. Basically, what I
want
to do, is chop off everything before 9th "\" and everything after the
10th "\" and see if it matches column A - can anyone solve this
challenge?
Thank you so much!
Regards,
Tariq

Feb 8 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Feb 8, 5:17 am, "Tawreq" <taw...@gmail.comwrote:
Hi All,

I have quite a unique issue - I would be very grateful if someone
could help out with this challenge....

I have two columns, Column A contains and acronym, and Column B
contains a corresponding file path. Like below:

Column A
FSSC
GPM SBU
AL ACT
....

Column B
/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/ITO/GPM/FSSC/
ADM-E-R001-C001.txt

/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/ITO/GPM/GPM
SBU/
ADM-I-R003-R001.txt

/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/II/AL/AL ACT/
ALV-A-R001-C001.txt

...

The 9th folder in the file path matches the acronym in column A, but
only 90% of the time. I have around 3000 rows and I need to highlight
which ones don't match the acronym in column A. Basically, what I
want
to do, is chop off everything before 9th "\" and everything after the
10th "\" and see if it matches column A - can anyone solve this
challenge?

Thank you so much!

Regards,
Tariq

I don't have much time to spend on it, but here's one that works with
at least 2 "/" above the file. You'll have to modify it if you need to
allow for any path (not just 9 folder in) - and if a different
delimiter is used ( \ instead of /). It will currently fail if the
path is not in the same format as you posted.
After modifying it to meet your needs, this should always give you the
last listed folder (probably better than relying on it to always be
the 9th). Use it in a qry and compare the output with ColumnA.

Function GetLastFolder(pth As String) As String
On Error GoTo stoprun
GetLastFolder = ""
Dim a As Long
Dim pos As Long
Dim slshLocs() As Long
Dim lenFldr As Long

a = 0
pos = 1
Do Until pos = 0
pos = InStr(pos, pth, "/")
If pos <0 Then
ReDim Preserve slshLocs(a)
slshLocs(a) = pos
a = a + 1
pos = pos + 1
End If
Loop

If UBound(slshLocs) >= 1 Then
a = UBound(slshLocs)
pos = UBound(slshLocs) - 1
lenFldr = slshLocs(a) - slshLocs(pos) - 1
GetLastFolder = Mid(pth, slshLocs(pos) + 1, lenFldr)
End If

exit_here:
Exit Function

stoprun:
MsgBox Err.Number & " - " & Err.Description
Resume exit_here
End Function

Feb 8 '07 #2

P: n/a
"Tawreq" <ta****@gmail.comwrote in news:1170929879.301819.284220
@m58g2000cwm.googlegroups.com:
Hi All,
I have quite a unique issue - I would be very grateful if someone
could help out with this challenge....
I have two columns, Column A contains and acronym, and Column B
contains a corresponding file path. Like below:

Column A
FSSC
GPM SBU
AL ACT
....
Column B
/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/ITO/GPM/FSSC/
ADM-E-R001-C001.txt
/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/ITO/GPM/GPM
SBU/
ADM-I-R003-R001.txt

/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/II/AL/AL ACT/
ALV-A-R001-C001.txt

The 9th folder in the file path matches the acronym in column A, but
only 90% of the time. I have around 3000 rows and I need to highlight
which ones don't match the acronym in column A. Basically, what I
want
to do, is chop off everything before 9th "\" and everything after the
10th "\" and see if it matches column A - can anyone solve this
challenge?
Test

ColumnA = Split(ColumnB, "/")(9)

Feb 8 '07 #3

P: n/a
Tawreq wrote:
>I have quite a unique issue - I would be very grateful if someone
could help out with this challenge....

I have two columns, Column A contains and acronym, and Column B
contains a corresponding file path. Like below:

Column A
FSSC
GPM SBU

Column B
/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/ITO/GPM/FSSC/
ADM-E-R001-C001.txt

/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/ITO/GPM/GPM
SBU/ADM-I-R003-R001.txt

The 9th folder in the file path matches the acronym in column A, but
only 90% of the time. I have around 3000 rows and I need to highlight
which ones don't match the acronym in column A. Basically, what I
want
to do, is chop off everything before 9th "\" and everything after the
10th "\" and see if it matches column A - can anyone solve this
challenge?
I like Lyle's idea, but another way could be the criteria:

Like "*\*\*\*\*\*\*\*\*\" & columnA & "\*"

--
Marsh
Feb 8 '07 #4

P: n/a
Test

ColumnA = Split(ColumnB, "/")(9)
I feel dumb for asking, but where does Split come from?
I've never seen this before.

Feb 9 '07 #5

P: n/a
On Feb 8, 9:53 pm, "storrboy" <storr...@sympatico.cawrote:
Test
ColumnA = Split(ColumnB, "/")(9)

I feel dumb for asking, but where does Split come from?
I've never seen this before.
Split was introduced in Access 2000. I believe it existed in VB
previously, but not in VBA.
Of course, many similar UDFs were created and published for use in
Access 97 and previous versions.

Feb 9 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.