Parse Directory Filenames
 

Hello all,


I am stuck and was hoping some of the experts out there in the foru
could give me a hand.

First, let me explain what I am trying to accomplish:


I have a directory (C:\Home) that has hundreds of files in it.

They are medical transcription files in Word format and are named a
such:


12345,Doe John,Ltr.doc

67890,Doe Jane,Pro.doc


The first string of 5 characters is the medical record.
The second string is the patient name.
The third is what type of document this is (Ltr being Letter, Pro bein
Progress Note, etc)


What I am trying to do is:

1. Parse directory and populate A1 with result.
2. Parse filenames, put medical record number in B1
3. Parse filenames, put patient name in C1
4. Parse filenames, put document type in D1

- If Ltr is found, "Letter" is inserted into D1
- If Pro is found, "Progress Note" is inserted into D1


Below is my code, I have gotten as far as 1. in the list above.

Any help would be very much appreciated!


Sub PARSE()
Dim fs, f, f1, f2
Range("A1").Select
i = 1
fldr = "C:\home\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder(fldr)
Set f1 = f.Files
For Each f2 In f1
ActiveCell.Offset(i, 0).Value = f2.Name
i = i + 1

Next
End Sub





thank you,

Chri

--
chrisjnorsko
-----------------------------------------------------------------------
chrisjnorskov's Profile: http://www.excelforum.com/member.php...fo&userid=2628
View this thread: http://www.excelforum.com/showthread.php?threadid=39570

Parse Directory Filenames
 

Add a Dim arr as Variant, then use split() to get the parts of the
string:

arr = split(f2.name, ",")

Range("A" & i).Value = arr(0)
Range("B" & i).Value = arr(1)
Range("C" & i).Value = arr(2)

If InStr(arr(4), "Ltr") 0 Then
Range("D" & i).Value = "Letter"
ElseIf InStr(arr(4), "Pro") 0 Then
Range("D" & i).Value = "Progress Note"
End If

Parse Directory Filenames
 

Append this to your code

Columns("A:A").Copy Range("B1")
Columns("B:B").TextToColumns Destination:=Range("B1"), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=True, _
Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1),
Array(3, 1))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"chrisjnorskov"
wrote in message
news:chrisjnorskov.1ts6qi_1124067998.5826@excelfor um-nospam.com...

Hello all,


I am stuck and was hoping some of the experts out there in the forum
could give me a hand.

First, let me explain what I am trying to accomplish:


I have a directory (C:\Home) that has hundreds of files in it.

They are medical transcription files in Word format and are named as
such:


12345,Doe John,Ltr.doc

67890,Doe Jane,Pro.doc


The first string of 5 characters is the medical record.
The second string is the patient name.
The third is what type of document this is (Ltr being Letter, Pro being
Progress Note, etc)


What I am trying to do is:

1. Parse directory and populate A1 with result.
2. Parse filenames, put medical record number in B1
3. Parse filenames, put patient name in C1
4. Parse filenames, put document type in D1

- If Ltr is found, "Letter" is inserted into D1
- If Pro is found, "Progress Note" is inserted into D1


Below is my code, I have gotten as far as 1. in the list above.

Any help would be very much appreciated!


Sub PARSE()
Dim fs, f, f1, f2
Range("A1").Select
i = 1
fldr = "C:\home\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder(fldr)
Set f1 = f.Files
For Each f2 In f1
ActiveCell.Offset(i, 0).Value = f2.Name
i = i + 1

Next
End Sub





thank you,

Chris


--
chrisjnorskov
------------------------------------------------------------------------
chrisjnorskov's Profile:
http://www.excelforum.com/member.php...o&userid=26286
View this thread: http://www.excelforum.com/showthread...hreadid=395707

Parse Directory Filenames
 

thanks for the tips! I have it working. Excellent!

Now I'm stuck on another issue.


I need to be able to read a word file and pull out the patient name an
date of birth and paste it into G1 and H1 excel columns.


In the word file each page or two has a header:


Patient Name: Doe, John Date of Birth
01/01/2004


etc...


Has anyone out there done this already?


The code i am trying to insert this functionality into is below:


Sub PARSE()
Dim fs, f, f1, f2
Dim arr As Variant

Range("A1").Select
i = 1
fldr = "C:\home\"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder(fldr)
Set f1 = f.Files

For Each f2 In f1
ActiveCell.Offset(i, 2).Value = f2.Name
arr = Split(f2.Name, "-")
ActiveCell.Offset(i, 3).Value = Left(f2.Name, Len(f2.Name) - 4)
i = i + 1

If InStr(arr(0), "PG") 0 Then
Range("A" & i).Value = "14"
End If

If InStr(arr(1), "SM") 0 Then
Range("F" & i).Value = "Dr. Smith"
End If

'Range("G" & i).Value = arr(2)

If InStr(arr(3), "WC") 0 Then
Range("E" & i).Value = "West Coast Health Care"
End If

If InStr(arr(4), "LTR") 0 Then
Range("B" & i).Value = "Letter"
ElseIf InStr(arr(4), "PRO") 0 Then
Range("B" & i).Value = "1"
ElseIf InStr(arr(4), "EML") 0 Then
Range("B" & i).Value = "Email"
End If


Next

End Sub




thank you for the help!

Chri

--
chrisjnorsko
-----------------------------------------------------------------------
chrisjnorskov's Profile: http://www.excelforum.com/member.php...fo&userid=2628
View this thread: http://www.excelforum.com/showthread.php?threadid=39570