How Do I: Use a Spreadsheet as an array

I Have A Bunch of data on a spreadsheet that i would like to use to fill in values for checkboxes, comboboxes, listboxes, sliders, and textboxes. each item starts anew at A and ends at AN and there are 88 items as of now. I will be assigning variables from each field A-AN depending on which item is selected. Can anyone get me started.:confused:

Yoy should start by doing a forum (or web) search. At any rate, here's a post about interfacing with Excel.
http://www.vbforums.com/showthread.p...gramming+excel

Thanks I'll give it a shot

Yeah.... No help.
i need a link to a snipet for taking values FROM Excel Spreadsheet A1 thru A88 and assigning them to VB.
Here's the error i get

sorry for double posting
here's a screenshot

Here is a place to start, if this is on track, I'll add the Array loading (on is already Dimmed).


Add the 'name' of your File to the Open statement too :)

Don't close of the instance of Excel, just Minimise it (I'll do that latter)

VB Code:

Option Explicit
 
'Note: NO reference to MS Excel Object Library x.x is required in this example
 
Private Sub Form_Load()
Dim objExcel As Object
Dim objExcelWS As Object
Dim intRowCount As Integer, intColCount As Integer
Dim intRowIdx As Integer, intColIdx As Integer
Dim strArr() As String
 
On Error GoTo Err_Handler
 
    'Use the current (Opened) existance of Excel << Alternate method
    'Set objExcel = GetObject(, "Excel.Application")
 
    'Craete an Instance of Excel
    Set objExcel = CreateObject("Excel.Application")
    Set objExcelWS = objExcel.Workbooks.Open(App.Path & "\test.xls")
 
    objExcel.Visible = True
 
    intRowCount = objExcel.ActiveSheet.UsedRange.Rows.Count 'Capture the number of USED Rows
    intColCount = objExcel.ActiveSheet.UsedRange.Columns.Count  'Capture the number of USED Columns
 
    For intRowIdx = 1 To intRowCount  'Iterate each Row
        For intColIdx = 1 To intColCount  'Iterate each Column
            MsgBox objExcel.ActiveSheet.Cells(intRowIdx, intColIdx).Value
        Next
    Next
 
    objExcel.Application.Quit
    Set objExcel = Nothing
    Set objExcelWS = Nothing
 
Exit Sub
 
Err_Handler:
    If Not (objExcelWS Is Nothing) Then Set objExcelWS = Nothing
    If Not (objExcel Is Nothing) Then
        objExcel.Application.Quit
        Set objExcel = Nothing
    End If
 
    MsgBox "Number: " & Err.Number & vbCrLf & _
    "Description: " & Err.Description, vbOKOnly + vbCritical, "Error!"
End Sub

Bruce.

The Excel Rows and Columns are 'dynamic'. You can add/delete as you need and the code will still work!




Bruce.

The strArr is MultiDimensional. It now stores all the values of the WorkSheet

Array(0,0) = A1
Array(0,1) = B2
.
.
Array(1,0) = A2
etc.

VB Code:

Option Explicit
 
'Note: NO reference to MS Excel Object Library x.x is required in this example
 
Private Sub Form_Load()
Dim objExcel As Object
Dim objExcelWS As Object
Dim intRowCount As Integer, intColCount As Integer
Dim intRowIdx As Integer, intColIdx As Integer
Dim strArr() As String
 
On Error GoTo Err_Handler
 
    'Use the current (Opened) existance of Excel << Alternate method
    'Set objExcel = GetObject(, "Excel.Application")
 
    'Craete an Instance of Excel
    Set objExcel = CreateObject("Excel.Application")
    Set objExcelWS = objExcel.Workbooks.Open(App.Path & "\test.xls")
 
    objExcel.Visible = True
 
    intRowCount = objExcel.ActiveSheet.UsedRange.Rows.Count 'Capture the number of USED Rows
    intColCount = objExcel.ActiveSheet.UsedRange.Columns.Count  'Capture the number of USED Columns
 
    ReDim strArr(intRowCount - 1, intColCount - 1)
 
    For intRowIdx = 1 To intRowCount  'Iterate each Row
        For intColIdx = 1 To intColCount  'Iterate each Column
           strArr(intRowIdx - 1, intColIdx - 1) = objExcel.ActiveSheet.Cells(intRowIdx, intColIdx).Value
        Next
    Next
 
    objExcel.Application.Quit
    Set objExcel = Nothing
    Set objExcelWS = Nothing
 
    '**************************************
    'Display the First value!
    MsgBox strArr(0, 0)
 
Exit Sub
 
Err_Handler:
    If Not (objExcelWS Is Nothing) Then Set objExcelWS = Nothing
    If Not (objExcel Is Nothing) Then
        objExcel.Application.Quit
        Set objExcel = Nothing
    End If
 
    MsgBox "Number: " & Err.Number & vbCrLf & _
    "Description: " & Err.Description, vbOKOnly + vbCritical, "Error!"
End Sub

Bruce.

Unless A1 and A88 are variables shouldn't the line (from Syizik's bitmap post)

Set oRng1 = oWS.Range(A1, A88)

be

Set oRng1 = oWS.Range("A1", "A88")

or the same syntax as oRng2

Set oRng1 = oWS.Range("A1:A88")

I beleive you would be correct!

In the example I posted, its is dynamic as far as number of rows/columns are concerned. It is help full to be ablr to return UsedRange - it is overlooked :)




Bruce.

It Worked!!!!!

Tanks 4 ur support.

No problem :)


Note: You can also use this (with a slight mod) to use the Excel Library Reference. In the example above, you don't need it - just have Excel of any version on the host PC.



Bruce.

How do i use this "UsedRange" Function you mentioned? Does it tell you how far it read the sheet? i.e.-max cells and rows

Quote:

---

Originally posted by Syizik
How do i use this "UsedRange" Function you mentioned? Does it tell you how far it read the sheet? i.e.-max cells and rows

---

Ummm, Its in the code example I provided (I even commeneted those lines) :)

UsedRage (column/row) will return the upper limit of the used cells. As previously mentioned it enables addition data/edits to the
WorkSheet whithout any 'hardcoding' changes.




Bruce.