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)