Retrieving text information from a file name?

[vavroom]

I'm sure there must be a way to do this, but I really have no idea
what it is. Could some kind soul help me out here?

On a form, I need to extract two bits of text from a file name. The
file name gets loaded in a text box, along with the entire path,
through a common dialogue box that allows the user to browse their
system.

The files will always have the same pattern, but the path before it may
not always be the same.

So, say the content of the textbox is:
C:\full\path\to\12345678_name_xxxxxx.pdf

The pattern is that the file name will always start with 8 numbers,
followed by an underscore. Followed by a person's name, which will
obviously vary in length depending on the name. This is followed by an
underscore, and several numbers.

I need to be able to "lift" the first 8 numbers, as well as the
name, to use elsewhere (the idea is to save some typing, and base
information on the file selected).

I've looked at some of the formulas that manipulate text, but I
can't make head nor tail of it. Help?

Thanks in advance

 

[Douglas J. Steele]

If the file actually exists, you can strip off just the file name using the
Dir function.

In other words, if your text box is named txtFullPath, then you can use
Dir(Me.txtFullPath) to return just 12345678_name_xxxxxx.pdf in your example
below.

Once you have just the file name in a string, there are several different
ways to parse it.

One would be something like:

Dim intFirstUnderscore As Integer
Dim intSecondUnderscore As Integer
Dim lngNumber As Long
Dim strFileName As String
Dim strName As String

strFileName = Dir(Me.txtFullPath)
intFirstUnderscore = InStr(strFileName, "_")
If intFirstUnderscore > 0 Then
intSecondUnderscore = _
InStr(intFirstUnderscore + 1, strFileName, "_")
If intSecondUnderscore > 0 Then
lngNumber = CLng(Left$(strFileName, _
intFirstUnderscore - 1)
strName = Mid$(strFileName, _
intFirstUnderscore + 1, _
intSecondUnderscore - intFirstUnderscore)
Else
' no second underscore was found: what do you want to do?
End If
Else
' no underscore was found: what do you want to do?
End If

Another would be something like:

Dim lngNumber As Long
Dim strFileName As String
Dim strName As String
Dim varValues As Variant

strFileName = Dir(Me.txtFullPath)
varValues = Split(strFileName, "_")
If UBound(varValues) > 0 Then
lngNumber = CLng(varValues(0))
strName = varValues(1)
Else
' no underscore was found: what do you want to do?
End If

 

[vavroom]

Doug,

Thanks for your response. The code you've proposed is *way* beyond me,
but I've tried applying it "as is" to the afterupdate even of txtPath
then tried to return strName in a different textbox (I think that's the
value I'd be looking for), but that doesn't seem to work at all. In
fact, just trying to get the Dir() working to retrieve the file name
doesn't seem to work. Yes, the file actually exists.

The second code sample is to retrieve the number, while the first one
retrieves the name, is this correct?

Hmmm, hope to detangle this one, smells so close and yet so far!

 

[Douglas J Steele]

The two samples both do the same thing: they read the content of whatever's
in text box txtFullPath and put the number in variable lngNumber and the
name in variable strName.

How are you trying to use the code? What event have you put it into?

 

[vavroom]

Doug, thanks for your response. With that brief explanation of what
your suggestions do, I was able to make it work, using the second
method! Excellent! Thanks

Now, if I could make the attachment work, it would be awesome, but
that's another question entirely (
http://groups.google.com/group/micr...3ba70b491b9/824733b2d7010925#824733b2d7010925
)

In any case, Doug, you were extremely helpful and your assistance is
much appreciated. Thanks

 

[vavroom]

Ah ha! One slight problem, which I think should be easily solved, but
i'm not sure how to go about it. The code you suggested appears to be
stripping the zeros at the beginning of the number, and they are
necessary. So '05038492' can't be transformed into '5038492'

I tried defining it as integer, double, decimal, and a variety of other
number types, but they all strip the 0.

Any ideas?

 

[Rick Brandt]

Ah ha! One slight problem, which I think should be easily solved, but
i'm not sure how to go about it. The code you suggested appears to be
stripping the zeros at the beginning of the number, and they are
necessary. So '05038492' can't be transformed into '5038492'

I tried defining it as integer, double, decimal, and a variety of other
number types, but they all strip the 0.

Any ideas?

If you want leading zeros then use Text. (numbers don't have leading zeros).

 

[vavroom]

Thanks Rick,

I assume you mean to define the variable as String, since I can't use
"Text" in code. I am not actually storing the information in the
database, simply retreiving a filename from a folder. The filename
happens to include an 8 digit number, which may, or may not, including
leading zeros.

In any case, I tried the code dimming the variable as string instead of
long, and I still was returned with the zeros stripped out.