Results 1 to 4 of 4

Thread: Writing into Excel using Visual basic

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2014
    Posts
    30

    Writing into Excel using Visual basic

    Dear all,
    I am using visual basic 2010. I have string of value, I wanted to recorded into excel one after the other. I want to save data which i can read later.

    IN below peiece of code i could able to record the data
    problems:
    1. I could not able to save the data
    2. I want to write into excel only after timer is on for 3S. i could able to write in rich text box. but not in excel.


    Code:
    Option Explicit On
    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class Excel_Record
        Dim stopclick As Boolean = True
        Dim Data As String
        Dim strarray() As String
        Dim xlApp As New Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
    
    
        Private Sub Bt_Record_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Bt_Record.Click
            xlWorkBook = xlApp.Workbooks.Add
            xlApp.Visible = True
            xlWorkSheet = xlWorkBook.Sheets("Sheet1")
           
            With xlWorkSheet
                .Range("A1").Value = "SMCB"
                .Range("B1").Value = "Device_ID"
                .Range("C1").Value = "String1"
                .Range("d1").Value = "String2"
                .Range("E1").Value = "String3"
                .Range("F1").Value = "String4"
                .Range("G1").Value = "String5"
                .Range("H1").Value = "String6"
                .Range("I1").Value = "String7"
                .Range("J1").Value = "String8"
                .Range("K1").Value = "String9"
                .Range("L1").Value = "String10"
                .Range("M1").Value = "String11"
                .Range("N1").Value = "String12"
                .Range("O1").Value = "String13"
                .Range("P1").Value = "String14"
                .Range("Q1").Value = "String15"
                .Range("R1").Value = "String16"
                .Range("S1").Value = "String17"
                .Range("T1").Value = "String18"
                .Range("U1").Value = "String19"
                .Range("V1").Value = "String20"
                .Range("W1").Value = "String21"
                .Range("X1").Value = "String22"
                .Range("Y1").Value = "String23"
                .Range("Z1").Value = "String24"
                .Range("AA1").Value = "TEMP"
                .Range("AB1").Value = "HVREAD"
                .Range("AC1").Value = "SPD"
                .Range("AD1").Value = "DIS"
    
            End With
    
    
    
            While True
                Timer1.Interval = 3000
                Timer1.Start()
                RichTextBox1.AppendText("Timer Started" & vbCrLf)
                If stopclick Then
                    stopclick = False
                    Exit While
                End If
            End While
        End Sub
    
        Private Sub BtCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtCancel.Click
            Timer1.Stop()
            stopclick = True
            RichTextBox1.AppendText("Timer Stopped" & vbCrLf)
        End Sub
    
    
        Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
            xlWorkSheet = xlWorkBook.Sheets("Sheet1")
            xlWorkSheet = xlWorkBook.Sheets("Sheet1")
            Data = "SMCB1,1,1,1,1,1,1,1,1,1,2,3,4,5,6,7,8,90,1,2,3,12,21,22,21,21,Temp_read:12,Hv_read:123,SPD:1,DIS:1"
            RichTextBox1.AppendText(Data & vbCrLf)
            strarray = Data.Split(",")
            With xlWorkSheet
                For J As Integer = 2 To 100
                    For index As Integer = 0 To strarray.GetUpperBound(0)
                        xlWorkSheet.Cells(J, index + 1) = strarray(index)
                    Next
                Next
    
            End With
    
        End Sub
    
        
    End Class
    Code part i found here.

    Code:
    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
            xlWorkSheet = xlWorkBook.Sheets("Sheet1")
            xlWorkSheet = xlWorkBook.Sheets("Sheet1")
            Data = "SMCB1,1,1,1,1,1,1,1,1,1,2,3,4,5,6,7,8,90,1,2,3,12,21,22,21,21,Temp_read:12,Hv_read:123,SPD:1,DIS:1"
            RichTextBox1.AppendText(Data & vbCrLf)
            strarray = Data.Split(",")
            With xlWorkSheet
                For J As Integer = 2 To 100
                    For index As Integer = 0 To strarray.GetUpperBound(0)
                        xlWorkSheet.Cells(J, index + 1) = strarray(index)
                    Next
                Next
    
            End With
    
        End Sub

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Writing into Excel using Visual basic

    Quote Originally Posted by AJITnayak View Post
    I am using visual basic 2010.
    Thread moved from the 'VB6 and Earlier' forum to the 'VB.Net' (VB2002 and later) forum

  3. #3
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Writing into Excel using Visual basic

    Is that ALL the code? After all, you do appear to be writing into a spreadsheet, but nowhere in that code do you save the spreadsheet, so I assume that it is all just lost once the application ends.
    My usual boring signature: Nothing

  4. #4
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    Re: Writing into Excel using Visual basic

    You need to call xlWorkBook.Save() or xlWorkBook.SaveAs("C:\YourFileName.xlsx") in your timer tick event to save the workbook.

    - kgc
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width