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.
asked Jul 13, 2010 at 0:00
Alex GordonAlex Gordon
57.6k289 gold badges671 silver badges1066 bronze badges
Within Excel you need to set a reference to the VBScript 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.dllfile 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
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
answered Jul 13, 2010 at 10:46
Robert MearnsRobert Mearns
11.8k3 gold badges39 silver badges42 bronze badges
3
In excel 2013 the object creation string is:
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
instead of the code in the answer above:
Dim fs,fname
Set fs=Server.CreateObject("Scripting.FileSystemObject")
answered Jul 31, 2016 at 19:54
1
These guys have excellent examples of how to use the filesystem object http://www.w3schools.com/asp/asp_ref_filesystem.asp
<%
dim fs,fname
set fs=Server.CreateObject("Scripting.FileSystemObject")
set fname=fs.CreateTextFile("c:\test.txt",true)
fname.WriteLine("Hello World!")
fname.Close
set fname=nothing
set fs=nothing
%>
shA.t
16.6k5 gold badges54 silver badges111 bronze badges
answered Jul 13, 2010 at 0:04
Gerald FerreiraGerald Ferreira
1,3492 gold badges23 silver badges44 bronze badges
1
After adding the reference, I had to use
Dim fso As New Scripting.FileSystemObject
answered Feb 15, 2018 at 23:25
thedanottothedanotto
6,9355 gold badges45 silver badges44 bronze badges
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)
Else
Exit Sub
End If
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 ...
Loop
fsoStream.Close
Set FSO = Nothing
answered May 4, 2018 at 16:18
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.
asked Jul 13, 2010 at 0:00
Alex GordonAlex Gordon
57.6k289 gold badges671 silver badges1066 bronze badges
Within Excel you need to set a reference to the VBScript 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.dllfile 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
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
answered Jul 13, 2010 at 10:46
Robert MearnsRobert Mearns
11.8k3 gold badges39 silver badges42 bronze badges
3
In excel 2013 the object creation string is:
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
instead of the code in the answer above:
Dim fs,fname
Set fs=Server.CreateObject("Scripting.FileSystemObject")
answered Jul 31, 2016 at 19:54
1
These guys have excellent examples of how to use the filesystem object http://www.w3schools.com/asp/asp_ref_filesystem.asp
<%
dim fs,fname
set fs=Server.CreateObject("Scripting.FileSystemObject")
set fname=fs.CreateTextFile("c:\test.txt",true)
fname.WriteLine("Hello World!")
fname.Close
set fname=nothing
set fs=nothing
%>
shA.t
16.6k5 gold badges54 silver badges111 bronze badges
answered Jul 13, 2010 at 0:04
Gerald FerreiraGerald Ferreira
1,3492 gold badges23 silver badges44 bronze badges
1
After adding the reference, I had to use
Dim fso As New Scripting.FileSystemObject
answered Feb 15, 2018 at 23:25
thedanottothedanotto
6,9355 gold badges45 silver badges44 bronze badges
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)
Else
Exit Sub
End If
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 ...
Loop
fsoStream.Close
Set FSO = Nothing
answered May 4, 2018 at 16:18
I’m writing a VBA script for a program called «ScorBase». I’m trying to call a subroutine that creates a text file yet I»m encountering this error «506».
this is the code that I’m writhing:
Sub emailFile()
' Declare a TextStream.
Dim stream
'As TextStream
dim fso
Set fso = New FileSystemObject
' Create a TextStream.
Set stream = fso.CreateTextFile("C:\Users\eladt\Desktop\creatFile\Mail.txt", True)
stream.WriteLine "user Email."
stream.WriteLine "Maki"
stream.WriteLine "Nigeri"
stream.WriteLine "Sashimi"
stream.Close
End Sub
asked Mar 29, 2016 at 9:36
8
An much simpler approach without using any external references:
Sub MM_Email_To_File()
Dim FF As Integer
FF = FreeFile
'// The file will be created if it doesn't exist
Open "C:\Users\eladt\Desktop\creatFile\Mail.txt" For Output As #FF
Print #FF, "User Email"
Print #FF, "Maki"
Print #FF, "Nigeri"
Print #FF, "Sashimi"
Close #FF
End Sub
You can check out this MSDN article for more information on I/O operation in VBA.
answered Mar 29, 2016 at 11:02
SierraOscarSierraOscar
17.5k6 gold badges40 silver badges68 bronze badges
If late binding from another application works then there is an issue with the VBA environment your 3rd party application is hosting, possibly it is performing some type of sandboxing.
You don’t need any external libraries to write to a file, you can try to do it natively:
Dim hf As Integer
hf = FreeFile
Open "C:\Users\eladt\Desktop\creatFile\Mail.txt" For Output As #hf
Print #hf, "user Email."
Print #hf, "Maki"
Print #hf, "Nigeri"
Print #hf, "Sashimi"
Close #hf
answered Mar 29, 2016 at 10:59
Alex K.Alex K.
172k30 gold badges264 silver badges288 bronze badges
1
The comments under your original question seem to suggest that Microsoft Scripting Runtime is not available or not installed on your PC. This could be unintentional, but it might also be because IT administrators have limited access to that library in your environment.
Fortunately, there are a number of ways of writing a text file. You may be able to use the ADO library to write a stream..
Just add a Reference to Microsoft ActiveX Data Objects 2.8 Library and then replace your code with:
Sub emailFile()
' Declare an ADO Stream.
Dim stream As ADODB.stream
' Create an ADO Stream.
Set stream = New ADODB.stream
stream.Open
stream.Type = adTypeText
stream.WriteText "user Email.", stWriteLine
stream.WriteText "Maki", stWriteLine
stream.WriteText "Nigeri", stWriteLine
stream.WriteText "Sashimi", stWriteLine
stream.SaveToFile "C:\Users\eladt\Desktop\creatFile\Mail.txt", adSaveCreateOverWrite
stream.Close
End Sub
answered Mar 29, 2016 at 10:22
ThunderFrameThunderFrame
9,3522 gold badges29 silver badges60 bronze badges
|
Объявление переменной типа File, FileSystemObject |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Sub Content_for_etfs_convert()
Kill "D:option programsотборIN*.*"
Kill "D:option programsотборOUT*.*"
Dim fso
Set fso = CreateObject("scripting.filesystemobject"): fso.CopyFolder "C:UsersАдминистраторDownloadsStocksrcdistdownloads", "D:option programsотборIN"
Set fso = CreateObject("Scripting.FileSystemObject")
cPath = fso.GetParentFolderName(ThisWorkbook.FullName)
cPathIn = cPath & "In"
cPathOut = cPath & "Out"
Set Folder = fso.GetFolder(cPathIn)
For Each File In Folder.Files
If fso.GetExtensionName(File.Name) = "txt" Then
With fso.OpenTextFile(cPathIn & File.Name, 1, True)
cIn = .ReadAll
.Close
End With
cOut = vbCrLf & "DATE"
arrL = Split(cIn, vbLf)
For i = LBound(arrL) To UBound(arrL)
If Len(arrL(i)) > 0 Then
arrD = Split(arrL(i), ",")
arrD(0) = Right(arrD(0), 2) & "." & Mid(arrD(0), 5, 2) & "." & Left(arrD(0), 4)
For j = 1 To 4
cnum = Replace(arrD(j), ".", ",")
arrD(j) = Replace(CStr(Round(CDbl(cnum), 2)), ",", ".")
Next
cnum = Replace(arrD(6), ".", ",")
arrD(6) = Replace(CStr(Round(CDbl(cnum), 0)), ",", ".")
cOut = cOut & vbCrLf & Join(Array(arrD(0), arrD(1), arrD(2), arrD(3), arrD(4), arrD(6)), vbTab)
End If
Next
With fso.OpenTextFile(cPathOut & File.Name, 2, True)
.Write cOut
.Close
End With
End If
Next
MsgBox "Ok"
End Sub
Sub replaceTxts()
Dim fso As New FileSystemObject, curFolder As Folder, curFile As File
folderPath = "D:option programsотборOUT"
Set curFolder = fso.GetFolder(folderPath)
For Each curFile In curFolder.Files
If Right(curFile.Path, 4) = ".txt" Then
curFile.copy Replace(curFile.Path, ".txt", ".csv")
curFile.Delete
End If
Next curFile
End Sub