Scripting FileSystemObject creates Runtime 429 error on Windows

Question

Am I missing a reference object? Trying to retrieve a list of files in a directory and have them show in a sheet.

Also, would it be possible to get the directory location from a specific cell in the file?

Sub LoopThroughFiles()

Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Integer

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFolder = oFSO.GetFolder("C:\VBA Folder")

For Each oFile In oFolder.Files

    Cells(i + 1, 1) = oFile.Name

    i = i + 1

Next oFile

End Sub

Here:

Set oFSO = CreateObject("Scripting.FileSystemObject")

Runtime error 429 on a Windows machine.

@JohnnyMopp lol Thanks! That is why I am thinking maybe I'm missing a reference or DLL or something. — Bill Littlejohn
– Bill Littlejohn, Commented Nov 22, 2019 at 18:01
As far as I know, Scripting.FileSystemObject should be available on all Windows machines, so I don't know what you are missing. I tested by copy-and-pasting into a new spreadsheet and it worked oob. Maybe you need elevated permissions?? — 001
– 001, Commented Nov 22, 2019 at 18:06
@JohnnyMopp I will check that. Maybe open the file as admin? — Bill Littlejohn
– Bill Littlejohn, Commented Nov 22, 2019 at 18:08
I bet it's removing the Hungarian Notation that fixed it! ;-) — Mathieu Guindon
– Mathieu Guindon, Commented Nov 22, 2019 at 19:10

Mathieu Guindon · Accepted Answer · 2019-11-22 19:42:31Z

1

There's little to no reason at all to late-bind the Scripting library - it's present (same identical version) on every single Windows box out there, and won't work on a Mac whether you late-bind it or not.

Tools/References, add the "Microsoft Scripting Runtime" library to your project, and then declare the actual data types for your object variables; you'll get compile-time validation and intellisense/autocompletion (good-bye error 438!) for all member calls.

Early binding is your friend. Declare As Scrpiting.FileSystemObject, and then just New up the object instead of hitting the registry to resolve the "Scripting.FileSystemObject" ProgID with CreateObject:

Set fso = New Scripting.FileSystemObject

Or have a With block whithold the object reference, and then you don't even need a local variable for it:

With New Scripting.FileSystemObject
    '....
End With

edited Nov 22, 2019 at 19:42

answered Nov 22, 2019 at 19:12

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Scripting FileSystemObject creates Runtime 429 error on Windows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related