Displaying row values from excel on a form

I have an excel spreadsheet and I have data in rows A1:A5. What I want is when a user clicks a buttom on a user form, the data in row A1 is displayed in a textbox. If the user clicks the button again, then the data from row 2 will be diplayed in the textbox.... Any help is GREATLY APPRECIATED...

Thanks,
GS
George_SolomonAsked:
Who is Participating?
 
vinnyd79Commented:
This example uses 6 Textboxes and a command button. Text1 is used to receive the scan. If Text1 has the focus you can scan the serial and it should appear in Text1.  Also you should make sure your scanner is programmed to send a carriage return after the scan so you can check if the file exists and display the values of the cells in the Textboxes so they can be modified. This example will do that.

' add reference to Microsoft Excel Object Library

' declarations area of form
Dim xl As New Excel.Application
Dim wb As Excel.Workbook, ws As Excel.Worksheet
Dim x As Integer
Dim CurrentFile As String


Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii = vbKeyReturn Then
   CurrentFile = ""
   Call CreateExcelFile("C:\results", Text1.Text)
End If
End Sub

Private Sub CreateExcelFile(DirLoc As String, BarCode As String)
   If Dir(DirLoc, vbDirectory) = "" Then MkDir (DirLoc)
   If Right$(DirLoc, 1) <> "\" Then DirLoc = DirLoc & "\"
   
   CurrentFile = DirLoc & BarCode & ".xls"
   
   If Dir(CurrentFile) <> "" Then
       DisplayValues
   Else
       Set wb = xl.Workbooks.Add
       wb.SaveAs (CurrentFile)
       wb.Close
       Set wb = Nothing
   End If
   
   Text2.SetFocus
End Sub

Private Sub Command1_Click()
CurrentFile = ""
Call CreateExcelFile("C:\results", Text1.Text)
SaveValues
End Sub

Private Sub SaveValues()
Set wb = xl.Workbooks.Open(CurrentFile)
Set ws = wb.Sheets("Sheet1")
ws.Cells(1, 1).Value = Text2.Text
ws.Cells(2, 1).Value = Text3.Text
ws.Cells(3, 1).Value = Text4.Text
ws.Cells(4, 1).Value = Text5.Text
ws.Cells(5, 1).Value = Text6.Text
wb.Save
wb.Close
Set wb = Nothing
xl.Quit
Set xl = Nothing

MsgBox "all done"

End Sub


Private Sub DisplayValues()
Set wb = xl.Workbooks.Open(CurrentFile)
Set ws = wb.Sheets("Sheet1")
Text2.Text = ws.Cells(1, 1).Value
Text3.Text = ws.Cells(2, 1).Value
Text4.Text = ws.Cells(3, 1).Value
Text5.Text = ws.Cells(4, 1).Value
Text6.Text = ws.Cells(5, 1).Value
wb.Close
Set wb = Nothing
xl.Quit
Set xl = Nothing
End Sub
0
 
Sys_ProgCommented:
What GUI tool have u used for building your user form

If VB
1. Connect to the Excel using ADODC control, read and display


If PB
1. Connect to Excel using datawindow and retrieve


Let me know and i can help u with either or any other options


0
 
Shane Russell2nd Line Desktop SupportCommented:
ok first of all you set the control source property of the textbox to A1 then If you give me a min I will post some of my PAQ's to show you how to do this :)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Shane Russell2nd Line Desktop SupportCommented:
Take a look at this :

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21335618.html

And scroll down to the end :) There is my other 2nd PAQ, that should help you with that :)
0
 
Shane Russell2nd Line Desktop SupportCommented:
btw you insert that code into the command button making sure that you have the textbox name instead of the name I have given them and you will need to alter the code slightly but that gives you exactly what you are looking for as far as I can tell :)
0
 
vinnyd79Commented:
In VB6 you could add a reference to the Microsoft Excel Object Library but what if the user is on Row 5 and presses the button again. Do you want it to go back to Row 1 and start over? This example will do that:


' add reference to Microsoft Excel Object Library

' declarations area of form
Dim xl As New Excel.Application
Dim wb As Excel.Workbook, ws As Excel.Worksheet
Dim x As Integer


Private Sub Form_Load()
Set wb = xl.Workbooks.Open("C:\myfile.xls")
Set ws = wb.Sheets("Sheet1")
x = 0
End Sub


Private Sub Command1_Click()
 If x = 5 Then x = 0
 x = x + 1
 Text1.Text = ws.Range("A" & x).Value
End Sub

Private Sub Form_Unload(Cancel As Integer)
wb.Close
xl.Quit
Set ws = Nothing
Set wb = Nothing
Set xl = Nothing
End Sub
0
 
George_SolomonAuthor Commented:
vinnyd79,
This is how the program should work:
1. The user scans a serial number and presses a button on the form. Pressing the button will create an excel sheet that has the serial number as the file name and create a director named results and save the file there (c:\results\1235.xls)

2. As the user answers each of the 5 questions at appear on the form (question asked from form,yes, no,comment), the data will written to the spreadsheet created with the serial number until all 5 step have been completed. At this time a message will appear in the text windo "ALL DONE"

0
 
Sys_ProgCommented:
As I said earlier, user ADODC control and treat Excel Sheet as a database.

Thus, each column in excel sheet becoms column of your database table and each row becomes a row in the table (in your example - 5)

You do not have to do anything extra - just treat excel sheet as a database.

Amit
0
 
Sys_ProgCommented:
You do not have to read cell by cell (as shown above) if you implment using ADoDC control


Amit

0
 
George_SolomonAuthor Commented:
Vinnyd79,
How do I add a reference to Microsoft Excel Object Library
0
 
vinnyd79Commented:
In VB goto the Projects Menu and select References,then Select Microsoft Excel Object Library. Personally I would not use an ADODC control,but you could.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.