How do I use FileSystemObject in VBA?

Alex Gordon picture Alex Gordon · Jul 13, 2010 · Viewed 350k times · Source

Is there something that I need to reference? How do I use this:

Dim fso As New FileSystemObject
Dim fld As Folder
Dim ts As TextStream

I am getting an error because it does not recognize these objects.

Answer

Robert Mearns picture Robert Mearns · Jul 13, 2010

Within Excel you need to set a reference to the VB script run-time library. The relevant file is usually located at \Windows\System32\scrrun.dll

  • To reference this file, load the Visual Basic Editor (ALT+F11)
  • Select Tools > References from the drop-down menu
  • A listbox of available references will be displayed
  • Tick the check-box next to 'Microsoft Scripting Runtime'
  • The full name and path of the scrrun.dll file will be displayed below the listbox
  • Click on the OK button.

This can also be done directly in the code if access to the VBA object model has been enabled.

Access can be enabled by ticking the check-box Trust access to the VBA project object model found at File > Options > Trust Center > Trust Center Settings > Macro Settings

VBA Macro settings

To add a reference:

Sub Add_Reference()

    Application.VBE.ActiveVBProject.References.AddFromFile "C:\Windows\System32\scrrun.dll"
'Add a reference

End Sub

To remove a reference:

Sub Remove_Reference()

Dim oReference As Object

    Set oReference = Application.VBE.ActiveVBProject.References.Item("Scripting")

    Application.VBE.ActiveVBProject.References.Remove oReference
'Remove a reference

End Sub