VB and Excel

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.

Answer 1

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

Answer 2

I think its not what I am looking for.

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

Answer 3

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.

Answer 4

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?

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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