Asked by:
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.
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.
Success
CorWednesday, 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
FormPublic 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
- Edited by KareninstructorMVP Wednesday, May 31, 2017 12:52 PM Added 2nd sample
- Proposed as answer by Cherry BuMicrosoft contingent staff Thursday, June 1, 2017 2:58 AM
Wednesday, May 31, 2017 11:42 AM