-
Jul 28th, 2016, 11:46 PM
#1
Thread Starter
Junior Member
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:
- I could not able to save the data
- 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
-
Jul 29th, 2016, 06:25 AM
#2
Re: Writing into Excel using Visual basic
Originally Posted by AJITnayak
I am using visual basic 2010.
Thread moved from the 'VB6 and Earlier' forum to the 'VB.Net' (VB2002 and later) forum
-
Jul 29th, 2016, 10:31 AM
#3
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
-
Jul 29th, 2016, 11:36 AM
#4
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|