Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Use VBA to install Excel Microsoft Scripting Runtime Reference
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP
    Join Date
    Mar 2004
    Location
    Canada
    Posts
    18,884
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    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 by Domenic; Aug 15th, 2019 at 06:44 PM.

  2. #12
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    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

  3. #13
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    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 #1 
    For i = 1 To LastRow
          For j = 1 To LastCol
             CellData = cstr(ActiveCell(i, j))
             Write #1 , CellData
          Next j
     Next i
     Close #1 
    End Sub

  4. #14
    New Member
    Join Date
    Mar 2018
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use VBA to install Excel Microsoft Scripting Runtime Reference

    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:
    'THIS CODE CREATES A NEW TXT FILE
    Sub Create_A_New_Text_File_1()
    Dim sFilePath As String
    Dim fileNumber As Integer
     
    sFilePath = "Q:\TEST.txt"
     
    'Assign a unique file number
    fileNumber = FreeFile
     
    Open sFilePath For Output As #fileNumber 
     
    Close #fileNumber 
     
    End Sub
     
    'THIS CODE WORKS TO COPY FROM EXCEL AND PASTE TEXT INTO A TXT FILE
    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
       
       'Create a TextStream.
       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()
    ‘Format the column to TEXT
        Columns("A:A").Select
        Selection.NumberFormat = "@"
    End Sub
     
    ‘THIS CODE WORKS TO COPY TEXT TO EXCEL AND PASTES INTO APPLICABLE CELLS
    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)
     
    'Delete text file
    With New FileSystemObject
        If .FileExists("Q:\TEST.txt") Then
            .DeleteFile "Q:\TEST.txt"
        End If
    End With
     
     
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •