splitting filename with VBA?

[out_airborne]

Hi everyone,

I am trying to create a status list of documents in a directory. I want
to take as much workload away from my fellow collegues. So I thought it
must be possible to get the information about a file directly from the
filename itself. I am experienced in PHP, but don't know that much yet
about VBA. I am happy for any help.

Here the case again:

Excel file columns:
Filefilter (P for public, M for management, X1 for client x1)
Filename (Contract232)
Valid until (20030531)

Now I thought, I could train the guys (and women) here to name a file
for example:

P_Contract232_20030531.pdf

Then use Excel to check if the file is there (This I could do) and
splitt the filename to the above mentioned parts...

Hmm,... I checked the Internet for hours before I submitted this... so
if any help is out there, I highly appreciate it!!!

Yours, Oliver.
[out_airborne]

 

[zantor]

Hi out_airborne,

One way to do this is as follows:

Sub BreakString()
Dim str(1 To 3) , strfilename As String
Dim i, p, l As Integer

strfilename = "P_Contract232_20030531.pdf"

For i = 1 To 3
If i = 3 Then
l = Len(strfilename)
p = InStr(1, strfilename, ".")
str(i) = Mid(strfilename, 1, p - 1)
strfilename = Mid(strfilename, p + 1, l - p)
Else
l = Len(strfilename)
p = InStr(1, strfilename, "_")
str(i) = Mid(strfilename, 1, p - 1)
strfilename = Mid(strfilename, p + 1, l - p)
End If
Next i

MsgBox str(1)
MsgBox str(2)
MsgBox str(3)

End Sub

Hope it will help.

 

[keepitcool]

if you're sure that users have excel2000 or newer
you can use split function from vba
have a look at split in vba help
it will return a zero based variant

Sub test()
Dim parts As Variant
Dim filename As String
Dim i As Integer
filename = "P_Contract232_20030531.pdf"
parts = Split(Left(filename, Len(filename) - 4), "_")
For i = 0 To UBound(parts)
Cells(1, i + 1) = parts(i)
Next
End Sub






keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >