Use VBA to install Excel Microsoft Scripting Runtime Reference

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,106
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...

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

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Mentor82

Board Regular
Joined
Dec 30, 2018
Messages
203
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 :).

Code:
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

Board Regular
Joined
Dec 30, 2018
Messages
203
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 :)
Code:
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

New Member
Joined
Mar 16, 2018
Messages
29
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!

Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,090,307
Messages
5,413,695
Members
403,496
Latest member
chamshop

This Week's Hot Topics

Top