extract more than 1 file extension from a directory path

[futonguy]

Hi all,

I am not sure if i should be posting at this site of the forum.
But my question is:

How can i extract the file directory with files containing extension *.xls and *.csv file?

Currently i can only extract a single extension file.

here's my code:
strFile = Dir(strFolder & "*.xls")
'strFile = Dir(strFolder & "*.csv")
While strFile <> "" 'while strFile NOT EQUAL TO
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
hope to get some advise soon
Cheers

[MitchO]

Forgive me, this is my first attempt at responding, so I may be missing some obvious stuff, but I'm confused by what you're asking the code to do versus what it does.

You are looking for any strFolder that has a .xls and a .csv inside of it? strFile in your code will give you the name of the first .xls file inside strFolder. For example:

Dir("C:\*.txt")
for me, returns bdlog.txt, the first text file in the main C:\ folder.

If you want a folder that contains both, just check both situations in If .. And .. :


If Dir(strFolder & "*.xls") <> "" And Dir(strFolder & " *.csv") <> "" Then
intFile = intFile + 1
ReDim Preserve strFileList(1 to intFile)
strFileList(intFile) = strFolder
End If

If you want the names of both files inside the folder, are they going in the same strFileList array? Then you just check for both, and add them one at a time:

If Dir(strFolder & "*.xls") <> "" And Dir(strFolder & " *.csv") <> "" Then
intFile = intFile + 1
ReDim Preserve strFileList(1 to intFile)
strFileList(intFile) = Dir(strFolder & "*.xls")

intFile = intFile + 1
ReDim Preserve strFileList(1 to intFile)
strFileList(intFile) = Dir(strFolder & "*.csv")

End If
(There are probably cleaner ways to keep your intFile and ReDim Preserves at that point, but for simplicity's sake for now)

Hope I sent you on the right track!

ETA: Also just a quick observation, I wanted to make sure that your strFolder has a trailing "\" on it ... if it reads "C:\Drivers" then your Dir will read Dir("C:\Drivers*xls") when you want Dir("C:\Drivers\*xls"). If it's there, cool. If not, you probably want to add it to the file extension part you're typing:

Dir(strFolder & "\*.xls")

[futonguy]

thanks Mitcho,

for that method, i can retrieve the file only once. What i have to do when i need to loop the folder to check for the all the relevant files with the similar extension to be retrieve out ?

[MitchO]

OK let's back up.

If you want to go through a folder, and do various things depending on situations inside of it (files existing, etc) ... it sounds to me like you'd rather be using the FileSystemObject instead. Essentially, this is an object with access to the file system, and you can then make an object that is the folder. One of the properties of this object would be an array of the items inside the folder, literally the .Files collection.

I would suggest you take a look at "Files Property", "Folder Object" and "FileSystemObject" in the VB Help and go from there. Essentially it sounds like you want something in this vein:


Dim fs as Object
Dim fFolder as Object
Dim fFile as Object

'See if our qualification is true; the folder has both types in it
If Dir(strFolder & "\*.xls") <> "" And Dir(strFoldeR) & "\*.csv" <> "" Then

'OK it qualifies. Let's loop through the folder and extract the name
'of ALL .xls and .csv files
Set fs = CreateObject("Scripting.FileSystemObject")
'FULL_PATH is a placeholder, you need to give the GetFolder Method
'the entire path of strFolder (eg: "c:\Documents and Settings\strFolder")
Set fFolder = fs.GetFolder(FULL_PATH & "\" & strFolder)

'Go through each file in the newly created Folder object
For Each fFile In fFolder.Files
'Check if the current file in the loop is xls or csv
'If so, add it to the strFileList array
If Right (fFile.Name,3) = "xls" Or Right (fFile.Name,3) = "csv" Then
intFile = intFile + 1
ReDim Preserve strFileList (1 to intFile)
strFileList (intFile) = fFile.Name
End If
Next fFile
End If

'Make sure to clean up our objects. Good ol' Access and memory issues.
Set fFolder = Nothing
Set fFile = Nothing
Set fs = Nothing

Now, again ... I don't know what you're using it for, so there may be more efficient ways to do this (I still don't fully get what you're trying to capture here, a list of the files, a list of the folders with the files, if there is a loop or function passing strFileList), so I definitely recommend reading the help files to fine tune this into the direction you are looking for, but hopefully it starts you in the right path.

(Another random thought/note: if you're looking for a folder that has an .xls and an .csv in it ... what about a folder with just two .xls? Does that count? Your current way of looking doesn't handle that. You may want to loop through the For Each ... and set up a flag that any file of your type exists .. and if you find another with the flag set, there's your key that ANY two files of your types are there)