creating an XL Spreadsheet in VB 5.0

[mmisbah69]

there is a an type mismatch error i am getting, when i want to creat an XL file for my code.

                                      
Private Sub Command6_Click()
' Forward mode1
Dim scanner As Integer
Dim StrMsgn As Integer
Dim Forwardn As Integer
Dim StoreVal(20) As Integer
'StrMsgn = "forwardn" & vbCrLf
For Forwardn = 0 To Text9.Text
 If CInt(Text1.Text) > 0 And CInt(Text1.Text) < 1000 Then
          For a = 1 To CInt(Text1.Text)
          'For a = 1 To 20
          'MsgBox ("Please set number of steps from 0 to 999.")
              USB1.WR_P0 (1)
              Sleep (Speed)
              USB1.WR_P0 (2)
              Sleep (Speed)
              USB1.WR_P0 (4)
              Sleep (Speed)
              USB1.WR_P0 (8)
              Sleep (Speed)
          Next a
      ' switch current to zero
      USB1.WR_P0 (0)
      Sleep (2)
      'StrMsgn = StrMsgn & Forwardn & "," & vbCrLf
   Else
    MsgBox ("Please set number of steps from 0 to 999.")
   End If
   StoreVal(0) = USBvolt.VOLT_CH1
                For scanner = 1 To Text8.Text
                    Sleep (500)
                    StoreVal(scanner) = USBvolt.VOLT_CH1
                    Text5.Text = StoreVal(scanner)
                Next scanner
Next Forwardn
'Call WriteInfo(StrMsgn)
End Sub

Private Function WriteInfo(WriteForwardn As String)
    
    
    Dim Forwardn As Integer 'making forward n times
    Dim fson, txtfile
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set txtfile = fso.CreateTextFile(App.Path & "\testn.csv", True)
    txtfile.Write (WriteStr) ' Write a line.

    txtfile.Close

End Function

this is my code and i am trying to write XL file for the values i am scanning in the main code. can anybody please help me to get it through.

[kadghar]

What i can see here is that you have a string and you try to write it into a CSV (coma separated values) text file.

You can actually, write it directly into an Excel File:

Instead of writing a single string with comas, write a 2d array.
When you have this array, lets say a 4x5 array (4 will be the rows, 5 the columns). Use the same method (active x) you use for a text file, but for an Excel file.

Google search some Excel's VBA functions, but, hoppefuly, something like this should do (if you have an 'Arr1(4,5)' array):

[CODE=vb]dim Obj1 as object
set obj= createobject("e xcel.applicatio n")
obj1.workbooks. add
obj1.worksheets .add
with obj1.worksheets (1)
.range(.cells(1 ,1),.cells(4,5) ) = Arr1
end with
obj1.visible=tr ue[/CODE]

HTH