CODEWITHSUNDEEP
excelvba
Avi
Avi

Reputation: 1

Creating text file from excel row values

I am trying to write code which creates the text file at the specified location and the input is cel value

Sub Create()

Dim myPathTo As String
myPathTo = "d:\users\"
Dim myFileSystemObject As Object
Set myFileSystemObject = CreateObject("Scripting.FileSystemObject")
Dim fileOut As Object
Dim myFileName As String

Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).row
Dim i As Long

    For i = 2 To lastRow
        If Not IsEmpty(Cells(i, 1)) Then
            myFileName = Cells(i, 4) & ".txt"
            Set fileOut = myFileSystemObject.opentextfile(myFileName, 8, True)
            fileOut.Write Cells(i, 8)
            fileOut.Close
        End If
    Next

Set myFileSystemObject = Nothing
Set fileOut = Nothing
End Sub

No error is shown. Don't understand why it is not working

Upvotes: 0

Views: 66

Answers (1)

Mikku
Mikku

Reputation: 6664

This will work:

Sub Create()

Dim myPathTo As String
myPathTo = "d:\users\"
Dim myFileSystemObject As Object
Set myFileSystemObject = CreateObject("Scripting.FileSystemObject")
Dim fileOut As Object
Dim myFileName As String

Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Dim i As Long

    For i = 2 To lastRow
        If Not IsEmpty(Cells(i, 1)) Then
            myFileName = Cells(i, 4) & ".txt"
            Set fileOut = myFileSystemObject.OpenTextFile(myPathTo & myFileName, 8, True)
            fileOut.Write Cells(i, 8)
            fileOut.Close
        End If
    Next

Set myFileSystemObject = Nothing
Set fileOut = Nothing


End Sub

You were not using the Full Path to save the File. Now you will see all the text files in D:\Users\

Upvotes: 1

Related Questions