CODEWITHSUNDEEP
excelvba
farzaneh abolqasemi
farzaneh abolqasemi

Reputation: 97

Export each row of spreadsheet to separated .txt file

I've a spreadsheet with 500 rows and 4 columns. I want to save each row to a separated TXT file and separate each column of rows by comma.

an example is :

spreadsheet data:

column1 column2 column3 column4

 id     title    body   personid

txt file : (should be)

id,title,body,personid

for each row.

Upvotes: 0

Views: 4572

Answers (1)

keong kenshih
keong kenshih

Reputation: 524

Try this.

Sub SaveWorksheet()
Dim MyWorkbook As Workbook
Dim MyDataWorksheet As Worksheet

Set MyWorkbook = Workbooks(ActiveWorkbook.Name)
Set MyDataWorksheet = MyWorkbook.Sheets("Data")

Dim OutputFile As String
Dim CellValue As String
Dim CurrentRow As Long
Dim CurrentCol As Long
Dim CurrentCharacter As Long
Dim LastRow As Long
Dim MyString As String

LastRow = MyDataWorksheet.Cells(Rows.Count, "a").End(xlUp).Row

For CurrentRow = 2 To LastRow
'C:\Users\lengkgan\Desktop\Testing\sample.txt
OutputFile = "C:\Users\lengkgan\Desktop\Testing\sample" & CurrentRow & ".txt"

Open OutputFile For Output As #1

    CellValue = MyDataWorksheet.Cells(CurrentRow, 1).Value & "," & MyDataWorksheet.Cells(CurrentRow, 2).Value & "," & MyDataWorksheet.Cells(CurrentRow, 3).Value & "," & MyDataWorksheet.Cells(CurrentRow, 4).Value
    'Write #1, CellValue
    Print #1, CellValue

Close #1

Next CurrentRow

MsgBox "Done"

End Sub

Upvotes: 3

Related Questions