Search code examples
excelstringvb.netappendstringbuilder

StringBuilder Append replacing data- issue

From a very large Excel file, We loop and store the values from 3 of the columns into variables( uName(Row), mgrName(Row), title(Row) ). From this excel, we also get the number of rows.

The issue comes when I am trying to use StringBuilder to create a separate .xls file to be used later in the application. The code I have looks like this:

    Dim XLstring As System.Text.StringBuilder = New System.Text.StringBuilder
    Dim newfile As System.IO.StreamWriter
    Dim fileTitle

    XLstring.Append("Name,Manager,Title" & vbCrLf)

    For X As Integer = 2 To countrows
        If X = countrows Then
            MsgBox(countrows)
            MsgBox(uName(X) & "," & mgrName(X) & "," & title(X))
        End If

        XLstring.Append(uName(X) & "," & mgrName(X) & "," & title(X) & vbCrLf)
    Next

    fileTitle = System.DateTime.Now.ToString("yy-MM-dd hh-mm-ss") & ".xls"
    filePath2 = "myPath" & fileTitle

    newfile = File.CreateText(filePath2)
    newfile.WriteLine(XLstring)

This works for the most part. I can see that I am grabbing the correct number of rows as well as the correct information in the last row using MsgBox. When I open the resulting .xls file however, there are entries missing at the end of the file. In addition, if I were to change 

XLstring.Append("Name,Manager,Title" & vbCrLf)

to something like

XLstring.Append("Name,Manager" & vbCrLf)

the exact number of characters I removed from the Append line will now successfully appear at the end of the file where the information is missing.

Is there some weird functionality that I am not understanding using these functions? I am completey lost and don't understand this behavior.

Solution

  • You need to close the file when you're done:

    newfile.Close()

    On a side note, your file is being saved with the XLS extension, but since you are exporting a list of comma-separated values, I would suggest saving in CSV format instead.