Vb And Excel

[Hari]

Hi,

The following is the program i have written to input data into Excel using VB.It is working fine for inoutting one value for a particular cell.BUT i want to make it work like a database entry i.e i WANT TO USE A SINGLE TEXTBOX TO MAKE ENTRIES INTO SAY INCREASING ROW NUMBER(i.e row1,2,3..).I have tried using 'for' statement for incrementing row number but it doesn't seem to work.

Private Sub Command1_Click()
Dim XLApp As New Excel.Application
Dim i As Integer

XLApp.Workbooks.Open ("C:\My Documents\1.xls")

XLApp.Visible = True


Text1.SetFocus
For i=1 to 10
XLApp.Cells(i, 1) = Text1.Text
XLApp.ActiveWorkbook.Save
ReadOnlyRecommended = False
CreateBackup = False
Text1 = ""
Next i
XLApp.Workbooks.Close
Set XLApp = Nothing

End Sub


Please help!

[alex_read]

You're putting too much in your for...neext statement:

Private Sub Command1_Click() 
Dim XLApp As New Excel.Application, i As Integer 

  With XlApp
     .Workbooks.Open ("C:\My Documents\1.xls") 
     .Visible = True 
     .displayalerts = false
  End with

  Text1.SetFocus 
  For i=1 to 10 
     XLApp.Cells(i, 1) = Text1.Text 
  Next i 

  Text1.text = "" 
  XLApp.ActiveWorkbook.Save 
  XLApp.Workbooks.Close 
  Set XLApp = Nothing 
End Sub

Your code was working fine, you deleted the text in the text box though by selecting the text1.text = "". As the for...next part was excecuting, you deleted this text after the first cell, then the blank text was copied over to the remaining 9 cells

I have also excluded the following lines, as these values are automatically set if you leave them out :
ReadOnlyRecommended = False
CreateBackup = False

[Anakim]

Hi Hari,

They way I chuck stuff into Excel cells is to use the Range object.....

Dim liRow As Index
Dim lsCol As String

    lsCol = "A"
    For liRow = 1 To 10
        loWorksheet.Range(lsCol & liRow).Value = "This is row No. " & liRow
    Next

I cycle through (not literally as a road bike tends to find the excel surface too bumpy unless the gridlines are turned off!). As I wuz saying.... the cols are cycled through too, in some cases, using a quick algorithm to convert a number to column. With the Range object you can use the regular Excel notation to place the same data in multiple cells ("A1:C3" etc)

[Anakim]

Alex, you got there b4 me and was paying somewhat more attention to Hari's code than I! First rule of programming analyse the the problem placed b4 you. Hmmm guess I'm guilty of blundering headlong without due care and attention!

[alex_read]

I've done that one too many times & have just about learnt my lesson from all the others on this site moaning

[Hari]

hi,
Thanks for the quick response.But WHATEVER I AM ENTERING IN TEXT! IS BEING REPEATED IN ALL THE ROWS.I don't want to do that i want to input new data to each row thru' the same textbox.I hope i make myself clear.
Waiting for ur response,
Hari

[Anakim]

Of course it's being repeated in all rows as there's no mechanism for the user to change the contents of the text box.

Put the code in the lostfocus event on the text box (though I try to avoid using lostfocus/gotfocus events as a rule), or trap the 'return' key. When user hits return key move text to whichever row you want. Praps if there's a boat load of stuff the user is entering consider an array of text boxes or a grid and loop through those placing contents to cells as you go.

Depends on what you're trying to achieve.

Oh and

WHATEVER I AM ENTERING IN TEXT! IS BEING REPEATED IN ALL THE ROWS

please don't shout - beer is still wearing off!

[alex_read]

Ok, something like:

Public i As Integer 

Private Sub Form1_load
   i = 1
End Sub


Private Sub Command1_Click() 
Dim XLApp As New Excel.Application 

  With XlApp
     .Workbooks.Open ("C:\My Documents\1.xls") 
     .Visible = True 
     .displayalerts = false
  End with

  Text1.SetFocus 
  XLApp.Cells(i, 1) = Text1.Text 
  i = i + 1
  Text1.text = "" 

  XLApp.ActiveWorkbook.Save 
  XLApp.Workbooks.Close 
  Set XLApp = Nothing 
End Sub

[Hari]

Yippee!!
it worked.Thanks a lot.
I will be coming up with such lousy mistakes if u don't mind!
Hari

[alex_read]

Not a problem, I noticed I wrote that in a rush there, & that you're a new member.

If you just pasted the code ijnto your app & want me to explainwhat the hell it means, just ask...