Filesystem object not defined

[Carthesis]

Right, I'm bloody confused.

I have a bit of code that takes a folder path (that is either hard-coded or pulled in from a form/excel worksheet cell/wherever), and then lists all the files in that folder and in any subfolders.

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)

' lists information about the files in SourceFolder - various information can be selected
'
' example: ListFilesInFolder "C:\FolderName\", True
'
' requires follwoing references:
    ' Visual Basic for Applications
    ' Microsoft Excel 11.0 Object Library
    ' Microsoft DAO 3.6 Object Library
    ' Microsoft ActiveX Data Object Library 2.1
    ' OLE Automation

Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File

Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder(SourceFolderName)

       ' display file properties and paste into cells in an Excel worksheet:
        'r = Range("A65536").End(xlUp).Row + 1

    ' pull the file information into named string variables for use with Access
    ' (for use with the Amey PhotoDB, only need Path+Filename and Taken On date)
        Dim Filepath As String
        Dim DateCreated As Date

    For Each FileItem In SourceFolder.Files
 
       ' display file properties and paste into cells in an Excel worksheet:
            'Cells(r, 1).Formula = FileItem.Path
            'Cells(r, 2).Formula = FileItem.Size
            'Cells(r, 3).Formula = FileItem.Type
            'Cells(r, 4).Formula = FileItem.DateCreated
            'Cells(r, 5).Formula = FileItem.DateLastAccessed
            'Cells(r, 6).Formula = FileItem.DateLastModified
            'Cells(r, 7).Formula = FileItem.Attributes
            'Cells(r, 8).Formula = FileItem.ShortPath & FileItem.ShortName
            'r = r + 1 ' next row number

    ' insert relevant file properties into predefined variables:
        Filepath = FileItem.path
        DateCreated = FileItem.DateCreated

        Debug.Print Filepath & ", created on: " & DateCreated

    Next FileItem

    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.path, True
        Next SubFolder
    End If
    

    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing

'Columns("A:H").AutoFit
'ActiveWorkbook.Saved = True

End Sub

This bit of code works perfectly in Excel, and populates the worksheet with the various bits of information. However, exactly the same code fails to work in Access. OK, OK, it's not IDENTICAL code, but the bit I've changed is the bit where the various attributes are determined, and that's not the bit that falls over. You can see where the bits that write the attributes into worksheet cells are commented out and the other code has been added.

Instead, what I get when I run the code in Access is:

"Compile error: User-defined type not defined"

It's crapping out on this:

Dim FSO As Scripting.FileSystemObject

Again, don't know why. I've checked all the references and they're all there. i've even gone as far as adding 'Microsoft Excel 11.0 Object Library' into the Access DB to see if that works, and it doesn't. Checked the VBA helpfile, and the code as written should work as far as I can see.

Help?

 

[PHV]

Add a reference to Microsoft Scripting Runtime

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Carthesis]

Oh for... *ahem*

PHV - thanks for providing me with such a PAINFULLY obvious solution.

I did, in fact, check the references as you can see from the comments at the top of the code, but I obviously missed the MS Scripting Runtime - must have been hiding off the bottom of the list.

I am a fool.

Many thanks.

 

[osx99]

Is it possible to add the required references from within VBA code?

If yes, what syntax is required to load say the Microsoft Scripting Runtime which I think is scrrun.dll?

 

[fumei]

VBProject
.References.AddFromFile "c:\windows\32\scrrun.dll"

Gerry