Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you pull filenames from a directory? | Excel Discussion (Misc queries) | |||
retrieve filenames from given directory into excel | Excel Worksheet Functions | |||
open in old directory save in new directory | Excel Programming | |||
Creating a macro that lists directory names within a directory.... | Excel Programming | |||
Check if directory empty OR no of files in directory. | Excel Programming |