How Do I: Use a Spreadsheet as an array

[Syizik]

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.

[krtxmrtz]

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

[Syizik]

Thanks I'll give it a shot

[Syizik]

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

[Syizik]

sorry for double posting
here's a screenshot

[Bruce Fox]

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)

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.

[Bruce Fox]

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




Bruce.

[Bruce Fox]

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.

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.

[brucevde]

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")

[Bruce Fox]

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.

[Syizik]

It Worked!!!!!

Tanks 4 ur support.

[Bruce Fox]

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.

[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

[Bruce Fox]

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.