none
Textbox lines into Excel rows RRS feed

  • Question

  • Hi,

    I have a a multi line textbox , each line consists of a number , like :

    1

    2

    46

    7

    ...

    I tested the code below so as to export textbox value into one excel column :

     Dim oExcel As Object
            Dim oBook As Object
            Dim oSheet As Object
    
            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.workbooks.add
            oSheet = oBook.worksheets(1)
    
            oSheet.range("A1").value = textbox1.text
    
    
    
            oBook.saveas("D:\test.xlsx")
            oBook.close()

    It puts whole the textbox lines in one cell (first row of the "A1" column). It know I should loop through textbox lines and add each of them to a new row. I know how to loop through textbox lines :

    For Each line As String In Me.textBox1.Text.Split(vbLf)
       '' do something
    Next
    
    

    but I'm not familiar with Excel automation.  I would be thankful if you help me out.

    Saturday, June 1, 2019 9:27 AM

Answers

  • Hello,

    There is a much better way to do this using a free library named SpreadSheetLight which is installed using Visual Studio NuGet package manager. About half way down the home page of SpreadSheetLight there is a help file, unfortunately (and this is not a bad thing) there is a ton of functionality so it may take a while to find everything you need past my code sample below and the link at the end of this post.

    After installing SpreadSheetLight add this to your form

    Imports SpreadsheetLight

    In the following example I iterate the lines of a TextBox, starting a A1 put a value into cell downward.

    Private Sub WriteTextBoxToExcelButton_Click(sender As Object, e As EventArgs) _
    Handles WriteTextBoxToExcelButton.Click
    
        Using doc As New SLDocument()
            For index As Integer = 0 To TextBox1.Lines.Count() - 1
                doc.SetCellValue(index + 1, 1, TextBox1.Lines(index))
            Next
            doc.SaveAs(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"))
        End Using
    
    End Sub
     

    The above after viewing the results in Excel will have a green chevron indicating that you should allow Excel to convert the value to number, with SpreadSheetLight if you know you are writing numbers rather than SetCellValue use SetCellValueNumeric e.g.

    Using doc As New SLDocument()
        For index As Integer = 0 To TextBox1.Lines.Count() - 1
            doc.SetCellValueNumeric(index + 1, 1, TextBox1.Lines(index))
        Next
        doc.SaveAs(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"))
    End Using

    In closing check out my MSDN code samples on SpreadSheetLight.

    https://code.msdn.microsoft.com/Alternate-methods-to-work-4c52c4a2?redir=0


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Saturday, June 1, 2019 9:59 AM
    Moderator

All replies

  • Hello,

    There is a much better way to do this using a free library named SpreadSheetLight which is installed using Visual Studio NuGet package manager. About half way down the home page of SpreadSheetLight there is a help file, unfortunately (and this is not a bad thing) there is a ton of functionality so it may take a while to find everything you need past my code sample below and the link at the end of this post.

    After installing SpreadSheetLight add this to your form

    Imports SpreadsheetLight

    In the following example I iterate the lines of a TextBox, starting a A1 put a value into cell downward.

    Private Sub WriteTextBoxToExcelButton_Click(sender As Object, e As EventArgs) _
    Handles WriteTextBoxToExcelButton.Click
    
        Using doc As New SLDocument()
            For index As Integer = 0 To TextBox1.Lines.Count() - 1
                doc.SetCellValue(index + 1, 1, TextBox1.Lines(index))
            Next
            doc.SaveAs(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"))
        End Using
    
    End Sub
     

    The above after viewing the results in Excel will have a green chevron indicating that you should allow Excel to convert the value to number, with SpreadSheetLight if you know you are writing numbers rather than SetCellValue use SetCellValueNumeric e.g.

    Using doc As New SLDocument()
        For index As Integer = 0 To TextBox1.Lines.Count() - 1
            doc.SetCellValueNumeric(index + 1, 1, TextBox1.Lines(index))
        Next
        doc.SaveAs(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"))
    End Using

    In closing check out my MSDN code samples on SpreadSheetLight.

    https://code.msdn.microsoft.com/Alternate-methods-to-work-4c52c4a2?redir=0


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Saturday, June 1, 2019 9:59 AM
    Moderator
  • Hello,

    There is a much better way to do this using a free library named SpreadSheetLight which is installed using Visual Studio NuGet package manager. About half way down the home page of SpreadSheetLight there is a help file, unfortunately (and this is not a bad thing) there is a ton of functionality so it may take a while to find everything you need past my code sample below and the link at the end of this post.

    After installing SpreadSheetLight add this to your form

    Imports SpreadsheetLight

    In the following example I iterate the lines of a TextBox, starting a A1 put a value into cell downward.

    Private Sub WriteTextBoxToExcelButton_Click(sender As Object, e As EventArgs) _
    Handles WriteTextBoxToExcelButton.Click
    
        Using doc As New SLDocument()
            For index As Integer = 0 To TextBox1.Lines.Count() - 1
                doc.SetCellValue(index + 1, 1, TextBox1.Lines(index))
            Next
            doc.SaveAs(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"))
        End Using
    
    End Sub
     

    The above after viewing the results in Excel will have a green chevron indicating that you should allow Excel to convert the value to number, with SpreadSheetLight if you know you are writing numbers rather than SetCellValue use SetCellValueNumeric e.g.

    Using doc As New SLDocument()
        For index As Integer = 0 To TextBox1.Lines.Count() - 1
            doc.SetCellValueNumeric(index + 1, 1, TextBox1.Lines(index))
        Next
        doc.SaveAs(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"))
    End Using

    In closing check out my MSDN code samples on SpreadSheetLight.

    https://code.msdn.microsoft.com/Alternate-methods-to-work-4c52c4a2?redir=0


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    What a perfect library! Thanks Karen
    Saturday, June 1, 2019 11:05 AM
  • Hello,

    There is a much better way to do this using a free library named SpreadSheetLight which is installed using Visual Studio NuGet package manager. About half way down the home page of SpreadSheetLight there is a help file, unfortunately (and this is not a bad thing) there is a ton of functionality so it may take a while to find everything you need past my code sample below and the link at the end of this post.

    After installing SpreadSheetLight add this to your form

    Imports SpreadsheetLight

    In the following example I iterate the lines of a TextBox, starting a A1 put a value into cell downward.

    Private Sub WriteTextBoxToExcelButton_Click(sender As Object, e As EventArgs) _
    Handles WriteTextBoxToExcelButton.Click
    
        Using doc As New SLDocument()
            For index As Integer = 0 To TextBox1.Lines.Count() - 1
                doc.SetCellValue(index + 1, 1, TextBox1.Lines(index))
            Next
            doc.SaveAs(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"))
        End Using
    
    End Sub
     

    The above after viewing the results in Excel will have a green chevron indicating that you should allow Excel to convert the value to number, with SpreadSheetLight if you know you are writing numbers rather than SetCellValue use SetCellValueNumeric e.g.

    Using doc As New SLDocument()
        For index As Integer = 0 To TextBox1.Lines.Count() - 1
            doc.SetCellValueNumeric(index + 1, 1, TextBox1.Lines(index))
        Next
        doc.SaveAs(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "test.xlsx"))
    End Using

    In closing check out my MSDN code samples on SpreadSheetLight.

    https://code.msdn.microsoft.com/Alternate-methods-to-work-4c52c4a2?redir=0


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange



    What a perfect library! Thanks Karen
    Glad to assist.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, June 1, 2019 11:24 AM
    Moderator