Transferring data to Excel

[royshh]

I am writing a program which transfers data from the serial port to an Excel file.

At the moment, values are read into a label every 5 seconds. Each new value overwrites the previous one so there is only one value in the label at a time.
Below is the code which does this.


Private Sub Timer1_Timer()
' Read in and display values.
Dim in_byte As String
Dim in_message As String
in_message = ""
MSComm1.InputLen = 1 ' Read a single byte from buffer each time.
MSComm1.Output = "READ?" & Chr(13) & Chr(10) ' Query instrument.
Do
Do
DoEvents ' Continue processing events.
Loop Until MSComm1.InBufferCount >= 1 ' Wait for a character.
in_byte = MSComm1.Input
in_message = in_message & in_byte ' Append to i/p string.
Loop Until (in_byte = Chr(10)) ' Wait for a LF terminator.
Label2.Caption = in_message ' Display message.
End Sub


I now need to send these values to an Excel file starting at cell B3. The times must start at cell A3, the first time being 0 and incrementing in steps of five.
The reason that they must start here is that later i will be inserting headings in the range A1 to B2 with a VB macro.

I already have a program which creates a two dimensional array using a random number generator and sends it to an Excel file.
The following code does this.


Private Sub Form_Load()
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim oRng As Excel.Range

' Start Excel and get Application object.
Set oXL = CreateObject("Excel.Application")

' Get a new workbook.
Set oWB = oXL.Workbooks.Add
Set oWS = oWB.ActiveSheet

' Initialize the random number generator.
Randomize

' Create an array with 100 rows and 2 columns.
Dim DataArray(1 To 100, 1 To 2) As Variant
Dim r As Integer
For r = 1 To 100
DataArray(r, 1) = r - 1
DataArray(r, 2) = Rnd() * 1000
Next

' Transfer the array to the worksheet starting at cell A3.
Range("A3").Resize(100, 2).Value = DataArray

'Save the Workbook and Quit Excel
oWB.SaveAs "C:\Book1.xls"
oXL.Quit


What i need to do is replace the random number generator with the values from the serial port and there corresponding times.

The program must be fully automated and be able to cater for an unknown number of values.

Can anyone help?

[si_the_geek]

It will be slow to continually open and close the workbook, so I would recommend keeping it open for the life of the program, with an occasional Save to make sure you don't lose too much data if your computer crashes.

You should always close objects when you have finished using them, and then set them to nothing (see my code in Form_Unload).

'in the "general declarations" section of the form:
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim oRng As Excel.Range
Dim lOutputRow as Long


Private Sub Form_Load()

' Start Excel and get Application object.
Set oXL = CreateObject("Excel.Application")

' Create a new workbook.
Set oWB = oXL.Workbooks.Add
Set oWS = oWB.ActiveSheet

'Set the headings ** change this as apt! **
oWS.Cells(1,1) = "Header1"
oWS.Cells(1,2) = "Header2"
oWS.Rows(1).Font.Bold = True

'Save the workbook ** you should have error checking here, in case this file exists already **
oWB.SaveAs "C:\Book1.xls"

'Set the row to put the next set of data into
lOutputRow = 2

End Sub


Private Sub Form_UnLoad()

'Save and close the Workbook
oWB.Save
oWB.Close
Set oWB = Nothing

'Quit Excel
oXL.Quit
Set oXL = Nothing

End Sub


Private Sub Timer1_Timer()
' Read in and display values.
Dim in_byte As String
Dim in_message As String
in_message = ""
MSComm1.InputLen = 1 ' Read a single byte from buffer each time.
MSComm1.Output = "READ?" & Chr(13) & Chr(10) ' Query instrument.
Do
Do
DoEvents ' Continue processing events.
Loop Until MSComm1.InBufferCount >= 1 ' Wait for a character.
in_byte = MSComm1.Input
in_message = in_message & in_byte ' Append to i/p string.
Loop Until (in_byte = Chr(10)) ' Wait for a LF terminator.
Label2.Caption = in_message ' Display message.

'insert values into Excel
oWS.Cells(lOutputRow, 1) = (lOutputRow-2)*5
oWS.Cells(lOutputRow, 2) = in_message
lOutputRow = lOutputRow + 1
'save after every 10 lines
If lOutputRow Mod 10 = 0 Then oWB.Save
If lOutputRow > 65536 Then
stop '** you cannot put in more values, so you need to create a new WorkSheet/WorkBook as you see fit **
End If

End Sub

[royshh]

I tried your code but it is giving an error on the line

Private Sub Form_UnLoad()

[si_the_geek]

If you get an error that you want help with, you should not only specify where the error occured, but what the error was too.

In this case it was a minor mistake on my part, that line should have been:
Private Sub Form_Unload(Cancel As Integer)