How to read an excel file using VB 6?

[Niki_S]

I have an excel file as '29587643' and the location is Desktop\29587643. Now I want to do something like this.
I have a textbox as txtStyle and I want to search the excel file location using my txtStyle. It means if the txtbox value is 295876438 I want to get my 29587643 excel file. And I want to get the numbers in column A,E.
The layout of my excel is as below,

After reading this I want to get results into an array.
The results I want is as below,

815
236
548
4
120
58
354
654
333
852

How can I do this?
Thank you.

 

[strongm]

What have you tried so far?

 

[Niki_S]

I have no any idea about this because this is new to me.

 

[Andrzejek]

IMO, first you should use (investigate how to use) Common Dialog control to display the Open dialog box. Once you can point to your (Excel) file, then you can try this

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Niki_S]

I used something like this.

Private Sub cmdExcel_Click()
Dim oXLApp As Excel.Application       'Declare the object variable
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
Dim Style_no As String
Dim Line_no As String

Dim vArray As Variant
Dim lngRow As Long, lngCol As Long
Dim strTemp As String


 
  Set oXLApp = New Excel.Application  'Create a new instance of Excel
  'oXLApp.Visible = True               'Show it to the user
  Set oXLBook = oXLApp.Workbooks.Open("K:\GENERAL\POD_Infor\295876438.xls") 'Open an existing workbook
  Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
  
  '*******STYLE NO*****
  Style_no = oXLSheet.Range("G5").Value
  
  
  '*******LINE NO******
  Line_no = oXLSheet.Range("G6").Value
  
  
  '("E12:E140")
  '*******JOB NO'S*****
  vArray = oXLSheet.Range("E12:E140").Value
  
  
  
  
    For lngRow = 1 To UBound(vArray, 1)
    For lngCol = 1 To UBound(vArray, 2) - 1
        strTemp = strTemp & vArray(lngRow, lngCol) & vbTab
    Next lngCol
        strTemp = strTemp & vArray(lngRow, UBound(vArray, 2)) & vbCrLf
    Next lngRow

  
 
   txtStyleNo.Text = Style_no
   txtLine.Text = Line_no
 
   cboOperation.AddItem vArray(lngCol, 1)
  
  
  Set oXLBook = Nothing               'Disconnect from Excel (let the user take over)
  Set oXLApp = Nothing
  Set oXLSheet = Nothing
  
End Sub

Now I want to add the strTemp data into my combo box. By doing like this in my combo it displays only the 1st value. How can I get the record list to my combo?

Thank you.

 

[Andrzejek]

If you do this at the end of your code:

... 
Debug.Print strTemp
...

