Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    file system object (2000)

    The following code works fine until it comes to the line:
    Set filFile =.....

    Where is my error?

    <pre>Sub GetFile()

    Dim fsoObj As Scripting.FileSystemObject
    Dim fldFolder As Scripting.Folder
    Dim filFile As Scripting.File
    Dim strPath As String
    Dim BaseWd As String
    Dim i As Integer

    BaseWd = ActiveSheet.Range("MyWord").Value
    strPath = ThisWorkbook.Path & "Images" & BaseWd & ""

    Set fsoObj = New Scripting.FileSystemObject
    Set fldFolder = fsoObj.GetFolder(strPath)
    If fldFolder.Files.Count < 1 Then
    MsgBox "No files were found."
    GoTo Shutdown
    End If

    For Each filFile In fldFolder.Files
    MsgBox filFile.Name
    Next filFile

    i = i + 1

    ''''''''''''''''''''''''''''''''

    Set filFile = fldFolder.Files(i)

    ''' The above line returns an error:
    ''' Invalid procedure call or argument
    ''''''''''''''''''''''''''''''''''''''''


    'more code here

    Shutdown:
    Set fsoObj = Nothing
    Set fldFolder = Nothing
    Set filFile = Nothing
    Exit Sub

    ErrHandler:

    End Sub
    </pre>


  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: file system object (2000)

    Paul,
    The Files.Item() property requires a key not an index (if you type <code>Files(</code> the tooltip should indicate Key, not Index) so you need to use the file name to access it in this way - i.e. it acts more like a dictionary than a typical collection.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file system object (2000)

    To iterate a Files collection, you'd probably be better off using a For Each...Next construct:
    <code>
    [VBScript]
    Function ShowFolderList(folderspec)
    Dim fso, f, f1, fc, s
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFolder(folderspec)
    Set fc = f.Files
    For Each f1 in fc
    s = s & f1.name
    s = s & ""
    Next
    ShowFolderList = s
    End Function
    </code>
    Alan

  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file system object (2000)

    Rory
    Thanks. I think I understand it better now. I'll have to find a workaround since I don't know the file name in advance, only the index.

    Alan
    My OP does iterate thru the folder and yields the name of all files. I only included it to show that the files were indeed present.

    I think I will try using the vba Dir function
    thanks

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: file system object (2000)

    OK, understood now. How about something like this?
    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Function</font color=blue> FileName(folderspec, idx)
    <font color=blue>Dim</font color=blue> fso, f, f1, fc, s, i
    <font color=blue>Set</font color=blue> fso = CreateObject("Scripting.FileSystemObject")
    <font color=blue>Set</font color=blue> f = fso.GetFolder(folderspec)
    <font color=blue>Set</font color=blue> fc = f.Files
    FileName = "File not found for index value " & idx
    i = 0
    <font color=blue>For</font color=blue> <font color=blue>Each</font color=blue> f1 <font color=blue>In</font color=blue> fc
    i = i + 1
    <font color=blue>If</font color=blue> i = idx <font color=blue>Then</font color=blue> FileName = f1.Name: <font color=blue>Exit</font color=blue> <font color=blue>For</font color=blue>
    <font color=blue>Next</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>Function</font color=blue>

    s = FileName("C:Ultramedia", 8)
    WScript.Echo s</font color=black></code></div hiblock>
    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •