How to use 2D array in vb 6? 1

[Niki_S]

I want to get my excel data into a 2D array. For that I did something like this and I want to know the way of getting data to my array.

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 vArray1 As Variant
Dim lngRow As Long
Dim lngRow1 As Long
Dim strTemp As String

Dim sqlqueryVw As String
Dim Operations As ADODB.Recordset

[highlight #FCE94F]Dim Operation(12, 140) As Integer[/highlight]

    
 
  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*****
[highlight #FCE94F]  Operation = oXLSheet.Range("E12:E140").Value[/highlight]
  
  vArray = oXLSheet.Range("E12:E140").Value
  vArray1 = oXLSheet.Range("U12:U140").Value
  
 
   
   txtStyleNo.Text = Style_no
   txtLine.Text = Line_no
   

   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
   
  
  Set oXLBook = Nothing               'Disconnect from Excel (let the user take over)
  Set oXLApp = Nothing
  Set oXLSheet = Nothing
  
End Sub

My requirement is, I want to get all the data in excel file from E12:E140 and U12:U140 to my array. How can I do this?

Thank You

 

[combo]

Simplest with Variant array:
[tt]Dim Operation() As Variant
' ...
Operation = oXLSheet.Range("E12:E140")[/tt]

combo

 

[Niki_S]

I want to take these two ranges at once.

 E12:E140 and U12:U140

How can I do that?

Thank you.

 

[strongm]

Is this not a repeat of thread222-1818982?

 

[combo]

No way to get 2D array from multiple area excel range without excel functions. Variable=Range assignment always returns 2D array of first area data. However, you can pick "E12:U140" to single array and loop two columns to copy to another array.

combo

 

[Niki_S]

If I use two arrays, how can I loop two columns to copy to another array?

Thank you

 

[combo]

Assuming that range sizes are the same and data is in first column:

vArray1 = oXLSheet.Range("E12:E140")
vArray2 = oXLSheet.Range("U12:U140")
For i = 1 to UBound(vArray1, 1)
    vArray3(i, 1) = vArray1(i, 1): vArray3(i, 2) = vArray2(i, 1)
    vArray4(i) = vArray1(i, 1): vArray4(i + UBound(vArray1, 1)) = vArray2(i)
Next i

Array from range (vArray1, vArray2) is 1-based and 2D. vArray3 is two columns combination, vArray4 combines data in single column, I assumed base 1, they should be redimed to match range sizes.

combo

 

[Niki_S]

I used this and I got an error as type mismatch in the highlighted are,

Dim vArray As Variant
Dim vArray1 As Variant
Dim vArray2 As Variant
Dim vArray3(12, 140) As Variant
Dim vArray4 As Variant
Dim lngRow As Long
Dim lngRow1 As Long
Dim strTemp As String

vArray1 = oXLSheet.Range("E12:E140")
vArray2 = oXLSheet.Range("U12:U140")

For i = 12 To UBound(vArray1, 1)
        vArray3(i, 1) = vArray1(i, 1): vArray3(i, 2) = vArray2(i, 1)
        [highlight #FCE94F]vArray4(i) = vArray1(i, 1)[/highlight]: vArray4(i + UBound(vArray1, 1)) = vArray2(i, 1)
    Next i

What should I do for this?

Thank you

 

[combo]

Declare vArray3 and vArray4 as variant arrays with sizes matching the source and output data.

combo

 

[Niki_S]

I did my one as below,

    For lngRow = 1 To UBound(vArray, 1)
    For lngCol = 1 To UBound(vArray, 2)
        strTemp = strTemp & vArray(lngRow, lngCol) & vbTab
    Next lngCol
        strTemp = strTemp & vArray(lngRow, UBound(vArray, 2)) & vbCrLf
    Next lngRow

    cboOperation.Clear
   For lngRow = LBound(vArray, 1) To UBound(vArray, 1)
    If Not IsEmpty(vArray(lngRow, 1)) Then
        'If cboOperation.Items.IndexOf(vArray(lngRow, 1)) = -1 Then
        'If vArray.Text = cboOperation.List(i) Then
            'Exit For
        'Else
            cboOperation.AddItem vArray(lngRow, 1)
        'End If
        'End If
    End If
   Next lngRow
   

For lngRow = 1 To UBound(vArray1, 1)
    For lngCol = 1 To UBound(vArray1, 2)
        strTemp = strTemp & vArray1(lngRow, lngCol) & vbTab
    Next lngCol
        strTemp = strTemp & vArray1(lngRow, UBound(vArray1, 2)) & vbCrLf
    Next lngRow
   
   For lngRow = LBound(vArray1, 1) To UBound(vArray1, 1)
    If Not IsEmpty(vArray1(lngRow, 1)) Then
        'If Not (al.contains(vArray)) Then
        'If vArray.Text = cboOperation.List(i) Then
            'Exit For
        'Else
            cboOperation.AddItem vArray1(lngRow, 1)
        'End If
        'End If
    End If
   Next lngRow

Thank you

 

[Andrzejek]

If that's your 'final' code, then why so much commented out lines of code?
And why [tt]strTemp[/tt]? It does not do anything...

---- Andy

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