What do you get?
I would guess you have some Tab delimited text, right? If so, do some 'digging' for a Split() function in VB

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MiggyD]

    For lngRow = 1 To UBound(vArray, 1)
    For lngCol = 1 To UBound(vArray, 2) - 1
        [COLOR=#CC0000]strTemp = strTemp & vArray(lngRow, lngCol) & vbTab[/color]
        [COLOR=#4E9A06]'change above line to:  strTemp = vArray(lngRow, lngCol)
        'add code here:  YourCombo.value(indexKey)=strTemp
        'add code here to setup your next "indexKey" for YourCombo for the next column grab[/color]
    Next lngCol
        [COLOR=#CC0000]strTemp = strTemp & vArray(lngRow, UBound(vArray, 2)) & vbCrLf[/color]
        [COLOR=#4E9A06]'change above line to this:  strTemp = vArray(lngRow, UBound(vArray, 2))
        'REPEATE the above [b]ADD[/b] remarks[/color]
    Next lngRow

I hope this is a step forward for you. You will need to do some work that you can claim your own. GL!


I close my eyes, only for a moment and the moment's gone.

 

[Andrzejek]

I don't know about you, MiggyD, but I do hate juggling data between different variables
I would be very tempted to just do:

For lngRow = 1 To UBound(vArray, 1)
    For lngCol = 1 To UBound(vArray, 2) - 1
        [blue]YouComboBox.AddItem[/blue] vArray(lngRow, lngCol)
    Next lngCol
        [blue]YouComboBox.AddItem[/blue] vArray(lngRow, UBound(vArray, 2)) 
Next lngRow

and forget about [tt]strTemp[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Erm...

[COLOR=blue]vArray = oXLSheet.Range("E12:E140").Value
YouComboBox.List=vArray[/color]

 

[Andrzejek]

strongm, you are killing it with a 'one-liner':
[tt]YouComboBox.List = oXLSheet.Range("E12:E140").Value[/tt]

But, did the question change?
In the OP, the question is about the ranges of: [tt]A5:A9[/tt] and [tt]E5:E9[/tt], and now we are talking about [tt]E12:E140[/tt]



---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Discontinuous ranges is trickier to achieve without looping - unless your version of excel is Excel 365 or excel for Web, in which case there is an opportunity to leverage the worksheetfunction VSTACK. I don't have either, so can't verify an example, but it should be something like:

[COLOR=blue]vArray = oXLSheet.WorksheetFunction.Vstack(Range("A5:A9"), Range("E5:E9")).Value
YouComboBox.List = vArray[/color]

 

[Andrzejek]

It would be nice if Niki_S would 'jump-in' and tell us if any of this is of any help...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

Looks like Niki_S decided to start a new thread pretty much asking the same question, the only difference being they don't want the results in a Combobox, they want them in an array. Which, of course, we'd already answered for a single column range. And provided suggestions for combining multiple single column ranges

I must have got out of bed the wrong side this morning, because I am actually quite irritated by this, particularly since they issued a star for the suggestion you'd need to loop after the same thing had already been said in this thread

(it also means I am now loathe to post my VBA code of a limited version of VStack that I knocked together - and yes, it uses loops ...)

 

[strongm]

Less wound up today, so:

[COLOR=blue][COLOR=green]' Vertically stacks values from first column of passed ranges into a single 1D array[/color]
Public Function vbVstack(ParamArray Rng() As Variant) As Variant
    Dim myRng As Variant
    Dim proxiearry() As Variant
    Dim myval As Variant
    Dim max As Long
    
    For Each myRng In Rng
        For Each myval In myRng.Value2
            max = max + 1
            ReDim Preserve proxiearry(1 To max) As Variant
            proxiearry(max) = myval
        Next
    Next
    
    vbVstack = proxiearry
End Function[/color]

 

[Niki_S]

I have to do a change in my excel as below,

According to my excel my final output should be like this,

797     0.23
785     1.02
258     0.56
124     0.95
256     0.85
987     0.845
875     0.68
797     0.12
548     0.54
684     0.78

For this I have to change my coed. My code is as below and how can I take this as my final output?

    cboOperation.Clear
   For lngRow = LBound(vArray, 1) To UBound(vArray, 1)
    If Not IsEmpty(vArray(lngRow, 1)) Then
            cboOperation.AddItem vArray(lngRow, 1)
    End If
   Next lngRow

Thank you.

 

[strongm]

I'm getting confused now, probably becaseu this same question gotr split over two threads.

So, for clarity

1) Are you definitely doing this in VB6, not VBA (this can make a significant difference depending on the answer to the next question)
2) Where how do you actually want your output? In an array? In a combo? As a string? What?

 

[Niki_S]

I'm doing this in VB6 and I want to take these data using an array and want to display them in a combo box.

 

[strongm]

>and want to display them in a combo box

The array bit is pretty easy, if you take a look at my vbVstack function above (just one example of how this might be done)

But just to be clear here - a VB6 combo box only has a single column, so how are you wanting to display multiple columns in that combo box (given it does not support tabs either)?

By the way, my earlier suggestion concerning directly assigning an array to the List property of a combobox only works with the MSForms version, not the VB6 version. All the talk of Excel somehow led me to foolishly assume we were working with MSForms.