MS Excell Problems

[ProgramGuy]

I need to make a program that will export data from a rich textbox into the cells in MS Excell. I have formatted the textbox so that the data for each cell is seperated by a space ex: hi there would be two different cells, "hi" and "there". If there is any way whatsoever, could someone please tell me how?

[jjortiz]

Here some code i had for excel. If that will help you any.

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

      'On Error GoTo Err_Handler
      
   ' Start Excel and get Application object.
      Set oXL = CreateObject("Excel.Application")
      oXL.Visible = True
      
   ' Get a new workbook.
      Set oWB = oXL.Workbooks.Add
      Set oSheet = oWB.ActiveSheet
      
   ' Add table headers going cell by cell.
      oSheet.Cells(1, 1).Value = Label1
      oSheet.Cells(1, 2).Value = Label2
      oSheet.Cells(1, 3).Value = Label3
      oSheet.Cells(1, 4).Value = Label4
      

   ' Format A1:D1 as bold, vertical alignment = center.
      With oSheet.Range("A1", "D1")
         .Font.Bold = True
         .VerticalAlignment = xlVAlignCenter
      End With
      
End Sub

[ProgramGuy]

Thanks for the code, at least i am at least halfway there now

[ProgramGuy]

Now I need to know how to take each part of the text and make it into a different cell. ex: visual basic would be two cells, visual and basic. I already know how to open excell now.

[jjortiz]

Okay can you draw like a little chart or something. Let's say like the last sentence. You would split that into 10 cells. Is that what you are trying to do.

[ProgramGuy]

well, I need each word to have its own cell coing across. so the sentence "hi i am a programmer" would be hi | i | am | a | programmer

[jjortiz]

Alright I am working on it right now. I will post as soon as i am done.

[ProgramGuy]

thanks, if you ever need a program, give me an email, mabe i can help

[jjortiz]

Okay i have not figured it out how to get specific line from the RTF. I will figure it out eventually. You probabably already know. but I created some code to take care of what you are trying to do. Just pass it the text and it will check for the spaces.

Private Sub Command1_Click()
       StartExcel Text1.Text
End Sub
Private Function StartExcel(ByVal Words As String)
      Dim sWordsArr() As String
      Dim oXL As Excel.Application
      Dim oWB As Excel.Workbook
      Dim oSheet As Excel.Worksheet
      Dim oRng As Excel.Range
      
      sWordsArr = Split(Words, Space(1))
      
      'On Error GoTo Err_Handler
      
   ' Start Excel and get Application object.
      Set oXL = CreateObject("Excel.Application")
      oXL.Visible = True
      
   ' Get a new workbook.
      Set oWB = oXL.Workbooks.Add
      Set oSheet = oWB.ActiveSheet
      
    For i = 0 To UBound(sWordsArr)
        oSheet.Cells(1, i + 1).Value = sWordsArr(i)
    Next

End Function

[ProgramGuy]

thanks a lot, your code worked perfectly!

[jjortiz]

oSheet.Cells(1, i + 1).Value = sWordsArr(i)
You see where the 1 is where the row is inserted and the i + 1 is because there is no row 0 so you can use option base 1 to start the array at 1. You are welcome.