Not registered yet?
Register now! It is easy and done in 1 minute and gives you access to special discounts and much more!
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.
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
Microsoft Scripting Runtime
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
Trust access to the VBA project object model
To add a reference:
'Add a reference
To remove a reference:
Dim oReference As Object
Set oReference = Application.VBE.ActiveVBProject.References.Item("Scripting")
'Remove a reference
robert thank you very much
What do you do on a Mac?
Isn't there a way to do this via command line, as in including a library or something?
Is there a way to AUTO tick the check-box 'Microsoft Scripting Runtime'? for ALL Excel files? I'm using Excel 2016
Have a look at the accepted answer to this question. stackoverflow.com/questions/9879825/…
After importing the scripting runtime as described above you have to make some slighty modification to get it working in Excel 2010 (my version). Into the following code I've also add the code used to the user to pick a file.
Dim intChoice As Integer
Dim strPath As String
' Select one file
Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
' Show the selection window
intChoice = Application.FileDialog(msoFileDialogOpen).Show
' Get back the user option
If intChoice <> 0 Then
strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Dim FSO As New Scripting.FileSystemObject
Dim fsoStream As Scripting.TextStream
Dim strLine As String
Set fsoStream = FSO.OpenTextFile(strPath)
Do Until fsoStream.AtEndOfStream = True
strLine = fsoStream.ReadLine
' ... do your work ...
Set FSO = Nothing
Hope it help!
After adding the reference, I had to use
Dim fso As New Scripting.FileSystemObject
In excel 2013 the object creation string is:
Set fso = CreateObject("Scripting.FileSystemObject")
instead of the code in the answer above:
to be slightly more explicit, I normally see it as Dim fso As Object
Dim fso As Object
These guys have excellent examples of how to use the filesystem object http://www.w3schools.com/asp/asp_ref_filesystem.asp
This code snippet demonstrates the use of the FSO in ASP/IIS not excel