CODEWITHSUNDEEP
excelvba
Amjad Hussain
Amjad Hussain

Reputation: 1

Open and write in txt file from Excel macro

I need help with code, I want my macro to open a .txt file and write only 1-9 rows(copy from Excel) in one txt file and then open next .txt file and write 9-18 and so on until data in the excel rows is finished under each column. Below code only paste 1-9 rows from Excel to Txt file and stop. So it means if I have 36 rows in Excel(A1-A36) it should create 3 txt files each with 9 rows only.

Sub env_gen()
    Dim filename As String
    Dim linetext As String
    Dim my_range As Range
    filename = ThisWorkbook.Path & "\Monday123" & ".txt"
    Open filename For Output As #1
    Set my_range = Worksheets("sheet1").Range("A1:A9")
    For i = 1 To 9
        For j = 1 To 1
            linetext = IIf(j = 1, "", linetext & ",")
        Next j
        Print #1, linetext
    Next i
    Close #1

   MsgBox ("File is created")

End Sub

Upvotes: 0

Views: 4312

Answers (1)

Toddleson
Toddleson

Reputation: 4457

To iterate over a range, you can use a loop and put the index inside the range address. Eg. For i = 1 to 9: Cells(i, 1) = .... This loop can also use the ranges size as a parameter to ensure you loop over the whole range and only the range (no extra). Eg. For i = 1 To my_range.Rows.Count.

When looping within a loop, you can also use the outer loops index as a parameter for the inner loop. Eg. For i = 1 to 36 Step 9: For j = i to i + 8

Here is an example of those techniques implemented with your code:

Sub env_gen()
    Dim filename As String
    Dim linetext As String
    Dim CurrentStep As Long
    
    Dim my_range As Range
    Set my_range = Worksheets("sheet1").Range("A1:A36")
    
    For i = 1 To my_range.Rows.Count Step 9
        CurrentStep = CurrentStep + 1
        
        filename = ThisWorkbook.Path & "\Monday123 (" & CurrentStep & ").txt"
        Open filename For Output As #1
        
        For j = i To i + 8
            linetext = my_range.Cells(i).Text & IIf(j <> i + 8, "", ",")
            Print #1, linetext
        Next j
        
        Close #1
    Next i

   MsgBox ("Files are created")

End Sub

Upvotes: 1

Related Questions