VB6 retrieving data from Excel. - Xtreme Visual Basic Talk

08-11-2012, 12:07 PM

LFEngineering LFEngineering is offline

Newcomer

Join Date: Aug 2012

Posts: 1

VB6 retrieving data from Excel.

Hello Everyone. I've done a bit of searching, but haven't really found the answers I'm looking for.

I'm working on a program which will controls the motors on my milling machine.

My question is how do I go about extracting data from an excel sheet and using it to populate text boxes on my form?

It's going to be hard to explain, but bare with me.

I have 5 text boxes:
Text1 = I call my "program line number" default value = 1
Text2 = X + value
Text3 = X - value
Text4 = Y + value
Text5 = Y - value

When I cycle my program it reads text2 then text3 then text4 then text5 then adds 1 to the value in text1 and repeats.

I also have an excel sheet which generates my X - Y data.
column 1 = program line number (1,2,3,4, etc)
column 2 = x + data
column 3 = x - data
column 4 = y + data
column 5 = y - data

What I want to do is:
If text1 = 1 then return data from excel sheet row 1, populate text2 with column 2, text3 with column3, and so on.

(If I were to do this in excel I'd use vlookup.)

When my program loops it automatically adds 1 to the value in text1, so my hope is that once it changes it will update with the new values in text boxes 2 thru 5.

Does this make any sense? I'm so sorry, my explanation sucks but I really appreciate any help.

08-24-2012, 04:23 PM

Gruff

Bald Mountain Survivor

Super Moderator
* Expert *

Join Date: Aug 2003

Location: Oregon, USA

Posts: 5,882

Welcome to the forum LF. Don't forget to read the forum Posting Guidelines.

The easy answer is to save a copy of your Excel Workbook as a .csv (Comma Separated Value) file instead of the standard .xls.

Open the File in VB. Read each Line of text. Separate each line by the commas into an array of string. Put each line segment into your textboxes.

If your excel file line number column already contains the serial value why do you not just use that in Text1 rather than trying employ some sort of loop counter?

Roughly something like.

Code:

Private Sub Run_File
  Dim sLine as string
  Dim sColumns() as string

  ' Open the file for input
  Open "C:\myJobData\Job1.csv" for input as #1
    ' Read the file in a loop line by line
    do while not eof(1)
      line input #1, sLine
      ' Break the line up into column segments based on commas
      sColumns = split(sLine,",")

      ' Apply values to textboxes
      Text1.Text = sColumns(0)
      Text2.Text = sColumns(1)
      Text3.Text = sColumns(2)
      Text4.Text = sColumns(3)
      Text5.Text = sColumns(4)

      ' Run whatever vb code you need here to move your motors
      ' Timing routines would probably be needed as well.

    loop
  Close #1
End Sub

A more complex way is to use automation to have Excel open the xls file directly.
Then use automation to read the contents of the worksheet one row at a time.
in a loop. Then close the Excel file through automation.

__________________
Burn the land and boil the sea
You can't take the sky from me

~T