Solved

File System Object

Posted on 2004-04-24
13
1,438 Views
Last Modified: 2008-07-09
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
0
Comment
Question by:pitmanromford
  • 4
  • 3
  • 2
  • +2
13 Comments
 
LVL 14

Expert Comment

by:ajexpert
Comment Utility
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

0
 
LVL 14

Accepted Solution

by:
aelatik earned 125 total points
Comment Utility
This does not work, the DIM statement already did create the object so you don't need to set is anymore.

' Dim fso as new filesystemobject
' Set fso = new filesystemobject

------------------------------------------------------------------------------------------------------------------------

A very good reference for FSO is here : http://www.sloppycode.net/fso/

------------------------------------------------------------------------------------------------------------------------

The correct way the handle is like :

Sub Attempt()
    Const PATH = "C:\WINDOWS\Desktop\trainers\Training Plans\""
    Dim FSO As New Scripting.FileSystemObject
    Dim FSO_FILES As FILES
    Dim FSO_FOLDER As FOLDER
    Dim FSO_FILE As FILE
    Set FSO_FOLDER = FSO.GetFolder(PATH)
    Set FSO_FILES = FSO_FOLDER.FILES
        For Each FSO_FILE In FSO_FILES
            MsgBox FSO_FILE.Name
        Next
    Set FSO = Nothing
    Set FSO_FOLDER = Nothing
    Set FSO_FILES = Nothing
End Sub
0
 
LVL 14

Expert Comment

by:aelatik
Comment Utility
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
0
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 125 total points
Comment Utility
Hi Lee,
  ReDim allocates memory dynamically for array.

  I will try to explain you with example

      Dim DynArr(50)
    ' The above array this stores status of 51 students
   
   
    ReDim DynArr(51)
    ' This will store 52 students, basically redimensions the already dimensioned array defined.
0
 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
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
0
 
LVL 2

Author Comment

by:pitmanromford
Comment Utility
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?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 125 total points
Comment Utility
You are correct that the Folder object is redundant - you can go straight to files with

Sub Attempt()
    Const PATH = "C:\Test\"
    Dim fso As New FileSystemObject
    Dim i As Integer, F As Files, G
    Set F = fso.GetFolder(PATH).Files
    For Each G In F
        If i = 10 Then Exit For
        i = i + 1
        MsgBox G.Name
    Next
End Sub

Cheers

Dave
0
 
LVL 14

Expert Comment

by:aelatik
Comment Utility
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
0
 
LVL 14

Expert Comment

by:aelatik
Comment Utility
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
0
 
LVL 2

Author Comment

by:pitmanromford
Comment Utility
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
0
 

Assisted Solution

by:drfunjohn
drfunjohn earned 125 total points
Comment Utility
Visual Basic support two types of creating and using of Objects.
First is "Late bindings" : as
'---------------
Dim fs as Object
Set fs = CreateObject ("Scripting.FileSystemObject")
'---------------
So Compilator cannot know what type of object is variable fs. And only during Runtime it can be known.

Second type (sorry, it realy fist type) is early bindings, as
'-----------------
Dim fs as FileSystemObject
Set fs = New FileSystemObject
'------------------
In this case compilator know what type of variable and perform all needed checks during comilation, for example:
fs.GetFolder (PATH)
compilator will check if fs has this method.

early Bindings is Prefer. Its more quikly! And you can find errors during compilation.

Difference from New in the Set operator or in the Dim operator hide in the Visual basic.
If you use New  in the Dim operator object is not realy created, it will be created only in the first reference to this vsriable:
Dim fs as New FileSystemObject 'not created yet
Dim .....
......
fs.GetFolder .... 'fs created just befor call GetFolder function
If you use multithreaded application there are hard to know when it will be happen.

If you use New in the Set operator object will be created in the this line:

Dim fs as FileSystemObject 'not created
Set fs = New FileSystemObject 'created

Prefer way to use is second when you place all your "Set .. = New ..." operators in the one block to initializate Application and dysplay to user progress bar of this action.

About parameter key in the
GetFolder("C:\").Files(paramater).Name
I don't know!
There are no any example or description in the MSDN.
In the VisualBasic you need use only one way is  "Each ... In ..." expression to access to each file in the folder through FileSystemObject of Scripting  lib.
Good luck
0
 
LVL 2

Author Comment

by:pitmanromford
Comment Utility
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
0
 

Expert Comment

by:drfunjohn
Comment Utility
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!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now