Fetching data from txt files and creating excel array

[pban92]

1. I have 960 .tur files each containing a single value.
2. The txt files are named as case001.tur, case002.tur,.....,case960.tur
2. I would like to make 10 excel files each containing 8x12 cells/array out of every 96 files over 960 files.

The following scripts are meant to that but with no success.

Sub ReadTextFiles()          ' Make reference to library:     ' Tools -> References -> Microsoft Scripting Runtime      Dim iRow As Integer, iCol As Integer     Dim iBooksCounter As Integer, iTextCounter As Integer     Dim fso As New FileSystemObject     Dim txt As TextStream, aFile As File     Dim sContent As String, wkb As Workbook          For Each aFile In fso.GetFolder(ThisWorkbook.Path).Files                  If fso.GetExtensionName(aFile.Name) = "tur" Then                          If iTextCounter Mod 96 = 0 Or iTextCounter = 0 Then                 iRow = 1: iCol = 1: iBooksCounter = iBooksCounter + 1                 If Not wkb Is Nothing Then                     With wkb                         .SaveAs Filename:=ThisWorkbook.Path & "\ExcelFile" & iBooksCounter & ".xls"                         .Close SaveChanges:=True                     End With                 End If                 Set wkb = Workbooks.Add             End If                          Set txt = fso.OpenTextFile(Filename:=aFile.Path, IOMode:=ForReading)             sContent = txt.ReadAll                          With wkb.Sheets(1)                 iCol = iCol + 1                 If iCol = 13 Then                     iRow = iRow + 1: iCol = 1                 End If                 .Cells(iRow, iCol) = sContent             End With                      End If              Next  End Sub

comments would be appreciated!

 

[xenou]

I would "simplify" by first getting all the values (step 1) and then writing them to the workbook (step 2) - for me, that's easier to do just one thing at a time.

As you step through the code, you'll also have all your single values from the text files in an array after the first loop is finished - so you can set a breakpoint there and use the watch window to view the array contents.

Try this:

[COLOR="Navy"]Sub[/COLOR] ReadTextFiles()

[COLOR="SeaGreen"]' Make reference to library:[/COLOR]
[COLOR="SeaGreen"]' Tools -> References -> Microsoft Scripting Runtime[/COLOR]

[COLOR="Navy"]Dim[/COLOR] fso [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]New[/COLOR] FileSystemObject
[COLOR="Navy"]Dim[/COLOR] ts [COLOR="Navy"]As[/COLOR] TextStream
[COLOR="Navy"]Dim[/COLOR] f [COLOR="Navy"]As[/COLOR] File
[COLOR="Navy"]Dim[/COLOR] a(1 [COLOR="Navy"]To[/COLOR] 960) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] wb [COLOR="Navy"]As[/COLOR] Workbook
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] intCount [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] ii, jj
[COLOR="Navy"]Dim[/COLOR] s [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]


    [COLOR="SeaGreen"]'//Get all values and save in string array[/COLOR]
    i = 1
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] f [COLOR="Navy"]In[/COLOR] fso.GetFolder(ThisWorkbook.Path).Files
        [COLOR="Navy"]If[/COLOR] fso.GetExtensionName(f.Name) = "tur" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] ts = fso.OpenTextFile(Filename:=f.Path, IOMode:=ForReading)
            a(i) = ts.ReadAll
            i = i + 1
            ts.Close
            [COLOR="SeaGreen"]'//trap too many files errors[/COLOR]
            [COLOR="Navy"]If[/COLOR] i > UBound(a) [COLOR="Navy"]Then[/COLOR]
                MsgBox "Error: Too many files (exceeds " & UBound(a) & ")  "
                [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]For[/COLOR]
            [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
        [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    [COLOR="Navy"]Next[/COLOR] f

    [COLOR="SeaGreen"]'//Write values to 10 workbook in grids of 8 X 12 cells[/COLOR]
    intCount = 1
    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 10
        [COLOR="SeaGreen"]'//create next workbook[/COLOR]
        [COLOR="Navy"]Set[/COLOR] wb = Workbooks.Add
        s = ThisWorkbook.Path & "\ExcelFile" & i & ".xls"
        wb.SaveAs s
        [COLOR="SeaGreen"]'//write values[/COLOR]
        [COLOR="Navy"]For[/COLOR] ii = 1 [COLOR="Navy"]To[/COLOR] 8
            [COLOR="Navy"]For[/COLOR] jj = 1 [COLOR="Navy"]To[/COLOR] 12
                [COLOR="Navy"]If[/COLOR] intCount <= UBound(a) [COLOR="Navy"]Then[/COLOR]
                    wb.Sheets(1).Cells(ii, jj).Value = a(intCount)
                    intCount = intCount + 1
                [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
            [COLOR="Navy"]Next[/COLOR] jj
        [COLOR="Navy"]Next[/COLOR] ii
        wb.Close True
    [COLOR="Navy"]Next[/COLOR] i
    
    
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]