asked on

File System Object

I already have a working solution (source copied from a helpfull website), but i don't really understand it and i'd prefer to do the same thing in a syntax i'm farmiliour with - i'm a beginner :-)

Basically i want a routine that will look through the contents of a given folder, and if the file is an excel workbook, then run some code using it's filename as a paramater.

This works: (Using Excel VBA if it matters)

Dim fs, f, f1, filelist()
Dim k As Integer, j As Integer, i As Integer
Const PATH = "C:\WINDOWS\Desktop\trainers\Training Plans\"
ReDim filelist(0)
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(PATH)
Worksheets("Estimated Course Hours").Select
k = 0
For Each f1 In f.Files
If LCase(Right(f1.Name, 3)) = "xls" Then
'Do Stuff for each file

This is what i tried (slightly different aim), and it diddn't work

Sub Attempt()
Const PATH = "C:\WINDOWS\Desktop\trainers\Training Plans\"
Dim fso As New FileSystemObject
Set fso = New FileSystemObject
Dim i As Integer
i = 0
Do While i < 10
i = i + 1
MsgBox fso.GetFolder(PATH).Files(i).Name
Loop
End Sub

I get a "Invalid procedure call or argument" error

Trying to understand what's happening:

Dim fso as new filesystemobject
Set fso = new filesystemobject

The above both do the same thing other than the first allows you to use VB's code hints?

Firstly could u explain why my code diddn't work, then correct it using as similar as possible syntax, then do the same for the code at the top

What does ReDim do as well?

On a side note, do any of you know of any website that has good examples or tutorial on the use of File System Object?

Thanks in Advance
Lee

ajexpert

Hi Lee,
Try this
Dim fso As New FileSystemObject
Set fso = New FileSystemObject
Dim objfol As Folder
Set objfol = fso.GetFolder(PATH)
Dim objfl
Set objfl = objfol.Files
Dim i As Integer
i = 0
For Each objfl In objfol.Files
If i > 10 Then
Exit For
End If
MsgBox objfl.Name
i = i + 1
Next

ASKER CERTIFIED SOLUTION

aelatik

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aelatik

Meaning of Redim :

The ReDim statement is used to size or resize a dynamic array that has already been formally declared using a Private, Public, or Dim statement with empty parentheses (without dimension subscripts). You can use the ReDim statement repeatedly to change the number of elements and dimensions in an array.

src : http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsstmredim.asp

SOLUTION

ajexpert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave

Firstly you need a reference to the Scripting type library (Scrrun.Dll) tobe able to use early binding

Dim fso As New FileSystemObject

Secondly, you don't need the
Set fso = New FileSystemObject
as you have already created the object with the Dim line above

Then you should use the Folder and File objects to retrieve the files

Sub Attempt()
Const PATH = "C:\WINDOWS\Desktop\trainers\Training Plans\"
Dim fso As New FileSystemObject
Dim F As Folder, Fi As File
Dim i As Integer
Set F = fso.GetFolder(PATH)
i = 0
For Each Fi In F.Files
If i = 10 Then Exit For
i = i + 1
MsgBox Fi.Name
Next
End Sub

Cheers

Dave

pitmanromford

ASKER

In all your examples you create both folder and files objects - why is this?

What is preventing you from going:
{Loop}
i=i+1
fso.getfolder("C:\Test").Files(i).Name
{Loop}

I notice it says Key in Files paramaters other than Index, is this the difference, if so what does this mean?

Can you only use For Each {} in {} to accomplish this goal?

SOLUTION

Dave

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aelatik

Why don't you use native VBA functions to do this ?

Sub Attempt()
Dim F, CNT
CNT = 0
F = Dir("c:\windows\*.*")
Do Until F = "" Or CNT = 10
If LCase(Right(F, 4)) = ".xls" Then MsgBox "This is an excel file !"
CNT = CNT + 1
F = Dir()
Loop
End Sub

aelatik

Or even better, just get a list of Excel files only :

Sub Attempt()
Dim F, CNT
CNT = 0
F = Dir("C:\WINDOWS\Desktop\trainers\Training Plans\*.xls")
Do Until F = "" Or CNT = 10
MsgBox "This is an excel file !"
CNT = CNT + 1
F = Dir()
Loop
End Sub

pitmanromford

ASKER

I'm using file system object because i wanted to use an "object" - i'm new to VB and this is the first object i've "needed" to use. - but nice alternative

Your help so far has been excelent, and i've been able to implement it with confidence in my vba macro.

Recapping my last questions to aid my understanding:

So if
{dim fso as new filesystemobject}
means that you dont need to do
{Set fso = New FileSystemObject},
when would you ever need to use
{Set fso = New FileSystemObject}?

What's the paramater key below used for, and how can it be used?
GetFolder("C:\").Files(paramater).Name

Cheers
Lee

SOLUTION

drfunjohn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pitmanromford

ASKER

Thank you all for giving me the understanding I needed
Out of this I see it as curtious to let you know where the points are going, in no particular order:

ajexpert mainly for your redim example
aelatik for the first solution i could understand :)
brettdj for expanding on things with some tidy code
drfunjohn for explaining early and late binding

Cheers
Lee

drfunjohn

I have only one extra comments about Parameters in the
Files(Parameter) or Files.Item(Parameter)
Parameter is name of file
Files("readme.txt") and this return File object what has "readme.txt" Name.

Good Luck!