Use VBA to install Excel Microsoft Scripting Runtime Reference

[jbuist]

Hi all! I'm trying to activate the Microsoft Scripting Runtime Reference within my code as I'm using FSOs in other parts of the code. I don't get any errors, but it's not activating the reference either. The path below is my actual path. Any help is appreciated.

Thanks in advance!

Sub Activate_Scripting_Runtime()
On Error Resume Next
Application.VBE.ActiveVBProject.References.AddFromFile "C:\Windows\SysWOW64\scrrun\dll"
 
End Sub

 

[Domenic]

You didn't say which line is causing the error, but since you're using late binding you'll need to replace the constant ForWriting with the actual value, which is 2...

Set stream = fso.OpenTextFile("Q:\TEST.txt", 2, True)

 

[Mentor82]

Check it out now. I'm sorry bit I currently cannot debug it because I do not have currently my laptop with me and I'm writing the code on a mobile .

Sub COPY_FROM_EXCEL_AND_PASTE_TEXT_INTO_A_TXT_FILE_2()
Range("A1").Select
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
   
   Const ForWriting = 2
   Dim fso As Object
   Set fso = CreateObject("Scripting.FileSystemObject")
   Dim stream As Object
 
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
   
   'Create a TextStream.
   Set stream = fso.OpenTextFile("Q:\TEST.txt", ForWriting, True)
 
   CellData = vbnullstring
 
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = cstr(ActiveCell(i, j))
         stream.Write CellData
      Next j
   Next i
 
   stream.Close
 
End Sub

 

[Mentor82]

Hi again,
There's also anather method to write data as string from excel to txt file. Check it out - it's simpler in my opinion

Sub WriteExcelDataToTextFile()
Dim strFile_Path As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long
strFile_Path = "Q:\TEST.txt"
LastCol =ActiveSheet.UsedRange.Columns.Count
LastRow =ActiveSheet.UsedRange.Rows.Count
Open strFile_Path For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = cstr(ActiveCell(i, j))
         Write [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , CellData
      Next j
 Next i
 Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
End Sub

 

[jbuist]

Here's the entire code. Still not having success without turning on Scripting Runtime, which I'd like to incorporate turning on into the code...unless there's another way (binding?). Thanks as always!

[B]'THIS CODE CREATES A NEW TXT FILE[/B]
Sub Create_A_New_Text_File_1()
Dim sFilePath As String
Dim fileNumber As Integer
 
sFilePath = "Q:\TEST.txt"
 
[B]'Assign a unique file number[/B]
fileNumber = FreeFile
 
Open sFilePath For Output As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNumber]#fileNumber[/URL] 
 
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=fileNumber]#fileNumber[/URL] 
 
End Sub
 
[B]'THIS CODE WORKS TO COPY FROM EXCEL AND PASTE TEXT INTO A TXT FILE[/B]
Sub COPY_FROM_EXCEL_AND_PASTE_TEXT_INTO_A_TXT_FILE_2()
Range("A1").Select
   Dim FilePath As String
   Dim CellData As String
   Dim LastCol As Long
   Dim LastRow As Long
 
   Dim fso As FileSystemObject
   Set fso = New FileSystemObject
   Dim stream As TextStream
 
   LastCol = ActiveSheet.UsedRange.Columns.Count
   LastRow = ActiveSheet.UsedRange.Rows.Count
   
[B]   'Create a TextStream.[/B]
   Set stream = fso.OpenTextFile("Q:\TEST.txt", ForWriting, True)
 
   CellData = ""
 
   For i = 1 To LastRow
      For j = 1 To LastCol
         CellData = (ActiveCell(i, j).Text)
         stream.WriteLine CellData
      Next j
   Next i
 
   stream.Close
 
End Sub
 
 
Sub Format_3()
[B]‘Format the column to TEXT[/B]
    Columns("A:A").Select
    Selection.NumberFormat = "@"
End Sub
 
[B]‘THIS CODE WORKS TO COPY TEXT TO EXCEL AND PASTES INTO APPLICABLE CELLS[/B]
Sub CopyTextFile_4()
Dim oFso: Set oFso = CreateObject("Scripting.FileSystemObject")
Dim oFile: Set oFile = oFso.OpenTextFile("Q:\TEST.txt", 1)
Dim lines As Variant
lines = Split(oFile.ReadAll, vbCrLf)
oFile.Close
ThisWorkbook.Sheets(1).Range("A1").Resize(UBound(lines)).Value = Application.Transpose(lines)
 
[B]'Delete text file[/B]
With New FileSystemObject
    If .FileExists("Q:\TEST.txt") Then
        .DeleteFile "Q:\TEST.txt"
    End If
End With
 
 
End Sub