none
VB and Excel RRS feed

  • Question

  • Hi everyone 

    With this question, I am just asking if its possible or not.

    I have VB application with two buttons , Button1, Button2.

    I want that when button1 is pressed, is written in the excel sheet, similarly for the button2, with the date and time of event triggered.

    I know that we can use Stream.Write to append the text etc. but the challenge I see so far is how to cope with the columns in excel sheet? I mean how we can direct output to specific column etc. Excel sheet looks like following:

    Thanks in advance.

    Wednesday, May 31, 2017 6:51 AM

All replies

  • If you look down in this forum, you will recognizse that Karen has made an article about VB and Excel,

    Maybe fine to read that.

    https://social.msdn.microsoft.com/Forums/vstudio/en-US/c5479c95-6541-4ba0-a09a-d5c224bf38a6/alternate-methods-to-work-with-microsoft-excel-in-vbnet-project?forum=vbgeneral


    Success
    Cor

    Wednesday, May 31, 2017 7:29 AM
  • I think its not what I am looking for.

    Keep this thread open for someone else, if they have better idea.

    Wednesday, May 31, 2017 7:36 AM
  • I think its not what I am looking for.
    Keep this thread open for someone else, if they have better idea.

    You should explain why that approach is not suitable for your task.

    You mention Stream.Write.  If you are implying that it might be possible to write some data directly to an XLS file, then that is simply not practicable.

    Wednesday, May 31, 2017 7:59 AM
  • Hello Acamar,

    As I mentioned already, with this thread question I am just asking if its possible or not. The example above do not show if data will be inserted into sheet when triggered by vb application.

    So what you say, its not good practice to use Stream.Write, correct? And look for a different approach?

    Wednesday, May 31, 2017 8:08 AM
  • As I mentioned already, with this thread question I am just asking if its possible or not. The example above do not show if data will be inserted into sheet when triggered by vb application.

    So what you say, its not good practice to use Stream.Write, correct? And look for a different approach?

    It is possible, and that thread shows some examples of how it would be done.  I think that was your question.

    If you are actually asking can it be done using Stream.Write, then that implies that you want to do it by updating the XLS file.   There is a description of the file format here:
    https://msdn.microsoft.com/en-us/library/office/gg615597(v=office.14).aspx
    so Yes, I suppose it is possible, and the existence of some applications that can access XLS files indicates that it has been done.   The use of Stream.Write is a trivial part of the process - the internal manipulation of the file elements is the difficult bit.  Whether it is worth the effort is, of course, a very different consideration.

    Wednesday, May 31, 2017 8:36 AM
  • how to define to point to correct column in excel sheet to write /append the text was my question. Like if button1 was clicked, Excel column A should append. In case of Button2 was clicked, column2 in Excel sheet should append or add data.
    • Edited by -OSD- Wednesday, May 31, 2017 9:39 AM
    Wednesday, May 31, 2017 9:38 AM
  • how to define to point to correct column in excel sheet to write /append the text was my question.

    I thought your question was 'Is it possible', not 'How to'.  If you want to do this with automation then see:
    https://support.microsoft.com/en-us/help/219151/how-to-automate-microsoft-excel-from-visual-basic

    If you want to use the library, follow the example provided above.

    If you want to do it by working directly with the XLS file, see the file definition information I referred to.

    Wednesday, May 31, 2017 9:44 AM
  • Is there a reason this is not for you?

    Using Excel automation takes many more lines of code than using the following done in SpreadSheetLight.

    This is for demo purposes, I hard coded dates, go through a for/each, each time writing a date to column B, next empty cells. You of course would do one date at a time, first validate it's a valid date then write it and if I were doing one at at time I would validate too.

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim dates As New List(Of DateTime) From {#1/1/2017#, #2/2/2016#, #3/3/2017#}
        For Each item As DateTime In dates
            ops.WriteDate("B", item)
        Next
    End Sub

    This code was written in Operations.vb from my code sample Cor mentioned.

        Public Sub WriteDate(ByVal Column As String, ByVal theDate As DateTime)
            Dim fileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Sample3.xlsx")
            Using sheet1 As New SLDocument(fileName, "Sheet1")
                Dim stats As SLWorksheetStatistics = sheet1.GetWorksheetStatistics
                Dim emptyRowIndex As Integer = 0
                For xRow As Integer = 1 To stats.EndRowIndex
                    emptyRowIndex = xRow + 1
                    If Not sheet1.HasCellValue($"{Column}{xRow}") Then
                        Exit For
                    End If
                Next
                If emptyRowIndex > 0 Then
                    Dim dateStyle = sheet1.CreateStyle
                    dateStyle.FormatCode = "mm-dd-yyyy"
                    sheet1.SetCellValue($"{Column}{emptyRowIndex}", theDate)
                    sheet1.SetCellStyle(emptyRowIndex, 2, emptyRowIndex, 2, dateStyle)
                    sheet1.Save()
                End If
            End Using
        End Sub

    Okay, had to run into work, just created another example.

    Imports SpreadsheetLight
    Public Class Operations
        Public Sub WriteDate(ByVal DateColumn As String, ByVal TimeColumn As String, ByVal theDate As DateTime)
            Dim fileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Sample4.xlsx")
            If Not IO.File.Exists(fileName) Then
                Exit Sub
            End If
            Using sheet1 As New SLDocument(fileName, "Demo")
                Dim stats As SLWorksheetStatistics = sheet1.GetWorksheetStatistics
                Dim emptyRowIndex As Integer = 0
                For xRow As Integer = 1 To stats.EndRowIndex
                    emptyRowIndex = xRow + 1
                    If Not sheet1.HasCellValue($"{DateColumn}{xRow}") Then
                        Exit For
                    End If
                Next
                If emptyRowIndex > 0 Then
                    Dim dateColIndex = SLConvert.ToColumnIndex(DateColumn)
                    Dim timeColIndex = SLConvert.ToColumnIndex(DateColumn)
                    Dim dateStyle = sheet1.CreateStyle
                    dateStyle.FormatCode = "mm-dd-yyyy"
                    sheet1.SetCellValue($"{DateColumn}{emptyRowIndex}", theDate)
                    sheet1.SetCellStyle(emptyRowIndex, dateColIndex, emptyRowIndex, dateColIndex, dateStyle)
                    sheet1.SetCellValue($"{TimeColumn}{emptyRowIndex}", theDate.ToShortTimeString)
                    sheet1.Save()
                End If
            End Using
        End Sub
    
    End Class
    
    Form
    Public Class Form1
        Private ops As New Operations
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            ops.WriteDate("A", "B", Now)
            Console.WriteLine("Done")
        End Sub
    End Class
    



    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Wednesday, May 31, 2017 11:42 AM
    Moderator