Import filename into seperate columns?

[dark91zc]

i have a macro that is called get folder contents and i am wondering if there is a way to break up the file name structure to place different parts into different column?
example
doe, john 12-31-1978 06-09-78
smith, bob 06-15-1922 12-85-99

name d.o.b. mdr

or

doe, john 06-09-78 2008
smith, bob 12-85-99 2011

name mdr year

what i would like is to have it where name is in one column, dob in another, then mdr in the last one. if i was going to use it on different file name formats will i have to change the code to work with the new file name?



i am new to programming excel. and thanks for the help. i can post that macro i have if need be.

[tehneXus]

Hi dark91zc,

Welcome to the forums.

i can post that macro i have if need be.

Please do so

Regards

[dark91zc]

this is the code i have

Private Type BROWSEINFO ' used by the function GetFolderName
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long


Sub GetFileNames()
Dim lRow As Long
Dim sPath As String
Dim sFname As String

sPath = GetFolderName("Select a folder")
If sPath = "" Then
Exit Sub
End If
sPath = sPath & "/"
lRow = 1
Cells(lRow, "a").Value = sPath
sFname = Dir(sPath & "*.*", vbNormal)
Do Until sFname = vbNullString
lRow = lRow + 1
Cells(lRow, "a").Value = sFname
sFname = Dir
Loop
End Sub


Function GetFolderName(Msg As String) As String
' returns the name of the folder selected by the user
Dim bInfo As BROWSEINFO, path As String, r As Long
Dim X As Long, pos As Integer
bInfo.pidlRoot = 0& ' Root folder = Desktop
If IsMissing(Msg) Then
bInfo.lpszTitle = "Select a folder."
' the dialog title
Else
bInfo.lpszTitle = Msg ' the dialog title
End If
bInfo.ulFlags = &H1 ' Type of directory to return
X = SHBrowseForFolder(bInfo) ' display the dialog
' Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal X, ByVal path)
If r Then
pos = InStr(path, Chr(0))
GetFolderName = Left(path, pos - 1)
Else
GetFolderName = ""
End If
End Function

i got it from this site http://www.ozgrid.com/forum/showthread.php?t=66389

and thank you

[tehneXus]

Hi,

to access file attributes I am using the FileSystemObject, make sure to set the reference in VBA to "Microsoft Scripting Runtime" in VBA Editor -> Tools -> References.

Please Login or Register  to view this content.

If this answer solves your problem please mark this thread SOLVED

Regards

[dark91zc]

thank you that will help with some other files i am doing but not what i am looking for here is a better example i hope.

the typical file name format is this last name, first name date of birth in this format MM/DD/YYYY followed by mdr (medical record number)
ARBOGAST, DAVID 11-21-1982 00-21-69
ARCHIBALD, TRISTEN 03-27-1972 00-15-69
BADAWI, LLYOD 02-14-1988 00-34-68
BAKKEN, KEVIN 04-03-1984 00-29-68
BATY, CINDY 11-18-1980 00-12-68
BAWA, NINA 07-01-1983 00-39-69

but when it is imported into excel it looks like this
ARBOGAST, DAVID 11-21-1982 00-21-69.pdf
ARCHIBALD, TRISTEN 03-27-1972 00-15-69.pdf
BADAWI, LLYOD 02-14-1988 00-34-68.pdf
BAKKEN, KEVIN 04-03-1984 00-29-68.pdf
BATY, CINDY 11-18-1980 00-12-68.pdf
BAWA, NINA 07-01-1983 00-39-69.pdf

i would like it to import in like this
column a column b column c
name DoB MDR
ARBOGAST, DAVID 11-21-1982 00-21-69
ARCHIBALD, TRISTEN 03-27-1972 00-15-69
BADAWI, LLYOD 02-14-1988 00-34-68
BAKKEN, KEVIN 04-03-1984 00-29-68
BATY, CINDY 11-18-1980 00-12-68
BAWA, NINA 07-01-1983 00-39-69

thanks for the help i hope this helps a little better.

[tehneXus]

Hi,

replace the GetFileNames Sub with:

Please Login or Register  to view this content.

Regards

[dark91zc]

i am getting an error with this part highlighted fso As New FileSystemObject.
i will be back on tomorrow thanks for the help =D

[tehneXus]

Hi,

that's strange, I didn't change anything in that line.

make sure to set the reference in VBA to "Microsoft Scripting Runtime" in VBA Editor -> Tools -> References.

Regards

[dark91zc]

thanks so much i guess the script had reset. it works great thank you so much =D.