Exporting sets of rows from Excel to txt files

Question

My objective is to take 99 rows in groups of 10 containing 9 rows each and 2 columns to make dat(text) files.

This can be achieved using loops by breaking the writing operation at 9th row and then resuming at 10th row.

I want the first 10 rows from Excel in one dat file, next 10 in another and so on.

When I run the following code, nothing happens. When I debug, it skips the loop structure. When I run it for just one file containing all the rows it runs perfectly.

0.8641  0.8654
0.6605  0.8269
0.5828  0.8269
0.9985  1.0000
0.7527  0.9423
0.6641  0.9423
1.1329  1.1346
0.8756  1.0962
0.7590  1.0769
0.9174  0.8836
0.7557  0.8443
0.5986  0.8164
0.9984  1.0000
0.8085  0.9656
0.6809  0.9443
1.0972  1.1328
0.8680  1.0902
0.7453  1.0623
0.8665  0.8714
0.6385  0.8429
0.5398  0.8143

Private Sub CommandButton1_Click()

' Variable declaration

Dim FilePath As String
Dim CellData As String
Dim Folder As String
Dim del As String
Dim LastCol As Long
Dim LastRow As Long
Dim FileNum As Integer
Dim count As Integer
Dim counter As Integer
Dim i, j, k As Integer

' Getting the last row and column from the workbook

LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Foler = "C:\TRNSYS1832-bit\TRNSYS18\MyProjects\Main project\Chiller and ice storage data\Excel\"

del = vbTab

count = 9

conuter = 1

For k = 1 To k = LastRow

    FilePath = Folder & "Data" & counter & ".txt"        ' This is the final name example - Data1.txt

    FileNum = FreeFile ' FreeFile so that I don't have to associate it with a specific number

    Open FilePath For Output As #FileNum    ' Opening the file for writing 

    Print #FileNum, "-5" & vbTab & "0" & vbTab & "5" & " !Chilled water leaving temperature (C)"
    Print #FileNum, "35" & vbTab & "45" & vbTab & "50" & " !Cooling water entering temperature (C)"

    For i = 1 To count

        For j = 1 To LastCol

            If j = LastCol Then
                CellData = CellData & Round(Cells(i, j).Value, 4)
            Else
                CellData = CellData & Round(Cells(i, j).Value, 4) & del
            End If

        Next j

        Print #FileNum, CellData
        CellData = ""

    Next i

    Close #FileNum

    i = count + 1
    count = count + 9
    counter = counter + 1

Next k

End Sub

Side notes: [`Round`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function) is bankers rounding, you may want `Application.Round`. Also, see [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for a better approach to find the last row/column. — BigBen, Feb 06 '20 at 17:21

score 0 · Answer 1 · edited Feb 06 '20 at 23:23

0

Could it be as simple as the misspelling in the folder in the following line of code?

Foler = "C:\TRNSYS1832-bit\TRNSYS18\MyProjects\Main project\Chiller and ice storage data\Excel\"

Looking at the rest of the code, Folder is not set to anything.

This line also has a spelling mistake:

conuter = 1

The first time through, counter = 0, it gets set to 1 the first time through the loop.

Another error I think:

For k = 1 To k = LastRow

Should be

For k = 1 To LastRow

edited Feb 06 '20 at 23:23

Miles Fett

711
4
17

answered Feb 06 '20 at 19:55

ACCtionMan

511
1
3
12

Thanks @ACCtionMan for your response. I understand the point about k = 1 to k= last row. I changed that and now I have the code is running and i am getting the required files, although there is still some problem in the logic as I'm getting too many files...but I'll fix it. – Tushar Sharma Feb 07 '20 at 09:41

Exporting sets of rows from Excel to txt files

1 Answers1